SELECT 账号 FROM 流水表 GROUP BY 账号 HAVING MAX(时间)<DATEADD(MONTH,-3,GETDATE()) SUM(金额)>=500000你都要统计了,只能是类似这样的查了,要提高效率,可以考虑分表 建索引
with a1 (date,account,amount) AS ( SELECT '2014/01/01','a',500000 UNION ALL SELECT '2014/01/01','b',400000 UNION ALL SELECT '2014/01/01','c',500000 UNION ALL SELECT '2014/12/31','c',100000 ) ,a2 AS ( SELECT account FROM a1 GROUP BY account HAVING SUM(amount)>=500000 ) SELECT * FROM a2 a WHERE NOT EXISTS(SELECT 1 FROM a1 WHERE account=a.account AND date>=DATEADD(month,-3,GETDATE()))
with a1 (date,account,amount) AS ( SELECT '2014/01/01','a',500000 UNION ALL SELECT '2014/01/01','b',400000 UNION ALL SELECT '2014/01/01','c',500000 UNION ALL SELECT '2014/12/31','c',100000 ) SELECT account FROM a1 a WHERE NOT EXISTS(SELECT 1 FROM a1 WHERE account=a.account AND date>=DATEADD(month,-3,GETDATE())) GROUP BY account HAVING SUM(amount)>=500000
不知道50的记录多不多,如果不多的话,有这个先决条件,速度会提高许多SELECT 账号 FROM 流水表 T1 WHERE 金额>=500000 AND NOT EXISTS(SELECT 1 FROM 流水表3 T2 WHERE T1.账号=T2.账号 AND T1.时间<T2.时间 AND T1.时间>=DATEADD(MONTH,-3,T2.时间))
with a1 (date,account,amount) AS ( SELECT '20140101','2010302020',10000.0 UNION ALL SELECT '20140102','2020202020',500000.0 UNION ALL SELECT '20140103','2222222222',3333.3 UNION ALL SELECT '20141231','3333333333',50000.0 ) SELECT a.* FROM a1 a OUTER APPLY ( SELECT TOP 1 date FROM a1 WHERE account=a.account AND date>a.date AND date<DATEADD(MONTH,3,a.date) ) b WHERE a.amount>=500000 AND b.date IS null
我觉得你可以试下先给流水表,建立 顺序为 账号 时间的覆盖索引然后把 满足50万条件的三十万条 按账号 聚合最大时间 存临时表里 SELECT 账号,MAX(时间)AS 时间 INTO #TEMP1 FROM 流水表 WHERE 金额>=500000 GROUP BY 账号然后根据这个#TEMP1,获取关联获取一下,这一步,可不能用标题函数去判断,这相当于你限死了SQLSERVER的优化 SELECT 账号 FROM #TEMP1 T1 WHERE 金额>=500000 AND NOT EXISTS(SELECT 1 FROM 流水表 T2 WHERE T1.账号=T2.账号 AND T1.时间<T2.时间 AND T1.时间>=DATEADD(MONTH,-3,T2.时间))
FROM 流水表
GROUP BY 账号
HAVING MAX(时间)<DATEADD(MONTH,-3,GETDATE())
SUM(金额)>=500000你都要统计了,只能是类似这样的查了,要提高效率,可以考虑分表 建索引
with a1 (date,account,amount) AS
(
SELECT '2014/01/01','a',500000 UNION ALL
SELECT '2014/01/01','b',400000 UNION ALL
SELECT '2014/01/01','c',500000 UNION ALL
SELECT '2014/12/31','c',100000
)
,a2 AS
(
SELECT account
FROM a1
GROUP BY account
HAVING SUM(amount)>=500000
)
SELECT *
FROM a2 a
WHERE NOT EXISTS(SELECT 1 FROM a1 WHERE account=a.account AND date>=DATEADD(month,-3,GETDATE()))
with a1 (date,account,amount) AS
(
SELECT '2014/01/01','a',500000 UNION ALL
SELECT '2014/01/01','b',400000 UNION ALL
SELECT '2014/01/01','c',500000 UNION ALL
SELECT '2014/12/31','c',100000
)
SELECT account
FROM a1 a
WHERE NOT EXISTS(SELECT 1 FROM a1 WHERE account=a.account AND date>=DATEADD(month,-3,GETDATE()))
GROUP BY account
HAVING SUM(amount)>=500000
FROM 流水表 T1
WHERE 金额>=500000
AND NOT EXISTS(SELECT 1 FROM 流水表3 T2 WHERE T1.账号=T2.账号 AND T1.时间<T2.时间 AND T1.时间>=DATEADD(MONTH,-3,T2.时间))
with a1 (date,account,amount) AS
(
SELECT '20140101','2010302020',10000.0 UNION ALL
SELECT '20140102','2020202020',500000.0 UNION ALL
SELECT '20140103','2222222222',3333.3 UNION ALL
SELECT '20141231','3333333333',50000.0
)
SELECT a.*
FROM a1 a
OUTER APPLY
(
SELECT TOP 1 date FROM a1 WHERE account=a.account AND date>a.date AND date<DATEADD(MONTH,3,a.date)
) b
WHERE a.amount>=500000 AND b.date IS null
SELECT 账号,MAX(时间)AS 时间 INTO #TEMP1 FROM 流水表
WHERE 金额>=500000
GROUP BY 账号然后根据这个#TEMP1,获取关联获取一下,这一步,可不能用标题函数去判断,这相当于你限死了SQLSERVER的优化
SELECT 账号
FROM #TEMP1 T1
WHERE 金额>=500000
AND NOT EXISTS(SELECT 1 FROM 流水表 T2 WHERE T1.账号=T2.账号 AND T1.时间<T2.时间 AND T1.时间>=DATEADD(MONTH,-3,T2.时间))