Adakah terdapat cara untuk memaparkan hanya nilai bukan sifar pertama, tetapi jika semua nilai adalah sifar, paparkan contoh pertama lajur dalam jadual berasingan daripada jadual yang digabungkan

Question

Dua jadual ini agak besar dan pernyataan pilihan saya mempunyai lebih banyak nilai daripada yang saya dapat, tetapi saya fikir saya boleh memudahkan data dan pertanyaan ini untuk menjawab soalan saya.

Ini adalah kenyataan pilihan saya:

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

Jadi saya ada dua meja. Jadual invois dan jadual InvoiceItem. Mereka disertai oleh lajur InvoiceNum dalam setiap jadual dan menunjukkan lajur InvoiceNum dan Layer

Berikut adalah keputusan pertanyaan ini:

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

Memandangkan jadual InvoiceItem saya mempunyai berbilang baris yang boleh diperuntukkan kepada 1 InvoiceNum, ini menghasilkan InvoiceNums pendua dalam hasil saya, yang saya tidak mahu.

Ini adalah hasil yang saya cuba dapatkan, untuk menyenaraikan hanya 1 nombor invois daripada jadual invois, di mana kes pertama ialah nilai bukan sifar daripada lajur lapisan jadual InvoisItem, dan jika tiada nilai bukan sifar , senaraikan sifar A pertama.

Cuba sesuatu seperti ini:

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

Saya hanya tidak pasti bagaimana untuk melakukan ini, atau sama ada ia mungkin memandangkan ia berada di dua meja berbeza.

Answer 1

Andaian/Pemahaman:

• Digunakan sybase 标记，但问题并没有区分 4x 不同的 Sybase RDBMS 产品（ASE、SQLAnywhere、IQ、Advantage walaupun), jadi saya akan tetap dengan sintaks SQL biasa (iaitu produk 4x mempunyai dialek SQL yang berbeza; juga, ASE tidak menyokong CTE)
• Saya tidak faham bahawa terdapat sebarang baris "hilang" dalam jawapan OP untuk left (outer) join 的使用，因为提供的输出似乎没有表明 InvoiceItem
Tidak jelas
• Layer 和 InvoiceItemNum 列属于哪个表，因此我假设它们属于 InvoiceItem

Teka set minimum definisi jadual dan penyataan

yang berkaitan: 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)

Pertanyaan yang menjana output semasa 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

Beberapa idea berbeza (rumit, tidak kemas) untuk menjana output yang diingini 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

Kedua-dua ini akan menjana:

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

Nota:

Tidak pasti apakah output yang sepatutnya kerana ia tidak muncul (untuk saya) dan OP telah menunjukkan keperluan
• left （外部）join
Semua pertanyaan diuji dalam (Sybase)SAP
• ASE 16.0

Answer 2

Soalan ini agak rumit:

Cuba dalam 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

Demo

Dalam MySQL 8:

Cuba ini:

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
)