有沒有辦法只顯示第一個非零值，但如果所有值都為零，則在連接表的單獨表中顯示列的第一個實例

Question

這兩個表相當大，我的 select 語句有更多我獲得的值，但我認為我可以簡化這些資料和查詢，以便回答我的問題。

這是我的選擇語句：

SELECT invoice.InvoiceNum, Layer, InvoiceItemNum 
FROM (INVOICE 
        left outer join InvoiceItem item 
            ON item.InvoiceNum = Invoice.InvoiceNum
    ) 
ORDER BY invoice.InvoiceNum

所以我有兩張桌子。 Invoice 表和 InvoiceItem 表。它們由每個表中的 InvoiceNum 列連接起來，並顯示 InvoiceNum 和 Layer 列

以下是此查詢的結果：

InvoiceNum  | Layer | InvoiceItemNum
1           | 10    | 1
1           | 0     | 2
1           | 7     | 3 
1           | 0     | 4 
2           | 0     | 1
2           | 3     | 2 
3           | 0     | 1
3           | 0     | 2
3           | 0     | 3 
4           | 0     | 1
4           | 0     | 2
4           | 5     | 3

由於我的 InvoiceItem 表有多行可以分配給 1 個 InvoiceNum，這導致我的結果中出現重複的 InvoiceNum，這是我不希望的。

這是我試圖獲得的結果，只列出發票表中的1 個發票編號，其中第一種情況是來自InvoiceItem 表的圖層列的非零值，如果沒有非零值，則列出第一個零。

嘗試這樣的事情：

InvoiceNum  | Layer | InvoiceItemNum
1           | 10    | 1
2           | 3     | 2 
3           | 0     | 1
4           | 5     | 3

我只是不確定如何執行此操作，或者鑑於它們位於兩個不同的表上，這是否可能。

Answer 1

假設/理解：

• 雖然用sybase 標記，但問題並沒有區分4x 不同的Sybase RDBMS 產品（ASE、SQLAnywhere、IQ、 Advantage），因此我將堅持使用通用SQL 語法（即4x 產品具有不同的SQL 方言；此外，ASE 不支援CTE）
• 我不明白 OP 對 left (outer) join 的使用，因為提供的輸出似乎沒有顯示 InvoiceItem 中存​​在任何「缺失」行
• 不清楚 Layer 和 InvoiceItemNum 欄位屬於哪個表，因此我假設它們屬於 InvoiceItem

#猜測一組最小表定義和關聯的 insert 語句：

create table Invoice
(InvoiceNum     int
)

create table InvoiceItem
(InvoiceNum     int
,InvoiceItemNum int
,Layer          int
)

insert Invoice select 1 union all select 2 union all select 3 union all select 4

insert InvoiceItem values (1,1,10)
insert InvoiceItem values (1,2,0)
insert InvoiceItem values (1,3,7)
insert InvoiceItem values (1,4,0)

insert InvoiceItem values (2,1,0)
insert InvoiceItem values (2,2,3)

insert InvoiceItem values (3,1,0)
insert InvoiceItem values (3,2,0)
insert InvoiceItem values (3,3,0)

insert InvoiceItem values (4,1,0)
insert InvoiceItem values (4,2,0)
insert InvoiceItem values (4,3,5)

產生OP目前輸出的查詢：

select  inv.InvoiceNum,
        item.Layer,
        item.InvoiceItemNum
from    Invoice inv
left                          -- superfluous in this case?
join    InvoiceItem item
on      inv.InvoiceNum = item.InvoiceNum
order by 1,3

 InvoiceNum  Layer       InvoiceItemNum
 ----------- ----------- --------------
           1          10              1
           1           0              2
           1           7              3
           1           0              4
           2           0              1
           2           3              2
           3           0              1
           3           0              2
           3           0              3
           4           0              1
           4           0              2
           4           5              3

產生 OP 所需輸出的幾個不同（複雜、混亂）的想法：

-- join based on Layer!=0; if no rows found then override NULLs
-- with Layer=0 and InvoiceItemNum=min(InvoiceItemNum) where Layer=0;
-- needs more work in case there are no matching rows in InvoiceItem ...
-- wrap case/then in a coalesce() and set to, what, 0?

select  inv.InvoiceNum,
        coalesce(item1.Layer,0) as "Layer",
        case    when item1.InvoiceItemNum is NULL
                then (select min(InvoiceItemNum) from InvoiceItem item3 where item3.InvoiceNum = inv.InvoiceNum)
                else item1.InvoiceItemNum
        end as "InvoiceItemNum"

from    Invoice inv
left
join    InvoiceItem item1
on      inv.InvoiceNum = item1.InvoiceNum
and     item1.Layer != 0
and     not exists(select       1
                   from         InvoiceItem item2
                   where        item2.InvoiceNum = item1.InvoiceNum
                   and          item2.Layer != 0
                   and          item2.InvoiceItemNum < item1.InvoiceItemNum)
order by 1

-- OR

-- perform a mutually exclusive UNION of Layer!=0 and Layer=0 queries
-- with Layer!=0 having prcedence

select  inv.InvoiceNum,
        item1.Layer,
        item1.InvoiceItemNum

from    Invoice inv
--left  ??? needs work if this is really an outer join ???
join    InvoiceItem item1
on      inv.InvoiceNum = item1.InvoiceNum

and     (
         (      item1.Layer != 0
          and   not exists(select 1
                           from   InvoiceItem item2
                           where  item2.InvoiceNum = item1.InvoiceNum
                           and    item2.Layer != 0
                           and    item2.InvoiceItemNum < item1.InvoiceItemNum)
         )

         or

         (      item1.Layer = 0
          and   not exists(select 1
                           from   InvoiceItem item3
                           where  item3.InvoiceNum = item1.InvoiceNum
                           and    item3.Layer != 0)
          and   not exists(select 1
                           from   InvoiceItem item4
                           where  item4.InvoiceNum = item1.InvoiceNum
                           and    item4.Layer = 0
                           and    item4.InvoiceItemNum < item1.InvoiceItemNum)
         )
        )
order by 1

這兩者都會產生：

InvoiceNum  Layer       InvoiceItemNum
 ----------- ----------- --------------
           1          10              1
           2           3              2
           3           0              1
           4           5              3

註解：

• 不確定輸出應該是什麼，因為它（對我來說）沒有出現，OP已經證明了需要 left （外部）join
• #在 (Sybase)SAP ASE 16.0 中測試的所有查詢

Answer 2

這個問題有點棘手：

在 Postgres 中嘗試一下：

with cte as (
select 
inv.invoicenum,sum(layer::int) "sum_layer"
from invoice inv inner join invoiceitem item on item.invoicenum=inv.invoicenum
group by 1
)
,
cte1 as (

select distinct on (inv.invoicenum) inv.invoicenum,layer, InvoiceItemNum

from invoice inv inner join invoiceitem item on item.invoicenum=inv.invoicenum
where inv.invoicenum in (select invoicenum from cte where sum_layer=0)
order by inv.invoicenum, InvoiceItemNum
),
cte2 as (
select 
distinct on (inv.invoicenum) inv.invoicenum, layer , InvoiceItemNum
from invoice inv inner join invoiceitem item on item.invoicenum=inv.invoicenum
where inv.invoicenum in (select invoicenum from cte where sum_layer>0) and layer::int>0
order by inv.invoicenum, InvoiceItemNum
)
(
select * from cte1
union all
select * from cte2
)
order by 1

示範

在 MySQL 8 中：

試試這個：

with cte as (
select 
inv.invoicenum,sum(layer) "sum_layer"
from invoice inv inner join invoiceitem item on item.invoicenum=inv.invoicenum
group by 1
)
,
cte1 as (
select * from (
select inv.invoicenum, layer, InvoiceItemNum
from invoice inv inner join invoiceitem item on item.invoicenum=inv.invoicenum
where inv.invoicenum in (select invoicenum from cte where sum_layer=0)
order by inv.invoicenum, InvoiceItemNum
) c
group by invoicenum
),
cte2 as (
select * from (
select inv.invoicenum, layer, InvoiceItemNum
from invoice inv inner join invoiceitem item on item.invoicenum=inv.invoicenum
where inv.invoicenum in (select invoicenum from cte where sum_layer>0) and layer>0
order by inv.invoicenum, InvoiceItemNum ) c
group by invoicenum
)
(
select * from cte1
union all
select * from cte2
)