如何下SQL，满足不同属性的同一记录？？？？

[Petergepeter]

有表A，两个COLUMN：ITEM，TYPE。
ITEM TYPE
A 1
A 2
B 2
B 0
C 1
C 0
---------------------------------------
我要找到同时满足TYPE=1，=0的，同一个ITEM。
这里是 C。
---------------------------------------
CREATE TABLE A
(ITEM CHAR(1),
TYPE INT)

INSERT A
VALUES('A',1)
INSERT A
VALUES('A',2)
INSERT A
VALUES('B',2)
INSERT A
VALUES('B',0)
INSERT A
VALUES('C',1)
INSERT A
VALUES('C',0)

[yesterday2000]

select * from a where item in (
select item from a where type=1 or type=0
group by item
having count(*)>1 )

[zjcxc]

select * from A
where TYPE=1 and exists(
select * from A aa
where aa.ITEM=a.ITEM
and TYPE=0)

[zjcxc]

--或者(这样可以完整的显示出记录):
select a.*
from A,(
select a.ITEM
from(select distinct ITEM from A where TYPE=1)a
,(select distinct ITEM from A where TYPE=0)b
where a.ITEM=b.ITEM
)b where a.ITEM=b.ITEM

[cxingh]

select distinct item from a where type in(0,1)
group by item
having count(*)>1

[zjcxc]

--或者(考虑到同一item,type的值会重复)
select a.* from a,(
select item from a where type=1 or type=0
group by item
having count(distinct type)>1
)b where a.item=b.item

[Petergepeter]

感谢两位，
不过这里有个前提，就是 TYPE=1，=0这个个数是不定的。
TO yesterday2000：
效率会不会差。
TO zjcxc：
那就要动态的嵌套了。
有没有更好的呢？

[lsxaa]

select aa.item
from (select distinct * from A) aa
where type in(1,0)
group by aa.item having count(*)>1

[zjcxc]

--处理的通用存储过程
create proc p_qry
@typelist varchar(1000) --用,分隔的type列表
as
set nocount on
declare @s nvarchar(4000),@i int
select @s='
select a.* from a,(
select item from a
where type in('+@typelist+')
group by item
having count(distinct type)>@i
)b where a.item=b.item'
,@i=len(@typelist)-len(replace(@typelist,',',''))
exec sp_executesql @s,N'@i int',@i
go

[zjcxc]

--处理的通用存储过程
create proc p_qry
@typelist varchar(1000) --用,分隔的type列表
as
set nocount on
declare @s nvarchar(4000),@i int
select @s='
select a.* from a,(
select item from a
where type in('+@typelist+')
group by item
having count(distinct type)>@i
)b where a.item=b.item'
,@i=len(@typelist)-len(replace(@typelist,',',''))
exec sp_executesql @s,N'@i int',@i
go

--测试
CREATE TABLE A(ITEM CHAR(1),TYPE INT)
INSERT A VALUES('A',1)
INSERT A VALUES('A',2)
INSERT A VALUES('A',3)
INSERT A VALUES('B',2)
INSERT A VALUES('B',0)
INSERT A VALUES('C',1)
INSERT A VALUES('C',0)
INSERT A VALUES('C',2)
INSERT A VALUES('C',3)
go

--调用存储过程来查询
exec p_qry '1,2,3'
go

--删除测试环境
drop proc p_qry
drop table A

/*--测试结果

ITEM TYPE
---- -----------
A 1
A 2
A 3
C 1
C 0
C 2
C 3
--*/