单个SQL从两个表中获取结果，这些表具有一个公共列但条件不同 [英] Single SQL to get results from two tables with one common column but different conditions

问题描述

新手尝试加入或联合失败。需要SQL来从两个具有公共列的表中获得结果。我想要包含唯一供应商代码的行，第2列计算来自表zqm的供应商代码，用于6月的日期，第3列是表rr中供应商代码（VC）的计数，用于每个唯一供应商代码，其中所有者是供应商和6月的日期。



来自以下两个表的sql期望结果示例：

Novice attempts to join or union failed. Need SQL to achieve results from two tables with common column. I want to have rows that contain unique vendor codes, with column 2 a count vendor codes from table zqm for date in June, with column 3 a count of vendor codes(VC) from table rr for each unique vendor code where owner is supplier and date in June.

Example sql desired result from below two tables:

VC     count VC       count RR
101	4             1
103     2             0

table zqm
VC     	date
101     June
101	June
101	June
101	June
103	June
103	June
103     July
Table rr
VC      owner      	date
101    supplier	        June
101    company	        June
103    company	        June
103    supplier         July

推荐答案

这是另一种方法：

with c as (
    select  vc,count(date) Count_VC
    from    zqm
    where   date = 'June'
    group by vc
    )
select  c.vc,c.Count_VC,sum(case rr.date when 'June' then 1 else 0 end) Count_RR
from    c
Left outer join rr on c.vc = rr.vc
where   rr.owner = 'supplier'
group by c.vc,c.Count_VC

select z.vc,
(select count(zqm.date)
from zqm where zqm.date = 'June' and zqm.vc=z.vc group by zqm.vc) as count_vc,
(CASE
  WHEN not exists (select * from rr where rr.date = 'June' and owner='supplier' and rr.vc=z.vc)
  THEN 0
  ELSE (select count(rr.date)
from rr where rr.date = 'June' and owner='supplier' and rr.vc=z.vc group by rr.vc)
  END)  as count_rr
from zqm z group by z.vc

试试这个

Try this

SElect distinct * from maintbl INNER JOIN
(select *  from zqm where  date='June') AS zqm ON maintbl.VC=zqm.VC
INNER JOIN
(select * from rr WHERE owner ='supplier' and date='June') as rr ON rr.VC=zqm.VC

这篇关于单个SQL从两个表中获取结果，这些表具有一个公共列但条件不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！