高分求SQL查询语句或者思路
数据库中的数据结构如下:
Item Type
it1 1
it1 5
it1 6
it2 2
it2 3
it3 4
it3 5
it4 8
要求输出有效的数据,要求TYPE中不能含有1, 2, 3的中任何一种:
输出结果为:
Item Type
it3 4
it3 5
it4 8
请高手指教!!!!

[解决办法]

SQL code

--随便写个Select * from tb twhere not exists(select 1 from tb where [Item]=t.[Item] and [Type]=1)and   not exists(select 1 from tb where [Item]=t.[Item] and [Type]=2)and   not exists(select 1 from tb where [Item]=t.[Item] and [Type]=3)
[解决办法]
SQL code
if object_id('[tb]') is not null drop table [tb]gocreate table [tb]([Item] varchar(3),[Type] int)insert [tb]select 'it1',1 union allselect 'it1',5 union allselect 'it1',6 union allselect 'it2',2 union allselect 'it2',3 union allselect 'it3',4 union allselect 'it3',5 union allselect 'it4',8select * from [tb] twhere not exists(select 1 from tb where item=t.item and type in(1,2,3))/*Item Type---- -----------it3  4it3  5it4  8(3 行受影响)*/