27,580
社区成员
发帖
与我相关
我的任务
分享
select * from #t where Card# in (
select Card# from #t group by Card# having count(Card#)=1)
go
--假设表为#t
select * from #t where Card# in (
select Card# from #t group by Card# having count(length)=1)
go
用ROW_NUMBER也一样可以去重,取不重复。;WITH cte as (
SELECT ROW_NUMBER() OVER (PARTITION BY Card# ORDER BY (SELECT 1) ) AS rid
,T.[Ticket#]
,T.Workshop
,T.[Card#]
FROM Test.dbo.TicketDetailData125 T
)
SELECT * FROM cte AS a
WHERE NOT EXISTS(
SELECT 1 FROM cte AS b WHERE b.rid>1 AND a.card#=b.card#
)
ORDER BY Card#
SELECT * FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY REPLACE(REPLACE(REPLACE([Card#], CHAR(10), ''), CHAR(13), ''),CHAR(9),'')
ORDER BY (SELECT 1)) AS rid
,T.[Ticket#]
,T.Workshop
,T.[Card#]
FROM Test.dbo.TicketDetailData125 T
) AS tt
WHERE tt.rid=1
ORDER BY tt.Workshop
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([Ticket] int,[Card] int)
Insert #T
select 20,150 union all
select 20,150 union all
select 20,231
Go
--测试数据结束
;
with list as(
select [Ticket],[Card],rid=ROW_NUMBER()over(partition by [Card] order by [Card])
from #T
)
select lt.*
from list lt
left join list li on lt.Card=li.Card and li.rid=2
where lt.rid=1 and li.rid is null
Ticket Card rid
----------- ----------- --------------------
20 231 1
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([Ticket] int,[Card] int)
Insert #T
select 20,150 union all
select 20,150 union all
select 20,231
Go
--测试数据结束
SELECT *
FROM
(
SELECT *,
COUNT(1) OVER (PARTITION BY Card ORDER BY GETDATE()) rn
FROM #T
) t
WHERE rn = 1;
SELECT * FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY REPLACE(REPLACE(REPLACE([Card#], CHAR(10), ''), CHAR(13), ''),CHAR(9),'')
ORDER BY (SELECT 1)) AS rid
,*
FROM tableName
) AS tt
WHERE tt.rid=1
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([Ticket] int,[Card] int)
Insert #T
select 20,150 union all
select 20,150 union all
select 20,231
Go
--测试数据结束
SELECT *
FROM
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY Card ORDER BY GETDATE()) rn
FROM #T
) t
WHERE rn = 1;