22,209
社区成员
发帖
与我相关
我的任务
分享
if OBJECT_ID('tempdb..#temp', 'u') is not null drop table #temp;
go
create table #temp( [a] varchar(100), [b] varchar(100), [c] varchar(100));
insert #temp
select '*','*','星期1' union all
select '*','*','星期1' union all
select '*','*','星期2' union all
select '*','*','星期3' union all
select '*','*','星期2'
--#1.楼主给的数据不全,其实还有其它几种写法:
SELECT b.* FROM
(select DISTINCT c from #temp) a
CROSS APPLY
(SELECT TOP(1) * FROM #temp m WHERE m.c=a.c ORDER BY GETDATE()) b
--#2.
;WITH cte AS
(
SELECT rowid =ROW_NUMBER() OVER(PARTITION BY c ORDER BY GETDATE()), * FROM #temp
)
SELECT a,b,c FROM cte
WHERE rowid = 1
/*
a b c
* * 星期1
* * 星期2
* * 星期3
*/
[/quote]
比如现在又3个字段,现在有5条数据
a b c
* * 3
* * 3
* * 0
* * 0
* * 1
我需要查出来是三条
* * 3
* * 0
* * 1
具体是哪一条没关系,只要C字段的值不重复就行
if OBJECT_ID('tempdb..#temp', 'u') is not null drop table #temp;
go
create table #temp( [a] varchar(100), [b] varchar(100), [c] varchar(100));
insert #temp
select '*','*','星期1' union all
select '*','*','星期1' union all
select '*','*','星期2' union all
select '*','*','星期3' union all
select '*','*','星期2'
--#1.楼主给的数据不全,其实还有其它几种写法:
SELECT b.* FROM
(select DISTINCT c from #temp) a
CROSS APPLY
(SELECT TOP(1) * FROM #temp m WHERE m.c=a.c ORDER BY GETDATE()) b
--#2.
;WITH cte AS
(
SELECT rowid =ROW_NUMBER() OVER(PARTITION BY c ORDER BY GETDATE()), * FROM #temp
)
SELECT a,b,c FROM cte
WHERE rowid = 1
/*
a b c
* * 星期1
* * 星期2
* * 星期3
*/
SELECT
fieldA,
fieldB,
MAX(fieldC),
MAX(fieldD)
FROM tb
GROUP BY
fieldA,
fieldB