mysql - 与Mysql数据对比

Question

我有一个服务表，我需要列出同一客户要求 3 种服务的时间。 (服务A、服务B、服务C)

在下面的示例中，我已经获得了 2 种服务类型，但我尝试添加第三种服务类型，但不能。

抱歉还有其他非常重要的事情，我忘了提及的是我是如何获得该列表中每项服务完成的日期的？

SELECT * 
FROM service 
WHERE status = 'CONCLUDED' AND (
   CASE WHEN service = 'servicoC' THEN 
   (CASE WHEN data LIKE '2017-05%' THEN 1 ELSE 0 END) ELSE data END) AND servico IN ' ServiceB ') 
GROUP BY id_client HAVING MIN (service) ='servicoA'AND MAX (service) ='servicoB'

表:

Id;
Id client
service;
date;
Status;

结果示例:

Id_client、date_serviceA、date_serviceB、date_serviceC

Answer 1

您可以使用GROUP_BY和HAVING来做到这一点，例如:

SELECT id_client, COUNT(*)
FROM service
WHERE service IN ('ServiceA', 'ServiceB', 'ServiceC')
GROUP BY id_client
HAVING COUNT(DISTINCT service)) >= 3;

如果您想要每个服务的计数，则可以将其添加到 GROUP BY 和 SELECT 中，例如:

SELECT id_client, service, COUNT(*)
FROM service
WHERE service IN ('ServiceA', 'ServiceB', 'ServiceC')
GROUP BY id_client, service
HAVING COUNT(DISTINCT service)) >= 3;

更新

如果您想要每个服务的最大日期，那么您可以使用内部 SELECT 查询来实现，例如:

SELECT s.id_client, s.service, COUNT(s.*),
(SELECT MAX(date) FROM service where id_client = s.id_client and service = 'ServiceA') AS "date_ServiceA",
(SELECT MAX(date) FROM service where id_client = s.id_client and service = 'ServiceB') AS "date_ServiceB",
(SELECT MAX(date) FROM service where id_client = s.id_client and service = 'ServiceC') AS "date_ServiceC"
FROM service s
WHERE s.service IN ('ServiceA', 'ServiceB', 'ServiceC')
GROUP BY s.id_client, s.service
HAVING COUNT(DISTINCT s.service)) >= 3;