MySQL 查询 - 优化

Question

是否可以通过单个 MySQL 查询来完成此任务？

表格网址。字段 {id, url}

1、www.mysite.kom
2、mysite.kom
3、anothersite.kom

表日志。字段 {id、url_id、group_type - 1..10 范围内的数字

1, 1, 4
2, 1, 4
3, 2, 5
4, 2, 5
5、3、9

本例中的查询结果应该是:1(mysite.com and www.mysite.com = 1)

目标:

需要计算logs表中记录的所有不同的url，但有几个条件:

1) 带和不带www. 前缀的网址，如mysite.kom 和www.mysite.kom，
应该算作 1(不是 2)。

2) group_type 在 4..6

范围内

3) 现在，任何出现在 group_type 低于 4 的列表中的 group_type 4..6 的 url 都应该被忽略，而不是根本算不上。

SQL代码:

SELECT COUNT(DISTINCT TRIM(LEADING 'www.' FROM b.url))
FROM logs a
INNER JOIN urls b
ON a.url_id = b.id
WHERE (group_type BETWEEN 4 AND 6) 

----- and this condition below -----

AND TRIM(LEADING 'www.' FROM b.url)
NOT IN (
  SELECT TRIM(LEADING 'www.' FROM b.url)
  FROM logs a
  INNER JOIN urls b
  ON a.url_id = b.id
  WHERE (group_type < 4)
)

如果我的 sql 查询是正确的，是否可以对其进行优化(使其看起来更紧凑)？

Answer 1

SELECT COUNT(DISTINCT u.id) AS COUNT_QUES FROM urls u
INNER JOIN logs l
ON u.id=l.url_id
WHERE u.url NOT IN (SELECT A.url FROM 
(SELECT * FROM urls u
WHERE SUBSTR(u.url,1,3)!='www')A,
(SELECT * FROM urls v
WHERE SUBSTR(v.url,1,3)='www')B
WHERE A.url=SUBSTR(B.url,5,LENGTH(B.url))
) 
AND l.group_type BETWEEN 4 AND 6
AND u.id NOT IN
(SELECT DISTINCT u.id FROM urls u
INNER JOIN logs l
ON u.id=l.url_id
WHERE u.url NOT IN (SELECT A.url FROM 
(SELECT * FROM urls u
WHERE SUBSTR(u.url,1,3)!='www')A,
(SELECT * FROM urls v
WHERE SUBSTR(v.url,1,3)='www')B
WHERE A.url=SUBSTR(B.url,5,LENGTH(B.url))
) 
AND l.group_type < 4
)

或

SELECT COUNT(DISTINCT CASE WHEN B.URL_ID IS NOT NULL AND FLAG1 = 1 AND FLAG2 = 0 THEN TRIM(LEADING 'www.' FROM A.URL) END)
FROM URLS A 
LEFT JOIN (SELECT URL_ID,
           MAX(CASE WHEN GROUP_TYPE BETWEEN 4 AND 6 THEN 1 ELSE 0 END) FLAG1,
           MAX(CASE WHEN GROUP_TYPE < 4 THEN 1 ELSE 0 END) FLAG2
           FROM LOGS
           GROUP BY URL_ID) B
           ON A.ID = B.URL_ID

希望这对你有用。在 SQLFIDDLE 上检查这个 - http://sqlfiddle.com/#!2/1fde2/39