有没有一种方法可以仅显示第一个非零值，但是如果所有值为零，则在单独的表中显示一个列的第一个实例

Question

这两个表非常大，我的选择语句具有更多的值，但是我认为我可以简化这些数据和查询，因此可以回答我的问题。

这是我的选择语句：

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

所以我有两个表。发票表和一张开票表。每个表中的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，这使我在我的我中具有重复的invoiceNums。结果，我不想要。

这是我试图获得的结果，仅在发票表中列出1个发票，其中第一种从InvoIceItem Taber的层列中的非零值的情况，以及如果没有非零的零，则列出了第一个零。

尝试这样的事情：

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

我只是不确定该如何做到这一点，或者如果可以的话，鉴于它们在两个不同的表上。

Answer 1

这个问题有点棘手：

在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

https://dbfiddle.uk/?rdbms = postgres_11& amp; fiddle = e16faa9ed8f9b9c8e08888888bc3c468229

尝试以下操作：

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
)

Answer 2

假设/理解：

• 虽然用Sybase标记该问题并未区分4x不同的Sybase RDBMS产品（ase> ase sqlanywhere iq iq ，优势），因此我要坚持应该是常规SQL语法（即4X产品具有不同的SQL方言；此外，ASE也不支持CTE），
• 我不 支持CTES） t了解OP的使用左（outer）Join由于提供的输出似乎没有表示InvoIteEtem的“缺失”行，
• 因此尚不清楚哪个表layer和invoiceItemnum列属于，因此我将假设它们属于InvoiceItem

在一组最小表定义和关联的insert猜测 /code>语句：

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表明需要左（外）JOIN（
• SYBASE）SAP ASE 16.0在（Sybase）SAP 中测试的所有查询

Answer 3

您是否尝试过ASC和DESC？

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