Gibt es eine Möglichkeit, nur den ersten Wert ungleich Null anzuzeigen, aber wenn alle Werte Null sind, die erste Instanz einer Spalte in einer separaten Tabelle von der verknüpften Tabelle anzuzeigen?

Question

Die beiden Tabellen sind ziemlich groß und meine Select-Anweisung enthält mehr Werte, als ich erhalte, aber ich denke, ich kann diese Daten und Abfrage vereinfachen, um meine Frage zu beantworten.

Das ist meine Wahlaussage:

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

Ich habe also zwei Tische. Invoice-Tabelle und InvoiceItem-Tabelle. Sie werden durch die Spalte „InvoiceNum“ in jeder Tabelle verbunden und zeigen die Spalten „InvoiceNum“ und „Layer“

Hier sind die Ergebnisse dieser Abfrage:

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

Da meine InvoiceItem-Tabelle mehrere Zeilen enthält, die 1 InvoiceNum zugewiesen werden können, führt dies zu doppelten InvoiceNums in meinen Ergebnissen, was ich nicht möchte.

Dies ist das Ergebnis, das ich zu erhalten versuche, um nur eine Rechnungsnummer aus der Rechnungstabelle aufzulisten, wobei der erste Fall ein Wert ungleich Null aus der Ebenenspalte der Tabelle „InvoiceItem“ ist und wenn es keinen Wert ungleich Null gibt , Listen Sie die erste A-Null auf.

Versuchen Sie etwas wie Folgendes:

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

Ich bin mir einfach nicht sicher, wie ich das machen soll oder ob das überhaupt möglich ist, da sie auf zwei verschiedenen Tischen liegen.

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
)