Existe-t-il un moyen d'afficher uniquement la première valeur non nulle, mais si toutes les valeurs sont nulles, afficher la première instance d'une colonne dans une table distincte de la table jointe

Question

Les deux tables sont assez volumineuses et mon instruction select a plus de valeurs que ce que j'obtiens, mais je pense pouvoir simplifier ces données et cette requête afin de répondre à ma question.

Voici ma déclaration de choix :

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

J'ai donc deux tables. Table de facture et table InvoiceItem. Ils sont rejoints par la colonne InvoiceNum dans chaque tableau et affichent les colonnes InvoiceNum et Layer

Voici les résultats de cette requête :

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

Étant donné que ma table InvoiceItem comporte plusieurs lignes pouvant être attribuées à 1 InvoiceNum, cela entraîne des InvoiceNums en double dans mes résultats, ce que je ne souhaite pas.

C'est le résultat que j'essaie d'obtenir, lister un seul numéro de facture de la table des factures, où le premier cas est une valeur non nulle de la colonne couche de la table InvoiceItem, et s'il n'y a pas de valeur non nulle , listez le premier A zéro.

Essayez quelque chose comme ceci :

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

Je ne sais tout simplement pas comment faire cela, ni si c'est même possible étant donné qu'ils sont sur deux tables différentes.

Answer 1

Assomption/Compréhension :

• Utilisé sybase 标记，但问题并没有区分 4x 不同的 Sybase RDBMS 产品（ASE、SQLAnywhere、IQ、Advantage cependant), je m'en tiendrai donc à la syntaxe SQL commune (c'est-à-dire que les produits 4x ont des dialectes SQL différents ; de plus, ASE ne prend pas en charge CTE)
• Je ne comprends pas qu'il y ait des lignes « manquantes » dans la réponse du PO à left (outer) join 的使用，因为提供的输出似乎没有表明 InvoiceItem
Pas clair
• Layer 和 InvoiceItemNum 列属于哪个表，因此我假设它们属于 InvoiceItem

Devinez un ensemble minimal de définitions de tableaux et d'énoncés

associés : 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)

La requête qui génère la sortie actuelle de l'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

Quelques idées différentes (compliquées, désordonnées) pour générer le résultat souhaité du PO :

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

Ces deux généreront :

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

Remarque :

Je ne sais pas quel devrait être le résultat car il n'apparaît pas (pour moi) et le PO a démontré le besoin
• left （外部）join
Toutes les requêtes testées dans (Sybase)SAP
• ASE 16.0

Answer 2

Cette question est un peu délicate :

Essayez-le dans 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

Démo

Dans MySQL 8 :

Essayez ceci :

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
)