select 1 id,2 jobid, 5 typeid into #test
union all select 2,3,10
union all select 3,3,10
union all select 4,3,10
union all select 5,3,10 select * from #test select * from #test
where jobid in(3) select * from #test
where jobid in(3,3,3) drop table #test
(5 行受影响)
id jobid typeid
----------- ----------- -----------
1 2 5
2 3 10
3 3 10
4 3 10
5 3 10 (5 行受影响) id jobid typeid
----------- ----------- -----------
2 3 10
3 3 10
4 3 10
5 3 10 (4 行受影响) id jobid typeid
----------- ----------- -----------
2 3 10
3 3 10
4 3 10
5 3 10 [/code]
你这里只涉及到一张表以下是我碰到的问题:
-----
if not object_id('#temp') is null
drop table #tempif not object_id('#TypeRelation') is null
drop table #TypeRelation-----
create table #temp(id int,EntID int,PerID int,jobID int)
insert into #temp
select 1 ,5 ,21 ,13
union all select 1,10,25,4
union all select 5,5,61,13
union all select 5,5,61,13create table #TypeRelation(jobID int,TypeID int)
insert into #TypeRelation
select 10 ,12
union all select 15,10
union all select 5,20
union all select 13,20
union all select 5,45select jobID from #temp where EntID=5
能得到和你一样的结果:
jobID
13
13
13select * from #TypeRelation where jobid in (select jobID from #temp where EntID=5)
这结果只有一个:
jobID TypeID
13 20这结果会在以下情况下碰到问题:
select tab.TypeID,count(*) as 统计数
from ( select * from #TypeRelation where jobid in (select jobID from #temp where EntID=5)) tab
group by tab.TypeID结果:
TypeID 统计数
20 1在EntID=5的情况下,有3个人的jobID=13,与jobID=13 相对的是TypeID=20,所以 统计数应为3
在以下情况能得到正确的结果:
select tab1.TypeID,count(*) as 统计数
from (select TypeID from #TypeRelation b inner join
(select jobID from #temp where EntID=5) a on b.jobID=a.jobID) tab1
group by tab1.TypeID结果:
TypeID 统计数
20 3drop table #temp
drop table #TypeRelation
可能我先前没表达清楚
union all select 2,3,10
union all select 3,3,10
union all select 4,3,10
union all select 5,3,10 select * from #test select * from #test
where jobid in(3) select * from #test
where jobid in(3,3,3) drop table #test
(5 行受影响)
id jobid typeid
----------- ----------- -----------
1 2 5
2 3 10
3 3 10
4 3 10
5 3 10 (5 行受影响) id jobid typeid
----------- ----------- -----------
2 3 10
3 3 10
4 3 10
5 3 10 (4 行受影响) id jobid typeid
----------- ----------- -----------
2 3 10
3 3 10
4 3 10
5 3 10 [/code]
你这里只涉及到一张表以下是我碰到的问题:
-----
if not object_id('#temp') is null
drop table #tempif not object_id('#TypeRelation') is null
drop table #TypeRelation-----
create table #temp(id int,EntID int,PerID int,jobID int)
insert into #temp
select 1 ,5 ,21 ,13
union all select 1,10,25,4
union all select 5,5,61,13
union all select 5,5,61,13create table #TypeRelation(jobID int,TypeID int)
insert into #TypeRelation
select 10 ,12
union all select 15,10
union all select 5,20
union all select 13,20
union all select 5,45select jobID from #temp where EntID=5
能得到和你一样的结果:
jobID
13
13
13select * from #TypeRelation where jobid in (select jobID from #temp where EntID=5)
这结果只有一个:
jobID TypeID
13 20这结果会在以下情况下碰到问题:
select tab.TypeID,count(*) as 统计数
from ( select * from #TypeRelation where jobid in (select jobID from #temp where EntID=5)) tab
group by tab.TypeID结果:
TypeID 统计数
20 1在EntID=5的情况下,有3个人的jobID=13,与jobID=13 相对的是TypeID=20,所以 统计数应为3
在以下情况能得到正确的结果:
select tab1.TypeID,count(*) as 统计数
from (select TypeID from #TypeRelation b inner join
(select jobID from #temp where EntID=5) a on b.jobID=a.jobID) tab1
group by tab1.TypeID结果:
TypeID 统计数
20 3drop table #temp
drop table #TypeRelation
可能我先前没表达清楚
select tab.TypeID,count(*) as 统计数
from ( select * from #TypeRelation where jobid in (select jobID from #temp where EntID=5)) tab
group by tab.TypeID结果:
TypeID 统计数
20 1这肯定是1啊,你没看你查出来是1条?
select A.TypeID, count(*)
from #TypeRelation A inner join #temp B
on A.jobID=B.jobID
group by A.TypeID
insert into #temp
select 1 ,5 , 21 ,13
union all select 1, 10, 25, 4
union all select 5, 5, 61, 13
union all select 5, 5, 61, 13create table #TypeRelation(jobID int,TypeID int)
insert into #TypeRelation
select 10 ,12
union all select 15,10
union all select 5,20
union all select 13,20
union all select 5,45select b.TypeID,count(1)as 数量 from #temp a join #TypeRelation b
on a.jobID=b.jobID
where a.EntID=5
group by b.TypeIDTypeID 数量
20 3
所以问题就没详细描述
http://topic.csdn.net/u/20080714/12/dc29d2ac-b8e5-48e2-9bee-2e1684d777de.html?seed=779225264
是的,不会出现
create table #temp(id int,EntID int,PerID int,jobID int)
insert into #temp
select 1 ,5 ,21 ,13
union all select 1,10,25,4
union all select 5,5,61,13
union all select 5,5,61,13create table #TypeRelation(jobID int,TypeID int)
insert into #TypeRelation
select 10 ,12
union all select 15,10
union all select 5,20
union all select 13,20
union all select 5,45
go
select tab1.TypeID,count(*) as 统计数
from (select TypeID from #TypeRelation b inner join
(select distinct jobID from #temp where EntID=5) a on b.jobID=a.jobID) tab1
group by tab1.TypeID/*TypeID 统计数
----------- -----------
20 1(所影响的行数为 1 行)
*/
if not object_id('#temp') is null
drop table #tempif not object_id('#TypeRelation') is null
drop table #TypeRelation-----
select tab.TypeID,count(*) as 统计数
from ( select * from #TypeRelation where jobid in (select jobID from #temp where EntID=5)) tab
group by tab.TypeID所以结果集tab里面只有1条记录,结果当然就只有1个想完成你的预想功能需要join一下