mysql - 连接来自同一个表的复杂 mysql 查询

Question

这有效:

    SELECT * 
FROM ( SELECT 
SUM(count) AS valid,
SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(ref_url,'http://',''),'https://',''),'www.',''),'/',1) AS sDomain 
FROM ref_records
WHERE user_id = 9507
GROUP BY sDomain 
ORDER BY 1 DESC) 
as A

INNER JOIN

( SELECT 
SUM(invalid) AS invalid,
SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(ref_url,'http://',''),'https://',''),'www.',''),'/',1) AS sDomain
FROM ref_records
WHERE user_id = 9507
GROUP BY sDomain 
ORDER BY 1 DESC) 
as B


on A.sDomain=B.sDomain

但是它输出下表:

valid | sDomain | invalid | sDomain

现在我希望按如下方式合并相同的结果:

sDomain | valid | invalid

有什么想法吗？谢谢。

Answer 1

您可以在选择中选择唯一的项目和其他项目的总和，然后使用 group by 语句。

检查以下语句:

select A.sDomain ,sum(valid) as valid, sum(invalid)
from A INNER JOIN B ON A.sDomain=B.sDomain
group By A.sDomain