sql—当一列有不同的数据时，如何返回1行而不是3行

lokaqttq 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(326)

我有3行查询返回的数据

OrderId | OtherId
--------+---------
  1234  |  444
  1234  |  555
  1234  |  666

如何以这种格式返回数据

OrderId | OtherId | OtherId2 | OtherId
--------+---------+----------+--------
  1234  |   444   |   555    |   666

我能用吗 Distinct 为了这个问题？
编辑
结果集来自一个查询，例如

left join   (
                    Select  distinct 
                                o.id
                                ,OL2.db
                                ,case
                                    when i.cust = 'cust2' then 
                                        case
                                            when
                                                lower(pdf.dept) not like 'abc%' or pdf.dept is null
                                                    then 'Yes'
                                            else'No'
                                        end
                                    else 'Yes'
                                end                                                                         as  'Show'
                                ,otherId

                    from        order       o       with(nolock)
                    Join        instance    i       with(nolock)    on  i.id                                    =   o.id
                    Join        orderLine   ol      with(nolock)    on  ol.id                                   =   o.SocialNetworker_Order_InstanceId
                                                                    and ol.id   =   o.id
                    join        product     p       with(nolock)    on  o.id        =   p.id
                                                                    and ol.id                                   =   p.id
                    left join   productEx   pdf     with(nolock)    on  p.id                                    =   pdf.id
                                                                    and o.id                                    =   pdf.id
                    Where i.cust    in ('cust1') 
                ) ol2   on  OL2.id =    sno.id
                        and OL2.id2 =   sno.id2
                        and i.db    =   cc.db

如何将解决方案转化为上述代码？

sql sql-server

来源：https://stackoverflow.com/questions/62896612/how-to-return-1-row-instead-of-3-when-one-column-has-different-data

2条答案

按热度按时间

lymgl2op1#

你可以用 row_number() 和条件聚合：

select orderid,
       max(case when seqnum = 1 then otherid end) as otherid_1,
       max(case when seqnum = 2 then otherid end) as otherid_2,
       max(case when seqnum = 3 then otherid end) as otherid_3
from (select t.*, row_number() over (partition by orderid order by otherid) as seqnum
      from t
     ) t
group by orderid;

赞(0）回复(0）举报 2021-07-26

r7s23pms2#

使用具有级联联接条件的外部联接：

select t1.OrderId, t1.OtherId OtherId1, t2.OtherId OtherId2, t3.OtherId OtherId3
from orders t1
left join orders t2 on t2.OrderId = t1.OrderId
  and t2.OtherId > t1.OtherId
left join orders t3 on t3.OrderId = t2.OrderId
  and t3.OtherId > t2.OtherId

如果其他ID少于3个，则最右边的列将为空。
这个查询几乎适用于任何数据库。

赞(0）回复(0）举报 2021-07-26

我来回答

sql—当一列有不同的数据时，如何返回1行而不是3行

2条答案

相关问题

热门标签

最新问答