select a,b,c from tb 得到如下结果
151 1 4.5
151 1 4.2
152 1 42.5
153 1 5.5
154 1 4.5
154 1 2.1
.
.a列可能会重复, B全部是一样, 那我只想得到,a不重复唯一的结果,可以怎么写语句呢?比如把上面的结果得到这样:
151 1 4.5
152 1 42.5
153 1 5.5
154 1 4.5
.
.
.
就是对a中重复的结果先显示第一个. 请高手指点 谢谢!
151 1 4.5
151 1 4.2
152 1 42.5
153 1 5.5
154 1 4.5
154 1 2.1
.
.a列可能会重复, B全部是一样, 那我只想得到,a不重复唯一的结果,可以怎么写语句呢?比如把上面的结果得到这样:
151 1 4.5
152 1 42.5
153 1 5.5
154 1 4.5
.
.
.
就是对a中重复的结果先显示第一个. 请高手指点 谢谢!
declare @tb table (a int,b int,c numeric(3,1))
insert into @tb
select 151,1,4.5 union all
select 151,1,4.2 union all
select 152,1,42.5 union all
select 153,1,5.5 union all
select 154,1,4.5 union all
select 154,1,2.1select * from @tb a
where c=(select max(c) from @tb where a=a.a)
order by a
/*
a b c
----------- ----------- ---------------------------------------
151 1 4.5
152 1 42.5
153 1 5.5
154 1 4.5
*/
;WITH cte AS
(
SELECT rowno=ROW_NUMBER() OVER(PARTITION BY a ORDER BY GETDATE()), * FROM tb
)
SELECT a,b,c FROM cte
WHERE rowno = 1
--#2.APPLY
SELECT b.* FROM
(SELECT DISTINCT a FROM tb) a
CROSS APPLY
(SELECT TOP(1) * FROM tb WHERE id = a.id) b
select a,b,c,identity(int,1,1) as [ID] into #tb from tb
select a,b,c from #tb t1 where id=(selec min(id) from #tb t2 where t1.a=t2.a)
*
from
(select *,row_number()over(partition by a order by getdate()) as id from tb)t
where
id=1
没有就用 row_number
insert into @tb
select 151,1,4.5 union all
select 151,1,4.2 union all
select 152,1,42.5 union all
select 153,1,5.5 union all
select 154,1,4.5 union all
select 154,1,2.1select distinct * from @tb a
where not exists(select 1 from @tb b where b.a = a.a and b.c > a.c)/*
151 1 4.5
152 1 42.5
153 1 5.5
154 1 4.5
*/