大家好，这条sql语句要怎么些呢？

[rgbcn]

表结构是这样的，一个产品(ItemID)属于多个栏目的 我要把同属于多个栏目(SpecialID)的产品 找出来, 在itemid 不确定的情况下，我怎么把比如这些同时属于(SpecailID)46 51 52 的产品找出来,这里面有一条记录就是413，该sql 语句要怎么写？

谢谢了

[rgbcn]

表如下
InfoID ItemID SpecialID ModuleType
45775 413 46 5
45757 67 46 5
45756 152 46 5
45755 296 46 5
45754 314 46 5
45752 404 46 5
45751 401 46 5
45750 402 46 5
45749 747 46 5
41191 756 46 5
41190 755 46 5
576 295 46 5
45774 413 51 5
45773 413 52 5

[rgbcn]

表如下
InfoID-----ItemID-----SpecialID-----ModuleType
45775-----413-----46-----5
45757-----67------46-----5
45756-----152-----46-----5
45755-----296-----46-----5
45754-----314-----46-----5
45752-----404-----46-----5
45751-----401-----46-----5
45750-----402-----46-----5
45749-----747-----46-----5
41191-----756-----46-----5
41190-----755-----46-----5
576-------295-----46-----5
45774-----413-----51-----5
45773-----413-----52-----5

[htl258_Tony]

题意不太理解

[htl258_Tony]

楼主想得到的结果是?
最好能列出你想要的结果的表出来.

[rgbcn]

图片附件怎么打不开，我新开了一帖子，请看

http://topic.csdn.net/u/20090222/13/fbfaea32-e6af-4d37-af37-30aef157a17d.html?seed=1692616703

谢谢

[htl258_Tony]

declare @t table(InfoID int, ItemID int, SpecialID int, ModuleType int)
insert @t select 45775 ,413, 46, 5 
union all select 45757 ,67 ,46 ,5 
union all select 45756 ,152 ,46, 5 
union all select 45755 ,296 ,46 ,5 
union all select 45754 ,314 ,46, 5 
union all select 45752 ,404, 46, 5 
union all select 45751 ,401, 46 ,5 
union all select 45750, 402, 46, 5 
union all select 45749, 747, 46, 5 
union all select 41191, 756 ,46, 5 
union all select 41190 ,755, 46, 5 
union all select 576 ,295, 46, 5 
union all select 45774 ,413 ,51 ,5 
union all select 45773, 413 ,52 ,5 

select ItemID 
from @t 
group by  ItemID, 
          case when 
          SpecialID in(46,51,52) then 1 end 
having count( case when SpecialID in(46,51,52) then 1 end ) >2

[水族杰纶]

set nocount on
if object_id('tb')is not null drop table tb
go
create table tb(InfoID int, ItemID int, SpecialID int,ModuleType int) 
insert tb select 45775 ,413, 46, 5 
insert tb select 45757 ,67 ,46 ,5 
insert tb select 45756 ,152 ,46 ,5 
insert tb select 45755 ,296 ,46 ,5 
insert tb select 45754 ,314 ,46 ,5 
insert tb select 45752 ,404 ,46 ,5 
insert tb select 45751 ,401 ,46 ,5 
insert tb select 45750 ,402 ,46 ,5 
insert tb select 45749 ,747 ,46 ,5 
insert tb select 41191 ,756 ,46 ,5 
insert tb select 41190, 755 ,46, 5 
insert tb select 576 ,295 ,46 ,5 
insert tb select 45774, 413, 51, 5 
insert tb select 45773, 413, 52, 5 
select ItemID from (
select itemid,SpecialID from tb where SpecialID=46
union all
select itemid,SpecialID from tb where SpecialID=51
union all
select itemid,SpecialID from tb where SpecialID=52)T
group by itemid having count(*)=3
/*ItemID
-----------
413
*/

[htl258_Tony]

-->这个估计是楼主要的:
create table #(InfoID int, ItemID int, SpecialID int, ModuleType int)
insert  # select 45775 ,413, 46, 5 
union all select 45757 ,67 ,46 ,5 
union all select 45756 ,152 ,46, 5 
union all select 45755 ,296 ,46 ,5 
union all select 45754 ,314 ,46, 5 
union all select 45752 ,404, 46, 5 
union all select 45751 ,401, 46 ,5 
union all select 45750, 402, 46, 5 
union all select 45749, 747, 46, 5 
union all select 41191, 756 ,46, 5 
union all select 41190 ,755, 46, 5 
union all select 576 ,295, 46, 5 
union all select 45774 ,413 ,51 ,5 
union all select 45773, 413 ,52 ,5 
declare @SpecialID varchar(2000)
select @SpecialID=isnull(@SpecialID,'')+','+cast(SpecialID as varchar) from #
select @SpecialID=stuff(@SpecialID,1,1,'')
exec(
'select ItemID 
from # 
group by  ItemID, 
         case when cast(SpecialID as varchar) in('+@SpecialID+')
         then 1 end 
having count( case when cast(SpecialID as varchar) in('+@SpecialID+') then 1 end ) >1'
)

drop table #
-->结果
413

[htl258_Tony]

-->我把题改一下
create table #(InfoID int, ItemID int, SpecialID int, ModuleType int)
insert  # select 45775 ,413, 44, 5 
union all select 45757 ,67 ,43 ,5 
union all select 45756 ,412 ,42, 5 
union all select 45755 ,413 ,41 ,5 
union all select 45754 ,314 ,40, 5 
union all select 45752 ,404, 45, 5 
union all select 45751 ,314, 39 ,5 
union all select 45750, 412, 38, 5 
union all select 45749, 747, 37, 5 
union all select 41191, 756 ,36, 5 
union all select 41190 ,755, 46, 5 
union all select 576 ,295, 46, 5 
union all select 45774 ,413 ,51 ,5 
union all select 45773, 413 ,52 ,5 
/*
select * from #
InfoID      ItemID      SpecialID   ModuleType  
----------- ----------- ----------- ----------- 
45775       413         44          5
45757       67          43          5
45756       412         42          5
45755       413         41          5
45754       314         40          5
45752       404         45          5
45751       314         39          5
45750       412         38          5
45749       747         37          5
41191       756         36          5
41190       755         46          5
576         295         46          5
45774       413         51          5
45773       413         52          5

（所影响的行数为 14 行）
*/

declare @SpecialID varchar(2000)
select @SpecialID=isnull(@SpecialID,'')+','+cast(SpecialID as varchar) from #
select @SpecialID=stuff(@SpecialID,1,1,'')
exec(
'select ItemID 
from # 
group by  ItemID, 
         case when cast(SpecialID as varchar) in('+@SpecialID+')
         then 1 end 
having count( case when cast(SpecialID as varchar) in('+@SpecialID+') then 1 end ) >1'
)

drop table #

/*
ItemID      
----------- 
314
412
413
*/

[swwei2002]

select distinct ItemID from #tb a 
where (select count(*) from #tb b where a.SpecialID<>b.SpecialID and a.ItemID=b.ItemID)>0