这个是view的sql
SELECT SUM(CONVERT(money, yavg)) / SUM(CONVERT(int, DAYS)) AS yavg, FproductID, NF, FproductName, MarketID
FROM (SELECT SUBSTRING(CONVERT(varchar(10), DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, GetDate) + 1, 0)), 20), 9, 9) AS DAYS,
SUBSTRING(CONVERT(varchar(10), DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, GetDate) + 1, 0)), 20), 9, 9) * yuejunjia AS yavg,
SUBSTRING(CONVERT(varchar(10), DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, GetDate) + 1, 0)), 20), 1, 4) AS NF, FproductID,
FproductName, MarketID
FROM (SELECT DISTINCT c.FproductName, b.GetDate, b.yuehuanbi, b.yuetongbi, b.yuejunjia, b.FproductID, b.MarketID
FROM dbo.ls_scb AS a INNER JOIN
dbo.ls_jgb AS b ON a.MarketID = b.MarketID INNER JOIN
dbo.ls_pzb AS c ON b.FproductID = c.FproductID) AS s) AS a
GROUP BY FproductID, NF, FproductName, MarketID
另外一个view 套上面一个视图
SELECT v1.NF, CAST(v1.yavg AS numeric(16, 2)) AS yavg, v1.FproductID, CAST((v1.yavg - v2.yavg) / v2.yavg * 100 AS numeric(16, 2)) AS yhb, v1.FproductName,
v1.marketid
FROM dbo.v_alllsyear_avg AS v1 LEFT OUTER JOIN
dbo.v_alllsyear_avg AS v2 ON v1.NF = v2.NF + 1 AND v1.FproductID = v2.FproductID AND v1.marketid = v2.marketid
这个是执行的sql 执行完15秒
sELECT *
FROM
ls_scb AS a
inner JOIN ls_jgb AS b ON a.MarketID = b.MarketID
inner JOIN ls_pzb AS c ON b.FproductID = c.FproductID
left join v_alllsyear_hb v on v.fproductid=b.fproductid and v.nf=substring(convert(varchar(10),b.getdate),7,9) and v.marketid=b.marketid
------解决方案--------------------
执行计划上一下