如何选择具有值组合的记录存在于两个表之间

Question

我无法计算出这一个，我想检查这两个表，其中给定LadSeries的所有LadReady都是"Shi"。这个查询就快到了。。。但410不应该出现在结果中，因为有一条记录中LadReady是"Yes"而不是"Shi"。

查询：

SELECT     Shipping.LadReady, 
Shipping.LadSeries, 
Drawings.Job, 
Drawings.Series, 
Drawings.Status, 
Drawings.DShopStatus, 
Drawings.SeriesInv 
FROM Shipping 
LEFT JOIN Drawings ON Drawings.Job = Shipping.LadJob 
AND Drawings.Series = Shipping.LadSeries 
WHERE Drawings.Job='22925' 
AND Shipping.LadReady='Shi' 
AND Drawings.Status='Shop Issued' 
AND DShopStatus='Complete'
AND (NOT Drawings.SeriesInv='Invoiced' AND NOT SeriesInv='Part Invoiced')

表：运输

LadReady | LadJob | LadSeries 
-----------------------------
Shi      | 22925  | 410
Shi      | 22925  | 410
Yes      | 22925  | 410
Shi      | 22925  | 100
Shi      | 22925  | 100
Shi      | 22925  | 200

表：图纸

Job   | Series |  Status      | DShopStatus | SeriesInv
-------------------------------------------------------
22925 | 410    | Shop Issued  | Complete    | Not Invoiced
22925 | 100    | Shop Issued  | Complete    | Not Invoiced
22925 | 200    | Shop Issued  | Complete    | Invoiced

结果：

LadReady | LadSeries| Job   | Series | Status       | DShopStatus | SeriesInv
--------------------------------------------------------------------------------
Shi       | 410      | 22925 | 410    | Shop Issued  | Complete    | Not Invoiced
Shi       | 100      | 22925 | 100    | Shop Issued  | Complete    | Not Invoiced

上面的第一个结果410不应该出现，因为我们有一个记录是LadReady="是"而不是"施"。

Answer 1

尝试以下查询。首先将shipping行限制为仅有效行，然后将其加入drawings

with valid as (
select Max(ladready) ladready, ladjob, ladseries
from shipping
where ladjob=22925
group by ladjob, ladseries
having Count(*)=Count(case when ladready='Shi' then 1 end)
)
select v.LadReady, v.LadSeries, d.Job, d.Series, d.DShopStatus, d.SeriesInv
from valid v
join drawings d on d.series=v.ladseries 
and d.DShopStatus='complete' 
and d.status='shop issued'
and not (d.SeriesInv in ('Invoiced','Part Invoiced'))

Answer 2

您可以使用cte:

with cte(id, c, s) as (
select s.ladseries, count(*), sum(s.ladready="Shi") from shipping s group by s.ladseries
)
select "Shi", d.* from drawings d join cte c on d.series = c.id and c.c = c.s where d.seriesinv = 'Not Invoiced'

Answer 3

我不知道你在使用哪些dbms，这在MySQL和SQL Server上进行了测试。

添加以下条件以删除所有至少为一次Yes的LadSeries

Shipping.LadSeries NOT IN (
select distinct Shipping.LadSeries 
from Shipping
where Shipping.LadReady ='YES' );

select s.LadReady,s.LadSeries,d.Job,d.Series,d.Status,d.DShopStatus,d.SeriesInv
from Drawings d
inner join Shipping s on d.Job=s.LadJob and d.Series=s.LadSeries
where d.Job=22925
and s.LadReady='Shi'
and d.Status='Shop Issued' 
and d.DShopStatus='Complete'
and d.SeriesInv not in ('Invoiced','Part Invoiced')
and s.LadSeries NOT IN (
select distinct Shipping.LadSeries 
from Shipping
where Shipping.LadReady ='YES' )
group by s.LadReady,s.LadSeries,d.Job,d.Series,d.Status,d.DShopStatus,d.SeriesInv; 

演示MySQL：https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/177

演示SQL Server：https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=784990a572531ef9d4063926d8e55ec1