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

[ldw701]

我的本意是要在：
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

[ldw701]

补充：
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

[ldw701]

呵呵。。自己解决了，把子查询写成了函数。。
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

[ldw701]

然后
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..

[xiaoku]

那把分给我把！

[xyxfly]

^_^

[jaway]

只为了好看,不是吧!

[zicxc]

好看不一定好用

用函数要小心，效率可能成问题

[specialsoldier]

支持楼上,函数硬生生阻断了优化器的全局优化考虑

[zicxc]

这样吧，看完写出来太费时间，大概浏览后给三个感性的认识
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、可以直接用字查询连接原来的语句，这样只需要一层的子查询

[jiechifeiniao]

据说函数很可能会降低效率,

[yjlhch]

学一点