现有数据如下id forwarder trans_name mawb hawb gwt mode
------------------------------------------------------------------------------------
100232 NTS @0901019001506181 043-47774344 HKG10885582 1.00 1
100233 NTS @0901019001506181 043-47774344 HKG10885582 1.00 0
100234 GSH @0901019001506587 618-82046274 205746 49.00 1
100235 GSH @0901019001506587 618-82046274 205746 49.00 0
100236 GSH @0901019001506587 618-82046274 205746 49.00 -2
100237 GSH @0901019001506587 618-82046274 205746 49.00 1
100238 GSH @0901019001506587 618-82046274 205746 49.00 -2
100239 GSH @0901019001506587 618-82046274 205746 49.00 1
100240 GSH @0901019001506587 618-82046274 205746 49.00 -1
100241 GSH @0901019001506244 781-51963656 4230159991 33.00 1
小弟写了一条语句
SELECT ROW_NUMBER() OVER(ORDER BY Rpt_HAWB) AS ID,Rpt_MAWB,Rpt_HAWB,Rpt_Pieces,Rpt_GrossWT FROM dbo.T_I_IMP_LIST GROUP BY Rpt_MAWB,Rpt_HAWB,Rpt_Pieces,Rpt_GrossWT
结果如下----------------------------------------------------------------------------------------------
1 618-82046274 205746 9 49.00
2 781-51963656 4230159991 1 33.00
3 043-47774344 HKG10885582 2 1.00
现在需求在这种结果下加一列,根据MODE数值来表示状态,大于零的算一种状态,小于零的算一种状态
------------------------------------------------------------------------------------
100232 NTS @0901019001506181 043-47774344 HKG10885582 1.00 1
100233 NTS @0901019001506181 043-47774344 HKG10885582 1.00 0
100234 GSH @0901019001506587 618-82046274 205746 49.00 1
100235 GSH @0901019001506587 618-82046274 205746 49.00 0
100236 GSH @0901019001506587 618-82046274 205746 49.00 -2
100237 GSH @0901019001506587 618-82046274 205746 49.00 1
100238 GSH @0901019001506587 618-82046274 205746 49.00 -2
100239 GSH @0901019001506587 618-82046274 205746 49.00 1
100240 GSH @0901019001506587 618-82046274 205746 49.00 -1
100241 GSH @0901019001506244 781-51963656 4230159991 33.00 1
小弟写了一条语句
SELECT ROW_NUMBER() OVER(ORDER BY Rpt_HAWB) AS ID,Rpt_MAWB,Rpt_HAWB,Rpt_Pieces,Rpt_GrossWT FROM dbo.T_I_IMP_LIST GROUP BY Rpt_MAWB,Rpt_HAWB,Rpt_Pieces,Rpt_GrossWT
结果如下----------------------------------------------------------------------------------------------
1 618-82046274 205746 9 49.00
2 781-51963656 4230159991 1 33.00
3 043-47774344 HKG10885582 2 1.00
现在需求在这种结果下加一列,根据MODE数值来表示状态,大于零的算一种状态,小于零的算一种状态
ROW_NUMBER() OVER(ORDER BY Rpt_HAWB) AS ID,Rpt_MAWB,Rpt_HAWB,Rpt_Pieces,Rpt_GrossWT,mode=(case when mode >0 then '大于0' else '小于0' end)
FROM
dbo.T_I_IMP_LIST
GROUP BY
Rpt_MAWB,Rpt_HAWB,Rpt_Pieces,Rpt_GrossWT
ROW_NUMBER() OVER(ORDER BY Rpt_HAWB) AS ID,Rpt_MAWB,Rpt_HAWB,Rpt_Pieces,Rpt_GrossWT,mode=(case when mode >0 then '大于0' else '小于0' end)
FROM
dbo.T_I_IMP_LIST
GROUP BY
Rpt_MAWB,Rpt_HAWB,Rpt_Pieces,Rpt_GrossWT,mode
------------------------------------------------------------------------------------
100232 NTS @0901019001506181 043-47774344 HKG10885582 1.00 1
100233 NTS @0901019001506181 043-47774344 HKG10885582 1.00 0
100234 GSH @0901019001506587 618-82046274 205746 49.00 1
100235 GSH @0901019001506587 618-82046274 205746 49.00 0
100236 GSH @0901019001506587 618-82046274 205746 49.00 -2
100237 GSH @0901019001506587 618-82046274 205746 49.00 1
100238 GSH @0901019001506587 618-82046274 205746 49.00 -2
100239 GSH @0901019001506587 618-82046274 205746 49.00 1
100240 GSH @0901019001506587 618-82046274 205746 49.00 -1
100241 GSH @0901019001506244 781-51963656 4230159991 33.00 1
小弟写了一条语句
SELECT ROW_NUMBER() OVER(ORDER BY Rpt_HAWB) AS ID,Rpt_MAWB,Rpt_HAWB,Rpt_Pieces,Rpt_GrossWT FROM dbo.T_I_IMP_LIST GROUP BY Rpt_MAWB,Rpt_HAWB,Rpt_Pieces,Rpt_GrossWT结果如下 -------------------------------------------------------------------------------------1 618-82046274 205746 9 49.00
2 781-51963656 4230159991 1 33.00
3 043-47774344 HKG10885582 2 1.00
其实是这样的最后结果应为3条分别按他们显示出来的字段GROUP BY,但最后一列是这样的,如果他们GOURP组里有<0的就标示为一种状态,>0的就是另一种状态