主页 / 问题 / 925164
Accepted
YevgeniyM
Asked:2020-12-25 14:18:50 +0800 CST2020-12-25 14:18:50 +0800 CST

MySQL IF_NULL 在 WHERE

  • 772

客户表：

CREATE TABLE `clients` (
  `id` int(11) NOT NULL,
  `name` varchar(45) CHARACTER SET latin1 DEFAULT NULL,
  `company_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_clients_1_idx` (`company_id`),
  CONSTRAINT `fk_clients_1` FOREIGN KEY (`company_id`) REFERENCES `companys` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8

客户任务表：

CREATE TABLE `tasks` (
  `id` int(11) NOT NULL,
  `name` varchar(45) CHARACTER SET latin1 DEFAULT NULL,
  `deadline` datetime DEFAULT NULL,
  `dt` datetime DEFAULT CURRENT_TIMESTAMP,
  `client__id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

带出客户：

SELECT clients.*, tasks.*
FROM clients
LEFT JOIN (
    SELECT id, task_text, MIN(deadline) as deadline, dt, client_id
    FROM tasks
    WHERE deadline >= NOW()
    GROUP BY client_id
) tasks ON clients.id = tasks.client_id
ORDER BY
    CASE WHEN deadline IS NULL THEN "3000:12:12 23:59:59" 
        ELSE deadline
    END ASC,
position ASC, dt DESC

有必要为客户显示最近的任务（即可以提前一周安排，但应该显示最近的任务）： MIN(deadline)..WHERE deadline >= NOW() BUT, if under条件 WHERE dead >= NOW () 没有任务，你需要显示最后一个，即 无条件：截止日期 >= NOW()

mysql

1 个回答

  1. Best Answer

    像这样的东西：

    SELECT clients.*, tasks.*
  FROM clients
  LEFT JOIN (
    SELECT *
      FROM tasks
     WHERE (client_id, deadline) IN(
        SELECT client_id,
               coalesce( MIN(if(deadline >= NOW(), deadline, NULL)), MAX(deadline)) as deadline
          FROM tasks
         GROUP BY client_id
       )
  ) tasks ON clients.id = tasks.client_id

    前提是对于一个客户，不能有两个任务具有相同的截止日期。如果可以，那么要搜索具有最小/最大截止日期的行，您将需要使用其中一种方法。或者对于 MySQL 8.0，使用窗口函数按所需顺序对行进行编号。

    • 2

相关问题