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

Question

我有一个嵌套的 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;

在此先感谢您的帮助!

Answer 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;