22,209
社区成员
发帖
与我相关
我的任务
分享
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
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
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
不要用我发给你的,就用你加了where条件的那个
再执行你的原来的语句,不要用我发给你的 [quote=引用 12 楼 u010615963 的回复:] [quote=引用 11 楼 DBA_Huangzj 的回复:] 执行一下:USE 库名 GO CREATE NONCLUSTERED INDEX NONCLUSTERED_INDEX_ls_jgb ON [dbo].[ls_jgb] ([MarketID],[FproductID]) INCLUDE ([GetDate],[yuetongbi],[yuehuanbi],[yuejunjia]) GO
[quote=引用 11 楼 DBA_Huangzj 的回复:] 执行一下:USE 库名 GO CREATE NONCLUSTERED INDEX NONCLUSTERED_INDEX_ls_jgb ON [dbo].[ls_jgb] ([MarketID],[FproductID]) INCLUDE ([GetDate],[yuetongbi],[yuehuanbi],[yuejunjia]) GO
执行一下:USE 库名 GO CREATE NONCLUSTERED INDEX NONCLUSTERED_INDEX_ls_jgb ON [dbo].[ls_jgb] ([MarketID],[FproductID]) INCLUDE ([GetDate],[yuetongbi],[yuehuanbi],[yuejunjia]) GO
USE 库名
GO
CREATE NONCLUSTERED INDEX NONCLUSTERED_INDEX_ls_jgb
ON [dbo].[ls_jgb] ([MarketID],[FproductID])
INCLUDE ([GetDate],[yuetongbi],[yuehuanbi],[yuejunjia])
GO
你换我私信的写法试试
看手机和私信。
600万的数据,没有where条件,15秒貌似挺优秀的....
执行计划上一下