mysql查询,从3个表中进行选择
问题描述:
我与查询拉动3个表中的唯一值进行争吵。这是更好地完成在2个单独的查询?mysql查询,从3个表中进行选择
查询是:
count as returned (
all leadID from lds where status = "ok"
AND leadID is also in rlds with recID="999"
AND rdate > (03-20-2012)
+
(all distinct leadID from plds where recID="999"
AND change != NULL
AND pdate > (03-20-2012))
工作查询的结果应该是 “2”:leadID 1和leadID 4
表LDS:
leadID |状态
1 |好吧
2 |不好
3 |好吧
表plds:
leadID | recID |改变| pdate
4 | 999 | ch1 | 03-27-2012
4 | 999 | ch2 | 03-27-2012
5 | 888 | NULL | 03-27-2012
表格地址:
leadID | recID2 | rdate
1 | 999 | 03-27-2012
6 | 999 | 03-27-2012
谢谢!
答
SELECT Ids.leadId
FROM
Ids JOIN
rlds ON rlds.leadId = Ids.LeadId AND recID = 999 AND rdate > '03-20-2012'
WHERE Ids.Status = 'ok'
UNION
SELECT leadId
FROM pIds
WHERE recID = 999 AND change IS NOT NULL AND pdate > '03-20-2012'
答
您可以使用UNION关键字中检索一个阵列中的2行,第一个包含您的第一场比赛的数量和你的第二个比赛的Seconde系列计数。
然后,您可以使用SUM关键字将封闭选择中的两行完全相加。结果将是一个单一的行计数你的数量。
SELECT sum(1)
FROM
(
SELECT count(1)
FROM lds
INNER JOIN rlds ON lds.leadid = plds.leadid
WHERE lds.status='ok' AND rlds.recid2=999 AND rlds.pdate > '03-20-2012'
UNION
SELECT count(1)
FROM plds
WHERE plds.recid=999
AND change != NULL
AND pdate > '03-20-2012'
) AS tmpcount
优秀的,这个作品 - 谢谢! – user1291842 2012-03-29 23:34:32