嗨，我想使用带有跟踪列表的MySQL过滤日志。
每个日志都属于一台服务器，
每个跟踪都属于一台服务器，并具有0..N个模式
每个模式都属于一个跟踪
我有3张桌子：
logs : | id | ip | url | server_id | ...
tracking : | id | server_id | name | other fields...
pattern : | id | tracking_id | pattern |
我想计算与特定服务器的跟踪匹配的日志，我的问题是我的查询混淆了有模式和无模式的跟踪。
SQL小提琴：http ://sqlfiddle.com/#!2/f11b1/2
SELECT COUNT(DISTINCT logs.ip), tr.name
FROM `logs`
INNER JOIN `trackings` as tr ON
( tr.server_id = logs.server_id )
AND -- OTHER conditions between log and tracking
LEFT JOIN `patterns` as pt ON
( pt.tracking_id = tr.id )
AND (logs.url LIKE pt.pattern )
GROUP BY tr.id
我的问题是在第二次联接上，如果我使用INNER JOIN patterns as pt ON我会得到正确的结果，但仅在具有某些模式的跟踪上，如果我使用LEFT JOIN patterns as pt ON我会得到所有跟踪，但计数错误（我得到的结果SELECT COUNT(DISTINCT logs.ip) FROM logs）
编辑我可以在跟踪字段中获得正确的结果，该字段指示跟踪是否具有模式和UNION：
(
SELECT COUNT(DISTINCT lg.ip), tr.name
FROM `logs` as lg
INNER JOIN `trackings` as tr ON
( tr.server_id = lg.server_id )
AND (tr.hasPatterns = 1)
AND -- Other conditions
INNER JOIN `patterns` as pt ON
( pt.tracking_id = tr.id )
AND (lg.url LIKE pt.pattern )
WHERE
GROUP BY tr.id
)
UNION
(
SELECT COUNT(DISTINCT lg.ip), tr.name
FROM `logs` as lg
INNER JOIN `trackings` as tr ON
( tr.server_id = lg.server_id )
AND (tr.hasPatterns = 0)
WHERE
GROUP BY tr.id, lg.date
)
但我想有一种方法可以不使用Union ...
您可以在其中放一个条件count，所以我认为以下内容可以满足您的需求：
SELECT COUNT(DISTINCT (case when tr.hasPatterns = 1 and pt.tracking_id is not null
then lg.ip
when tr.hasPatterns = 0
then lg.ip
end)), tr.name
FROM `logs` as lg
INNER JOIN `trackings` as tr ON
( tr.server_id = lg.server_id )
AND -- Other conditions
LEFT JOIN `patterns` as pt ON
( pt.tracking_id = tr.id )
AND (lg.url LIKE pt.pattern )
WHERE
GROUP BY tr.id
编辑：
这将返回您想要的：
SELECT COUNT(DISTINCT (case when tr.size = 0 and pt.tracking_id is not null
then lg.ip
when tr.size > 0 and lg.size > tr.size
then lg.ip
end)), tr.name
FROM `logs` as lg
INNER JOIN `trackings` as tr ON
( tr.server_id = lg.server_id )
LEFT JOIN `patterns` as pt ON
( pt.tracking_id = tr.id )
AND (lg.url LIKE pt.pattern )
GROUP BY tr.id;
您的SQL Fiddle具有lg.size > tr.size原始条件中没有的其他条件。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句