新闻特性实现,这样设计有点问题,求助
SQLSERVER

News_Special   新闻特性关系表
NewsId   SpecialId(聚集索引)
1 22
1 30
2 30
3 123
4 50
4 22
4 30
5 22
5 60
6 22
6 30


同时具有   SpecialId=22   和   SpecialId=30   特性的NewsId(不重复)
也就是结果:
1
4
6

目前想到的方法
1.
Select   NewsId   From   News_Special   Where   SpecialId=22   And   NewsId   In(Select   NewsId   From   News_Special   Where  

SpecialId=30)

2.
Select   NewsId   From   News_Special   Where   SpecialId=22   or   SpecialId=30   group   by   NewsId   Having  

Count(NewsId)=2

不知道还有什么简单点或效率更高的方法,
当查询同时具有多个特性的时候,不知道效率如何.
可以修改表的结构索引,只要能实现类似查询.


------解决方案--------------------
create table News_Special(NewsId int, SpecialId int)
insert News_Special select 1, 22
union all select 1, 30
union all select 2, 30
union all select 3, 123
union all select 4, 50
union all select 4, 22
union all select 4, 30
union all select 5, 22
union all select 5, 60
union all select 6, 22
union all select 6, 30

select distinct NewsID from News_Special tmpA
where (select count(distinct SpecialId) from News_Special where NewsId=tmpA.NewsId and (SpecialId=22 or SpecialId=30) )> 1

--result
NewsID
-----------
1
4
6

(3 row(s) affected)
------解决方案--------------------
--当SpecialId会有重复时，可改为如下:

Select NewsID from (
Select NewsId,rID=1 From News_Special Where SpecialId=22
Union all
Select NewsId,2 From News_Special Where SpecialId=30
) t group by NewsID having count(distinct rID)> =2
------解决方案--------------------
select a.NewsID from
(select NewsID from News_Special where SpecialId=22) a,
(select NewsID from News_Special where SpecialId=30) b
where a.NewsID=b.NewsID group by a.NewsID
------解决方案--------------------
select out.NewsID from News_Special out
where
exists(select 1 from News_Special in1 where in1.NewsID =out.NewsID and in1.SpecialId = 22)
and
exists(select 1 from News_Special in2 where in2.NewsID =out.NewsID and in2.SpecialId = 30)
------解决方案--------------------
declare @News_Special table (NewsId int, SpecialId int)
insert @News_Special select 1,22
union all select 1,30
union all select 2,30
union all select 3,123
union all select 4,50
union all select 4,22
union all select 4,30
union all select 5,22
union all select 5,60
union all select 6,22
union all select 6,30

select NewsId from @News_Special a
where exists
(select 1 from @News_Special where SpecialId in(22,30)
and NewsId=a.NewsId and SpecialId=a.SpecialId
group by NewsId,SpecialId having count(*)!> 1)
group by NewsId having count(*)=2

（所影响的行数为 11 行）

NewsId
-----------
1
4
6

（所影响的行数为 3 行）