mysql - SQL 查询以区分使用大小写的行

Question

这是我正在查询的表中的数据的样子

表一

ITEM     SEQUENCE     CODE
Item1       1            A 
Item1       2            B
Item2       1            B
Item2       2            C
Item2       3            D

我当前的查询看起来像

  Select Distinct Table1.ITEM, 
  case when Table1.SEQUENCE = '1' Then Table2.DSC end As FirstDSC,
  Case When Table1.SEQUENCE = '2' then Table2.DSC End As SecondDSC,
  Case When Table1.SEQUENCE = '3' Then Table2.DSC End As ThirdDSC
    From Table1
    Join Table2 on Table2.Code = Table1.Code
    Where Table1.Item In (Subquery here to find distinct values that Item can be)

它目前返回的数据看起来像

ITEM  FIRSTDSC SECONDDSC THIRDDSC
Item1   DSC-A     NULL      NULL
Item1   NULL     DSC-B      NULL 

我想知道如何让数据返回看起来像

ITEM FIRSTDSC  SECONDDSC  THIRDDSC
Item1  DSC-A      DSC-B       NULL
Item2  DSC-B      DSC-C       DSC-D

有没有好的方法可以做到这一点，还是我目前的查询方向完全错误？

Answer 1

我认为您需要group by 和条件聚合，而不是distinct:

Select Table1.ITEM, 
       max(case when Table1.SEQUENCE = '1' Then Table2.DSC end) As FirstDSC,
       max(Case When Table1.SEQUENCE = '2' then Table2.DSC End) As SecondDSC,
       max(Case When Table1.SEQUENCE = '3' Then Table2.DSC End) As ThirdDSC
From Table1 Join
     Table2
     on Table2.Code = Table1.Code
Where Table1.Item In (Subquery here to find distinct values that Item can be)
group by table1.ITEM;