将 MySQL 查询从嵌套选择转换为内部连接

【中文标题】将 MySQL 查询从嵌套选择转换为内部连接【英文标题】：Convert MySQL query from a nested select to an inner join 【发布时间】：2013-10-08 08:47:58 【问题描述】：

我有一个嵌套的 select 语句可以正常工作，唯一的问题是运行时间太长。我将我的其他查询之一转换为内部联接，它要快得多。我正在尝试将此查询转换为内部联接。

当前工作查询：

select date(datetime), req_origin,  count( distinct session_id)
from LOG L1
where((datetime >= str_to_date('2013-01-01 00:00:00','%Y-%m-%d %H:%i:%s'))  
and (datetime < str_to_date('2013-01-05 00:00:00','%Y-%m-%d %H:%i:%s'))
and code_subcode in ('1001111','1001112','1001113','1001114'))
and ((
select count(*) from LOG L2 where  L2.session_id = L1.session_id and date(L2.datetime)
= date(L1.datetime)
and code_subcode in ('1001111','1001112','1001113','1001114')
) = 4)
group by date(datetime),req_origin order by date(datetime),req_origin;

这就是我为内部连接而获得的，但它不能正常工作。它仅在检查 1 个匹配代码时返回数据。当我查询 4 ​​个匹配代码时，查询不返回任何内容。

select date(l1.datetime), l1.req_origin, count(distinct l1.session_id)
from LOG l1
INNER JOIN LOG l2 on l2.SESSION_ID = l1.SESSION_ID
where((l1.datetime >= str_to_date('2013-01-01 00:00:00','%Y-%m-%d %H:%i:%s'))  
and (l1.datetime < str_to_date('2013-01-05 00:00:00','%Y-%m-%d %H:%i:%s'))
and l1.code_subcode in ('1001111','1001112','1001113','1001114')
and l2.code_subcode in ('1001111','1001112','1001113','1001114') = 4)
group by date(l1.datetime), l1.req_origin order by date(l1.datetime), l1.req_origin;

提前感谢您的帮助！

【问题讨论】：

【参考方案1】：

对子选择使用连接的可能解决方案。

SELECT DATE(datetime), req_origin,  COUNT( DISTINCT session_id)
FROM LOG L1
INNER JOIN 
(
    SELECT session_id, DATE(datetime) AS DateTime, COUNT(*) AS ItemCount
    FROM LOG  
    WHERE code_subcode IN ('1001111','1001112','1001113','1001114')
    GROUP BY session_id, DATE(datetime)
) L2
ON L1.session_id = L2.session_id
AND DATE(L1.datetime) = L2.DateTime
AND ItemCount = 4
WHERE((L1.datetime >= str_to_date('2013-01-01 00:00:00','%Y-%m-%d %H:%i:%s'))  
AND (L1.datetime < str_to_date('2013-01-05 00:00:00','%Y-%m-%d %H:%i:%s'))
AND code_subcode IN ('1001111','1001112','1001113','1001114'))
GROUP BY DATE(L1.datetime), req_origin 
ORDER BY DATE(L1.datetime), req_origin;

【讨论】：

@kickstar 是 log L1，log L2 是 diff table ?? 他们都是同一张桌子。给定主要事件的别名 L1，而 L2 是给子查询的别名，该子查询正在获取每个会话 ID/日期的项目计数。