各位大侠进来看看，这句sql该怎么优化，自己看了都很恶心。。

Question

我的本意是要在：
SELECT a.id,a.wizard_id,a.challenge_number,a.status,a.[file_id],b.title,b.date_deadline,b.award_amount,b.created_by 
FROM challenges a 
INNER JOIN challenges_version b ON a.id=b.challenge_id 
WHERE b.version=a.current_version
这个集后面添加3列。。3列的数据来自3个子查询。
子查询1：
SELECT COUNT(*) as workingon,challenge_id 
FROM dbo.challenge_engagements 
GROUP BY challenge_id 
子查许2：
SELECT COUNT(*) as totoalsubmissions,challenge_id as testid 
FROM dbo.submissions 
GROUP BY challenge_id
子查询3：
SELECT COUNT(*) as totalsubmissions,challenge_id as test2id 
FROM dbo.submissions WHERE status='Forwarded'
GROUP BY challenge_id 因为涉及到排序，所以只能用一条sql语句。。
前台数据显示用gridview + sqlDatasource展现下面是我写的sql语句，总觉得层数太多了SELECT id,wizard_id,challenge_number,status,[file_id],title,date_deadline,award_amount,ISNULL(workingon,0) AS 'working on',ISNULL(totalSubmissions,0) AS 'total submissions',created_by FROM (
SELECT * FROM 
(
SELECT COUNT(*) as workingon,challenge_id 
FROM dbo.challenge_engagements 
GROUP BY challenge_id 
) c RIGHT OUTER JOIN 
(
SELECT a.id,a.wizard_id,a.challenge_number,a.status,a.[file_id],b.title,b.date_deadline,b.award_amount,b.created_by 
FROM challenges a 
INNER JOIN challenges_version b ON a.id=b.challenge_id 
WHERE b.version=a.current_version
) d ON c.challenge_id = d.id 
      ) e RIGHT OUTER JOIN 
        (
SELECT COUNT(*) as totoalsubmissions,challenge_id as testid 
FROM dbo.submissions 
GROUP BY challenge_id
) f ON e.id=f.testid LEFT JOIN 
(SELECT COUNT(*) as totalsubmissions,challenge_id as test2id 
FROM dbo.submissions WHERE status='Forwarded'
GROUP BY challenge_id 
) g ON e.id = g.test2id

Answer 1

补充：
SELECT a.id,a.wizard_id,a.challenge_number,a.status,a.[file_id],b.title,b.date_deadline,b.award_amount,b.created_by 
FROM challenges a 
INNER JOIN challenges_version b ON a.id=b.challenge_id 
WHERE b.version=a.current_version
的a.id对应3个子查询的challenge_id

Answer 2

呵呵。。自己解决了，把子查询写成了函数。。
FUNCTION dbo.f_Getcount 
(
@challenge_id bigint,
@type int
)
RETURNS int
AS
BEGIN
DECLARE @count int
IF @type = 0
SELECT @count = COUNT(*) FROM challenge_engagements WHERE challenge_id=@challenge_id
ELSE IF @type = 1
SELECT @count = COUNT(*) FROM submissions WHERE challenge_id=@challenge_id
ELSE
SELECT @count = COUNT(*) FROM submissions WHERE challenge_id=@challenge_id AND status='Forwarded'
RETURN @count
END

Answer 3

然后
SELECT a.id,a.wizard_id,a.challenge_number,a.status,a.[file_id],b.title,b.date_deadline,b.award_amount,b.created_by,dbo.f_getcount(a.id,0) as 'working on',dbo.f_getcount(a.id,1) as 'submissions',dbo.f_getcount(a.id,2) as 'forwarded'
FROM challenges a 
INNER JOIN challenges_version b ON a.id=b.challenge_id 
WHERE b.version=a.current_version通过，比上面的好看多了，hoho..

Answer 4

这样吧，看完写出来太费时间，大概浏览后给三个感性的认识
1、
子查许2：
SELECT COUNT(*) as totoalsubmissions,challenge_id as testid 
FROM dbo.submissions 
GROUP BY challenge_id
子查询3：
SELECT COUNT(*) as totalsubmissions,challenge_id as test2id 
FROM dbo.submissions WHERE status='Forwarded'
GROUP BY challenge_id 
可一合并成
SELECT count(*) as totalsubmissions,sum(case when status='Forwarded' then 1 else 0 end) as totalsubmissions1,challenge_id 
FROM dbo.submissions 
GROUP BY challenge_id 2、感觉你的子查询层次写多了，应该可一写到两层
3、可以直接用字查询连接原来的语句，这样只需要一层的子查询