主页 / 问题 / 907526
Accepted
YevgeniyM
Asked:2020-11-17 02:08:26 +0000 UTC2020-11-17 02:08:26 +0000 UTC

使用 group by 条件和排序

  • 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

以及客户端的任务表（一个客户端可以有>0个任务，或者为空）：

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

有必要在任务中显示按截止日期字段排序的客户端。在最后期限 >= NOW() 条件下（也可以有 >1 个，但应该出现最近的一个）。

这是基本查询：

select * from clients
left join tasks ON tasks.client_id=clients.id
group by clients.id;
mysql

1 个回答

  1. Best Answer

    像这样的东西（例如https://paiza.io/projects/hQF7wYJbIDZGgK9150as_A?language=mysql）

    SELECT clients.*, tasks.*
FROM clients
JOIN (
    SELECT client__id, MIN(deadline) as deadline
    FROM tasks
    WHERE deadline >= NOW()
    GROUP BY client__id
) minTasks ON clients.id = minTasks.client__id
LEFT JOIN tasks ON tasks.client__id = clients.id AND minTasks.deadline = tasks.deadline
ORDER BY tasks.deadline ASC;
    • 1

相关问题