SQL 小问解决思路

SQL 小问

有数据表：

ID Company Name Mobile
-------------------------------------
0 IBM R 123456
1 KDO P 987654
2 JCN S 234567
3 KDO K 345678
4 IBM L 654321
5 HAL W 765432




选出下表：(将 Company 重复的记录只留任意一个)

ID Company Name Mobile
-------------------------------------
0 IBM R 123456
1 KDO K 345678
2 JCN S 234567
5 HAL W 765432



[解决办法]

SQL code

--> 测试数据：[test]if object_id('[test]') is not null drop table [test]create table [test]([ID] int,[Company] varchar(3),[Name] varchar(1),[Mobile] int)insert [test]select 0,'IBM','R',123456 union allselect 1,'KDO','P',987654 union allselect 2,'JCN','S',234567 union allselect 3,'KDO','K',345678 union allselect 4,'IBM','L',654321 union allselect 5,'HAL','W',765432select * from test awhere [ID]=(select MIN([ID]) from test b where a.Company=b.Company)order by ID/*ID    Company    Name    Mobile0    IBM    R    1234561    KDO    P    9876542    JCN    S    2345675    HAL    W    765432*/
[解决办法]
SQL code
--> 测试数据：[test]if object_id('[test]') is not null drop table [test]create table [test]([ID] int,[Company] varchar(3),[Name] varchar(1),[Mobile] int)insert [test]select 0,'IBM','R',123456 union allselect 1,'KDO','P',987654 union allselect 2,'JCN','S',234567 union allselect 3,'KDO','K',345678 union allselect 4,'IBM','L',654321 union allselect 5,'HAL','W',765432--2005以上版本with tas(select px=ROW_NUMBER()over(partition by Company order by id),* from test)select ID,Company,Name,Mobile from twhere px=1order by id/*ID    Company    Name    Mobile0    IBM    R    1234561    KDO    P    9876542    JCN    S    2345675    HAL    W    765432*/