如何選擇兩個表之間存在值組合的記錄

我無法弄清楚這一點，我想檢查這兩個表，對于給定的 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='Yes' 而不是 'Shi'。

uj5u.com熱心網友回復：

嘗試以下查詢。首先將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'))

uj5u.com熱心網友回復：

您可以使用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'

uj5u.com熱心網友回復：

我不知道您使用的是哪個 dbms，這是在MySQL和上測驗的SQL Server。

添加以下條件以洗掉所有LadSeries至少一次的人Yes

      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

標籤：sql

上一篇：[PL/SQL]如何制作一個運行多個程式的回圈

下一篇：獲取唯一的事件計數