如何在SQL中连接两个查询
我有两个要加入的 SQL 查询。
第一个查询：
SELECT
rp_date, key_code,
sum(case when rp_id=15102 then rp_value else null end) as users_completed,
sum(case when rp_id=15108 then rp_value else null end) as users_inProgress
FROM
te_rp_pc_rate
WHERE
abc_code = 'A204'
AND organisation_id = '444-4'
AND key_ code = '#KL0560'
GROUP BY
rp_date, key_code
ORDER BY
rp_date DESC, key_code
LIMIT 100;
第二个查询：
SELECT
cr_date
sum(case when rp_id=23101 then rp_value else null end) AS prim_kfc
FROM
te_emk_rate
WHERE
abc_code = 'A204'
AND organisation_id = '444-4'
AND ref_value = 0
GROUP BY
cr_date
ORDER BY
cr_date DESC
LIMIT 100;
日期 ( cr_date, rp_date) 应用于加入。对于第一个查询的rp_date第二个cr_date.
目标是获取同一日期的行中的列。我试过了，但结果太高了。
回答
您可以将查询放入子查询中并加入它们。像这样的东西：
SELECT *
FROM (
SELECT rp_date, key_code,
sum(case when rp_id=15102 then rp_value else null end) as users_completed,
sum(case when rp_id=15108 then rp_value else null end) as users_inProgress
from te_rp_pc_rate
WHERE abc_code = 'A204'
AND organisation_id = '444-4'
AND key_ code = '#KL0560'
group by rp_date, key_code
Order By rp_date DESC, key_code
LIMIT 100;
) as q1
LEFT JOIN (
SELECT cr_date, key_code
sum(case when rp_id=23101 then rp_value else null end) as prim_kfc
from te_emk_rate
WHERE abc_code = 'A204'
AND organisation_id = '444-4'
AND ref_value = 0
group by cr_date, key_code
Order By cr_date DESC, key_code
LIMIT 100;
) as q2
on q1.rp_date = q2.cr_date