多列字段有重复如何去重?

乖喵咪 2017-11-25 10:32:22
Select Distinct TD.DocEntry,TD.LineNum,
Case When TD1.BaseEntry=TM.DocEntry and TD1.BaseLineNum=TD.LineNum Then TM1.DocEntry end as RKDH,
Case When TD1.BaseEntry=TM.DocEntry and TD1.BaseLineNum=TD.LineNum Then TD1.LineNum end as HH
From ZZ_QGD TM
LEFT JOIN ZZ_QGDA TD on TD.DocEntry=TM.DocEntry
LEFT JOIN ZZ_QGDR TM1 on TM1
LEFT JOIN ZZ_QGDRA TD1 on TD1.DocEntry=TM1.DocEntry
Where TM.FKTK <> '现付'

申请单号和行号重复了,如何去重?

结果:
...全文
752 3 打赏 收藏 转发到动态 举报
写回复
用AI写文章
3 条回复
切换为时间正序
请发表友善的回复…
发表回复
中国风 2017-11-27
  • 打赏
  • 举报
回复
举例缺少条件为:TM1.DocEntry = TM.DocEntry 用优先连接的方式处理 用以下方法去测测
SELECT DISTINCT
        TD.DocEntry ,
        TD.LineNum ,
        CASE WHEN TD1.BaseEntry = TM.DocEntry
                  AND TD1.BaseLineNum = TD.LineNum THEN TM1.DocEntry
        END AS RKDH ,
        CASE WHEN TD1.BaseEntry = TM.DocEntry
                  AND TD1.BaseLineNum = TD.LineNum THEN TD1.LineNum
        END AS HH
FROM    ZZ_QGD TM
        LEFT JOIN ZZ_QGDA TD ON TD.DocEntry = TM.DocEntry
        LEFT JOIN (ZZ_QGDR TM1
        INNER JOIN ZZ_QGDRA TD1 ON TD1.DocEntry = TM1.DocEntry)  ON TM1.DocEntry = TM.DocEntry
WHERE   TM.FKTK <> '现付';
听雨停了 2017-11-27
  • 打赏
  • 举报
回复
引用 楼主 sielsn 的回复:
Select Distinct TD.DocEntry,TD.LineNum, Case When TD1.BaseEntry=TM.DocEntry and TD1.BaseLineNum=TD.LineNum Then TM1.DocEntry end as RKDH, Case When TD1.BaseEntry=TM.DocEntry and TD1.BaseLineNum=TD.LineNum Then TD1.LineNum end as HH From ZZ_QGD TM LEFT JOIN ZZ_QGDA TD on TD.DocEntry=TM.DocEntry LEFT JOIN ZZ_QGDR TM1 on TM1 LEFT JOIN ZZ_QGDRA TD1 on TD1.DocEntry=TM1.DocEntry Where TM.FKTK <> '现付' 申请单号和行号重复了,如何去重? 结果:
用一个row_number() over(partition by .. order by ..) as rn 函数,取值rn=1的结果,就可以去除重复行了
二月十六 2017-11-25
  • 打赏
  • 举报
回复
楼主给的语句有问题
Select Distinct TD.DocEntry,TD.LineNum,
Case When TD1.BaseEntry=TM.DocEntry and TD1.BaseLineNum=TD.LineNum Then TM1.DocEntry end as RKDH,
Case When TD1.BaseEntry=TM.DocEntry and TD1.BaseLineNum=TD.LineNum Then TD1.LineNum end as HH
From ZZ_QGD TM
LEFT JOIN ZZ_QGDA TD on TD.DocEntry=TM.DocEntry
LEFT JOIN ZZ_QGDR TM1 on TM1 --这里on后边没有条件
LEFT JOIN ZZ_QGDRA TD1 on TD1.DocEntry=TM1.DocEntry
Where TM.FKTK <> '现付'


去重的例子这样写:
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([DocEntry] int,[LineNum] int,[RKDH] int,[HH] int)
Insert #T
select 10018,6,null,null union all
select 10018,7,10021,9 union all
select 10018,7,null,null union all
select 10018,6,10021,8
Go
--测试数据结束
SELECT DocEntry ,
LineNum ,
RKDH ,
HH
FROM ( SELECT * ,
ROW_NUMBER() OVER ( PARTITION BY DocEntry, LineNum ORDER BY RKDH DESC ) AS num
FROM #T
) t
WHERE num = 1


588

社区成员

发帖
与我相关
我的任务
社区描述
提出问题
其他 技术论坛(原bbs)
社区管理员
  • community_281
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧