数据表：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*/