MSSQL去掉重复项的查询语句
发个问题,请大家帮解决一下,呵呵
环境:mssql
表:
id a b c d
1 1 2 3 90
2 3 2 2 89
3 1 3 3 76
4 3 8 4 54
5 2 9 5 53
6 2 4 2 31
上述为五列的一个示意表,所想达到的功能是以A列,去掉A中重复的项,并以D例按从大到小的顺序排列出来,想要的结果如下表所示:
id a b c d
1 1 2 3 90
2 3 2 2 89
5 2 9 5 53
请各位高手想一下,这句sql语句怎么写,谢谢啦
补充:朋友们,现在答案已经出来了,以下是非百分之百正确答案:
SELECT id, a, b, c, d
FROM a
WHERE (id IN
(SELECT MAX(ID) AS Expr1
FROM a AS test_1
GROUP BY a))
ORDER BY d DESC
详细的正确答案解说:请看
http://hi.zzzyk.com/loglab/blog/item/ac38f908c23fd9d763d98673.html
答案:select * from [表] where id in (select min(id) from [表] group by a) order by d desc
其他:select * from 表名 m where id=(select min(id) from 表名 where a=m.a )order by d desc select 表1.* from 表名 as 表1,(select a,min(id) as id from 表名 group by a) as 表2 where 表1.id=表2.id order by d 通过临时表来实现吧
select *,identity(int,1,1) as id into #A from 表名
select a,b,c,d
from #A m
where not exists(select 1 from #A n where m.a=n.a and n.id<m.id)
order by d desc
drop table #A
上一个:mssql中 关于列的数据较长如何查看全部的问题
下一个:mssql的异步运行指的是什么?