语句优化的问题，也许还有更简便的方法~~

[richweb123]

语法分析基本上是正确的，能执行，就是结果有问题，估计是太长了，我是想统计一个2011年每月分布在各个【片区ID】的一个数据，所以其中运用的自定义函数，主要是完成select 节点ID，count(*) 的作用，最后出的结果发现一些数据很大，明显就是错误的，实在无解了，请大家给点修改意见以及解决方法……感激~~~

SELECT a.片区ID, YEAR(GETDATE()) AS 统计年份, MONTH(GETDATE()) AS 统计月份,
GETDATE() AS 统计日期, b.[2011-1], b.[2011-2], b.[2011-3], b.[2011-4], b.[2011-5],
b.[2011-6], b.[2011-7], b.[2011-8], b.[2011-9], b.[2011-10], b.[2011-11], b.[2011-12],
b.[2011年以后]
FROM 片区网络基本信息表 a LEFT OUTER JOIN
(SELECT 片区ID, SUM(b.[2011-1]) AS [2011-1], SUM(b.[2011-2]) AS [2011-2],
SUM(b.[2011-3]) AS [2011-3], SUM(b.[2011-4]) AS [2011-4], SUM(b.[2011-5])
AS [2011-5], SUM(b.[2011-6]) AS [2011-6], SUM(b.[2011-7]) AS [2011-7],
SUM(b.[2011-8]) AS [2011-8], SUM(b.[2011-9]) AS [2011-9], SUM(b.[2011-10])
AS [2011-10], SUM(b.[2011-11]) AS [2011-11], SUM(b.[2011-12]) AS [2011-12],
SUM(b.[2011年以后]) AS [2011年以后]
FROM 片区网络基本信息表 a LEFT OUTER JOIN
(SELECT a.所属项目, q.[2011-1], d .[2011-2], e.[2011-3], f.[2011-4],
r.[2011-5], s.[2011-6], t .[2011-7], u.[2011-8], v.[2011-9], w.[2011-10],
x.[2011-11], y.[2011-12], z.[2011年以后]
FROM 网络资源信息表 a LEFT OUTER JOIN
(SELECT 节点ID, CN AS [2011-1]
FROM CountTab2('2011-1-1', '2011-1-31')) q ON a.节点ID = q.节点ID LEFT OUTER JOIN
(SELECT 节点ID, CN AS [2011-2]
FROM CountTab2('2011-2-1', '2011-2-28')) d ON a.节点ID = d .节点ID LEFT OUTER JOIN
(SELECT 节点ID, CN AS [2011-3]
FROM CountTab2('2011-3-1', '2011-3-31')) e ON a.节点ID = d .节点ID LEFT OUTER JOIN
(SELECT 节点ID, CN AS [2011-4]
FROM CountTab2('2011-4-1', '2011-4-30')) f ON a.节点ID = f.节点ID LEFT OUTER JOIN
(SELECT 节点ID, CN AS [2011-5]
FROM CountTab2('2011-5-1', '2011-5-31')) r ON a.节点ID = r.节点ID LEFT OUTER JOIN
(SELECT 节点ID, CN AS [2011-6]
FROM CountTab2('2011-6-1', '2011-6-30')) s ON a.节点ID = s.节点ID LEFT OUTER JOIN
(SELECT 节点ID, CN AS [2011-7]
FROM CountTab2('2011-7-1', '2011-7-31')) t ON a.节点ID = t .节点ID LEFT OUTER JOIN
(SELECT 节点ID, CN AS [2011-8]
FROM CountTab2('2011-8-1', '2011-8-31')) u ON a.节点ID = u.节点ID LEFT OUTER JOIN
(SELECT 节点ID, CN AS [2011-9]
FROM CountTab2('2011-9-1', '2011-9-30')) v ON a.节点ID = v.节点ID LEFT OUTER JOIN
(SELECT 节点ID, CN AS [2011-10]
FROM CountTab2('2011-10-1', '2011-10-31')) w ON a.节点ID = w.节点ID LEFT OUTER JOIN
(SELECT 节点ID, CN AS [2011-11]
FROM CountTab2('2011-11-1', '2011-11-30')) x ON a.节点ID = x.节点ID LEFT OUTER JOIN
(SELECT 节点ID, CN AS [2011-12]
FROM CountTab2('2011-12-1', '2011-12-31')) y ON a.节点ID = y.节点ID LEFT OUTER JOIN
(SELECT 节点ID, CN AS [2011年以后]
FROM CountTab2('2012-1-1', '2015-12-31')) z ON a.节点ID = z.节点ID) b ON
a.片区ID = b.所属项目
GROUP BY 片区ID) b ON a.片区ID = b.片区ID

[AcHerat]

看不下去了！

[liangCK]

把CountTab2表值函数代码发出来看看。

[richweb123]

CREATE FUNCTION CountTab2(@bdate nvarchar(20),@edate nvarchar(20))
RETURNS TABLE
as
return SELECT 节点ID,COUNT(*) AS CN FROM 用户资源信息表 WHERE 交费截止日期 BETWEEN @bdate AND @edate AND 账户名称 = '主机' AND 用户状态 = '使用' GROUP BY 节点ID

[claro]

基本上，优化的原则是简化语句。

什么是简化语句？？你想想就会明白的。

对于部分语句的表，可以暂时放到#tempdb中，通常不要超过5个以上的Join.

[--小F--]

[--小F--]

无数的嵌套

[liangCK]

后面一连串使用自定义表值函数都是多余的。

[快溜]

[Quote=引用 3 楼 richweb123 的回复:]
CREATE FUNCTION CountTab2(@bdate nvarchar(20),@edate nvarchar(20))
RETURNS TABLE
as
return SELECT 节点ID,COUNT(*) AS CN FROM 用户资源信息表 WHERE 交费截止日期 BETWEEN @bdate AND @edate AND 账户名称 = '主机' AND 用户状态 ……
[/Quote]聚合太多了，效率会慢下来。