mysql - 复杂的 MysQL 查询，每次用户在同一天出现多次

Question

我正在尝试查询一个表。有3个重要字段:attendant_id、client_id和date。

服务员每次与客户打交道时，他们都会添加一个条目，其中包括他们的 ID、客户的 ID 和日期。有时，一名服务员会在同一天与多个客户一起工作。我想捕捉这种情况发生的时间。这是我目前所拥有的:

SELECT  *
FROM    timesheet_lines tsl1
WHERE   EXISTS
(
SELECT  *
FROM    timesheet_lines tsl2
WHERE   tsl1.date = tsl2.date
AND tsl1.attendant_id = tsl2.attendant_id
AND tsl1.client_id <> tsl2.client_id
AND tsl1.date between '2014-04-01' AND '2014-06-30'
LIMIT 2,5
)

我只想显示服务员与至少 2 个不同客户合作的结果。我不希望一天有超过 5 个。这就是我使用 LIMIT 2,5 的原因。

我也只对今年的 4 月到 6 月感兴趣。

我想我的语法可能是正确的，但查询似乎要花很长时间才能运行。有更快的查询吗？对于这个特定的日期范围，应该只有大约 42000 多个条目。我预计不会获得超过 500-600 个符合标准的结果。

我最终使用了以下内容:

create TEMPORARY table tempTSL1
 (date1 date, start1 time, end1 time, attend1 varchar(50), client1 varchar(50), type1 tinyint);
insert into tempTSL1(date1, start1, end1, attend1, client1, type1)
select date, start_time, end_time, attendant_id, client_id, type
from timesheet_lines
WHERE
timesheet_lines.date BETWEEN '2014-04-01' AND '2014-06-30'
and timesheet_lines.type IN (1,2,5,6);

create TEMPORARY table tempTSL2
 (date2 date, start2 time, end2 time, attend2 varchar(50), client2 varchar(50), type2 tinyint);
insert into tempTSL2(date2, start2, end2, attend2, client2, type2)
select date, start_time, end_time, attendant_id, client_id, type
from timesheet_lines
WHERE
timesheet_lines.date BETWEEN '2014-04-01' AND '2014-06-30'
and timesheet_lines.type IN (1,2,5,6);

SELECT *
FROM tempTSL1
WHERE (attend1,date1) IN (
    SELECT attend2
        ,date2
    FROM tempTSL2 tsl2
    GROUP BY attend2
        ,date2
    HAVING COUNT(date2) > 1
)
GROUP BY attend1
    ,client1
    ,date1
HAVING COUNT(client1) = 1

ORDER BY date1,attend1,start1

Answer 1

您可能会使它变得比需要的复杂得多。尝试这样的事情:

SELECT attendant_id
    ,client_id
    ,date
FROM timesheet_lines
WHERE (attendant_id,date) IN (
    SELECT attendant_id
        ,date
    FROM timesheet_lines tsl1
    GROUP BY attendant_id
        ,date
    HAVING COUNT(date) > 1
)
GROUP BY attendant_id
    ,client_id
    ,date
HAVING COUNT(client_id) = 1

子查询仅返回服务员在同一日期执行多项事件的结果。顶部查询将从同一个表中提取，匹配服务员和事件日期，并将结果集过滤为分组中只有 1 个客户的项目。示例:

attendant_id            client_id            date
1                       A                    2014-01-01
1                       B                    2014-01-01
2                       C                    2014-01-01
2                       D                    2014-01-02

将返回:

attendant_id            client_id            date
1                       A                    2014-01-01
1                       B                    2014-01-01

未经测试，但我认为它应该符合您要查找的内容，假设以下两个陈述为真:

• 您不会试图捕捉在同一天为同一客户工作的两名不同服务员
• 服务员每天只能为每位客户执行一项事件

如果第二点不正确，那么您将需要将其他字段合并到子查询中(例如 activity_id 或其他内容)。

希望这对您有所帮助。