修正一下:表Z_Pro
结构是这样的
--------------------
P_Id P_Name
3 化学
4 物理
5 生物
6 外语结果仍然是:
--------------------------------
P_Id P_Name Title Count1 Count2
3 化学 问题三 3 2
4 物理 问题五 2 3 因为生物与外语没有问题
结构是这样的
--------------------
P_Id P_Name
3 化学
4 物理
5 生物
6 外语结果仍然是:
--------------------------------
P_Id P_Name Title Count1 Count2
3 化学 问题三 3 2
4 物理 问题五 2 3 因为生物与外语没有问题
a.P_Id,
a.P_Name,
Title =(select top 1 Title from Z_Inq where P_Id=a.P_Id order by id desc),
count1=(select count(*) from Z_Inq where P_Id=a.P_Id),
count2=(select count(*) from Z_Stuwhere P_Id=a.P_Id )
from
Z_Pro a
insert into @Z_Inq select 1,'问题一',3
insert into @Z_Inq select 2,'问题二',3
insert into @Z_Inq select 3,'问题三',3
insert into @Z_Inq select 4,'问题四',4
insert into @Z_Inq select 5,'问题五',4declare @Z_Pro table(P_Id int,P_Name varchar(10))
insert into @Z_Pro select 3,'化学'
insert into @Z_Pro select 4,'物理'
insert into @Z_Pro select 5,'生物'
insert into @Z_Pro select 6,'外语'declare @Z_Stu table(P_Id int,S_Name varchar(10))
insert into @Z_Stu select 3,'路人甲'
insert into @Z_Stu select 3,'路人乙'
insert into @Z_Stu select 4,'路人丙'
insert into @Z_Stu select 4,'路人丁'
insert into @Z_Stu select 4,'路人戊'select
a.P_Id,
a.P_Name,
Title =(select top 1 Title from @Z_Inq where P_Id=a.P_Id order by id desc),
count1=(select count(*) from @Z_Inq where P_Id=a.P_Id),
count2=(select count(*) from @Z_Stu where P_Id=a.P_Id )
from
@Z_Pro a
where
exists(select 1 from @Z_Inq where P_Id=a.P_Id)
or
exists(select 1 from @Z_Stu where P_Id=a.P_Id)
/*
P_Id P_Name Title count1 count2
----------- ---------- ---------- ----------- -----------
3 化学 问题三 3 2
4 物理 问题五 2 3
*/
p_id=p_id,
p_name=p_name,
title=(select title from z_inq where p_id=a.p_id and id=(select max(id) from z_inq where p_id=a.p_id)),
Count1=(select count(p_id) from z_stu where p_id=a.p_id)
--回答在哪呢 count2=( )
from z_pro a
select
p_id=p_id,
p_name=p_name,
title=(select title from z_inq where p_id=a.p_id and id=(select max(id) from z_inq where p_id=a.p_id)),
Count1=(select count(p_id) from z_inq where p_id=a.p_id)
count2=(select count(p_id) from z_stu where p_id=a.p_id)
from z_pro a
Declare @Z_Pro table(p_id int,p_name varchar(10))
Declare @Z_Stu table(p_id int,S_Name varchar(10))insert @Z_Inq
SELECT '1','问题1','3' union all
SELECT '2','问题2','3' union all
SELECT '3','问题3','3' union all
SELECT '4','问题4','4' union all
SELECT '5','问题5','4' insert @Z_Pro
SELECT '3','化学' union all
SELECT '4','物理' union all
SELECT '5','生物' union all
SELECT '5','外语'
insert @Z_Stu
SELECT '3','路人甲' union all
SELECT '3','路人乙' union all
SELECT '4','路人丙' union all
SELECT '4','路人丁' union all
SELECT '4','路人戊' select
a.P_Id,
a.P_Name,
Title =(select top 1 Title from @Z_Inq where P_Id=a.P_Id order by id desc),
count1=(select count(*) from @Z_Inq where P_Id=a.P_Id),
count2=(select count(*) from @Z_Stu where P_Id=a.P_Id )
from
@Z_Pro a
where
exists(select 1 from @Z_Inq where P_Id=a.P_Id)
or
exists(select 1 from @Z_Stu where P_Id=a.P_Id)
exists(select 1 from @Z_Inq where P_Id=a.P_Id)
or
exists(select 1 from @Z_Stu where P_Id=a.P_Id)
应该把
exists(select 1 from @Z_Inq where P_Id=a.P_Id)
or
只剩下
exists(select 1 from @Z_Stu where P_Id=a.P_Id)
如果没有人回答的话就说明没有问题,楼主既然追求速度你有何必多一次查询呢
a.P_Id,
a.P_Name,
Title =(select top 1 Title from @Z_Inq where P_Id=a.P_Id order by id desc),
count1=(select count(*) from @Z_Inq where P_Id=a.P_Id),
count2=(select count(*) from @Z_Stu where P_Id=a.P_Id )
from
@Z_Pro a
执行成本远远小于
select
a.P_Id,
a.P_Name,
Title =(select top 1 Title from @Z_Inq where P_Id=a.P_Id order by id desc),
count1=(select count(*) from @Z_Inq where P_Id=a.P_Id),
count2=(select count(*) from @Z_Stu where P_Id=a.P_Id )
from
@Z_Pro a
where
exists(select 1 from @Z_Inq where P_Id=a.P_Id)
or
exists(select 1 from @Z_Stu where P_Id=a.P_Id)
(select count(*) from Z_Stu s where s.P_Id=p.P_Id) count2
from Z_Pro p, Z_Inq i,
where p.P_Id=i.P_Id
group by p.P_Id, p.P_Name