22,209
社区成员
发帖
与我相关
我的任务
分享
--缓存数据到临时表
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp
SELECT rr_fee, cz_times, c_code
INTO #tmp
FROM Sys_ReplenishRecord
WHERE 1=1
AND rr_time >= '2017-7-1 00:00:00'
AND rr_time <= '2017-7-31 23:59:59'
AND rr_state <> '-2'
CREATE CLUSTERED INDEX ix_tmp_code ON #tmp( c_code )
--查询
SELECT c_name,
c_bank,
c_account,
c_tag,
(
SELECT SUM(rr_fee)
FROM #tmp t
WHERE t.c_code IN (SELECT c_code
FROM sys_ct
WHERE sys_ct.opt_id = com.opt_id)
) fee,
(
SELECT SUM(cz_times)
FROM #tmp t
WHERE t.c_code IN (SELECT c_code
FROM sys_ct
WHERE sys_ct.opt_id = com.opt_id)
) times,
com.opt_id
FROM sys_company com
WHERE c_companytype = 1
--清除临时表
DROP TABLE #tmp
看下是否能快一点?SELECT c_name ,
c_bank ,
c_account ,
c_tag ,
SUM(rr_fee) fee ,
SUM(cz_times) times ,
com.opt_id
FROM sys_company com
JOIN sys_ct ON sys_ct.opt_id = com.opt_id
JOIN Sys_ReplenishRecord ON Sys_ReplenishRecord.c_code = sys_ct.c_code
WHERE c_companytype = 1
AND rr_time >= '2017-7-1 00:00:00'
AND rr_time <= '2017-7-31 23:59:59'
AND rr_state <> '-2'
GROUP BY c_name ,
c_bank ,
c_account ,
c_tag ,
com.opt_id