SqlServer 多字段查询要求某个字段唯一不重复

最新推荐文章于 2024-06-04 14:01:45 发布

TivonaLH

最新推荐文章于 2024-06-04 14:01:45 发布

阅读量765

点赞数

分类专栏： SQL_SERVER 文章标签： sqlserver 数据库 sql

本文链接：https://blog.csdn.net/TivonaLH/article/details/129196722

版权

SQL_SERVER 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

with A as (
SELECT  ce.num,gr.groupnum,gr.grantnum,gr.GrantExplain,gr.ReceiverName,gr.ReceiverTel,gr.ReceiverUnit,gr.ReceiverCard,CONVERT(varchar(100), gr.GrantDate, 23) AS
 GrantDate,gr.filluser,gr.sendmode,ti.Name,eb.SysPrimaryKey,eb.Code,eb.ProjectNum,eb.ProjectName,eb.EntrustUnitName,eb.EntrustNum,
 eb.ReportNum,eb.SampleNum,eb.EntrustDate,eb.AcceptDate,eb.AcceptSampleMan,eb.StructPart,eb.Sizes  ,CONVERT(varchar(100), PrintDate, 23) 
 AS PrintDate,eb.needchargemoney,eb.explain,Signimage,eb.ReceiveDate,eb.TestStartDate,eb.ApproveDate,eb.AuditingDate,ISNULL(eb.IsExportGrantLedger,'') IsExportGrantLedger,
 eb.testitemparmname,eb.samplecount,eb.sendsampleman,eb.sendsamplemantle  ,eb.SampleName,ChargeStatus,CASE WHEN ChargeStatus='1' THEN '已收费' ELSE '未收费' END AS ChargeStatusStr
 ,eb.PrintMan,eb.sendsampleman2 ,contractkey   
 FROM GrantReport AS gr left JOIN GrantEntrustbills AS ge  ON gr.GrantKey=ge.GrantKey  
 left JOIN EntrustBills AS eb ON ge.Sysprimarykey=eb.SysPrimaryKey  left JOIN TestItems AS ti ON eb.Code=ti.Code
left JOIN  
				   (select count(1) as num ,ReportNum  from EntrustBills where ReportNum in (select  ReportNum from EntrustBills where SysPrimaryKey in ( select Sysprimarykey from GrantEntrustbills) ) group by ReportNum) as ce
				   on ce.ReportNum=eb.ReportNum
)

 select t.* from (select *,row_number() over (partition by ReportNum 
  order by EntrustDate) as group_idx from A) t where  t.group_idx= 1





例句：
SELECT * FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY 重复字段 ORDER BY [不重复字段]) AS rid,*
    FROM [表名]
) AS tt
WHERE tt.rid=1