过滤数据重复的问题。
数据表：table1
id name
1 AA333AB
2 AA555AB
3 BB666AC
4 BB555AC
5 CC777
...

-----------------------------

查询结果：
id name
1 AA333AB
2 AA555AB
3 BB666AC
5 CC777
...


-----------------------------


第二条数据和第四条数据中间的数据编号是一样的，我想把中间三个数字的相同数据过滤重复，这得怎么写？




[解决办法]

SQL code

--> 测试数据：[tb]IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]GO CREATE TABLE [tb]([id] INT,[name] VARCHAR(7))INSERT [tb]SELECT 1,'AA333AB' UNION ALLSELECT 2,'AA555AB' UNION ALLSELECT 3,'BB666AC' UNION ALLSELECT 4,'BB555AC' UNION ALLSELECT 5,'CC777'--------------开始查询--------------------------SELECT * FROM [tb] AS tWHERE NOT EXISTS (SELECT 1 FROM tb WHERE SUBSTRING([name],3,3)=SUBSTRING(t.[name],3,3) AND id<t.id )----------------结果----------------------------/* id          name----------- -------1           AA333AB2           AA555AB3           BB666AC5           CC777(4 行受影响)*/
[解决办法]
SQL code
select * from tb t where id=(select min(id) from tb where substring(name,3,3)=substring(t.name,3,3))
[解决办法]
SQL code
DECLARE @TABLE1 TABLE([ID] INT,[NAME] VARCHAR(7))INSERT @TABLE1SELECT 1,'AA333AB' UNION ALLSELECT 2,'AA555AB' UNION ALLSELECT 3,'BB666AC' UNION ALLSELECT 4,'BB555AC' UNION ALLSELECT 5,'CC777'SELECT * FROM @TABLE1 TWHERE ID=(SELECT MIN(ID) FROM @TABLE1 WHERE SUBSTRING(NAME,3,3)=SUBSTRING(T.NAME,3,3))/*ID          NAME----------- -------1           AA333AB2           AA555AB3           BB666AC5           CC777*/