SELECT c.card_no, v.service_id,
SUM(case datediff(dd,c.pay_date,d.log_date)/30 when 0 then IsNull(d.expend_points, 0) end) * 0.05 * 0.2
+count(distinct u.user_account) AS amount,
SUM(case datediff(dd,c.pay_date,d.log_date)/30 when 0 then IsNull(d.expend_points, 0) end) AS points,
SUM(case datediff(dd,c.pay_date,d.log_date)/30 when 1 then IsNull(d.expend_points, 0) end) * 0.05 * 0.16 AS amount1,
SUM(case datediff(dd,c.pay_date,d.log_date)/30 when 1 then IsNull(d.expend_points, 0) end) AS points1,
SUM(case datediff(dd,c.pay_date,d.log_date)/30 when 2 then IsNull(d.expend_points, 0) end) * 0.05 * 0.08 AS amount2,
SUM(case datediff(dd,c.pay_date,d.log_date)/30 when 2 then IsNull(d.expend_points, 0) end) AS points2
FROM card_expend c, BILLDB.TWBillingRep.dbo.expenddetail d,
(select b.* from BILLDB.TWBilling.dbo.user_pay b ,(select min(pay_date) pay_date,user_account
from BILLDB.TWBilling.dbo.user_pay where points>=200 and pay_date > '2006.09.01 00:00:00' group by user_account) a
where b.user_account=a.user_account and b.pay_date>a.pay_date and b.pay_date > '2006.09.01 00:00:00'
AND LEFT(b.pay_flag, 1) = '1' ) u, v_connect_server v,
card_agent_detail a,agent_card_type t
WHERE c.user_account = d.user_account AND d.pay_id = u.pay_id AND d.connect_id = v.connect_id
AND DateAdd(mm, 3, c.pay_date) >= getdate()
AND u.pay_date <= DateAdd(mm,3 , c.pay_date)
AND c.card_no = a.card_no AND a.type_no = t.type_no AND t.flag = 'C' AND c.service_id = 'XMX' AND a.status ='E'
GROUP BY c.card_no, v.service_id
------------
上面是我写的SQL语句,由于BILLDB.TWBillingRep.dbo.expenddetail 这个表的资料有上千万笔资料,
BILLDB.TWBilling.dbo.user_pay 这个表有300多万笔资料,所以我的这个SQL语句执行的效率太低了,求各位帮帮忙,帮我优化一下,小第感激不尽.这样吧,我先解析这句话的意思
select b.* from BILLDB.TWBilling.dbo.user_pay b ,(select min(pay_date) pay_date,user_account
from BILLDB.TWBilling.dbo.user_pay where points>=200 and pay_date > '2006.09.01 00:00:00' group by user_account
假如表BILLDB.TWBilling.dbo.user_pay 的数据如下
user_account points pay_date
001 15 2006-9-1
001 200 2006-9-2
001 1 2006-9-2
002 300 2006-9-1
003 5 2006-9-5
003 200 2006-9-7
......
那么我要取的数据就是
user_account points pay_date
001 200 2006-9-2
001 1 2006-9-2
002 300 2006-9-1
003 200 2006-9-7
有什么好办法把这个SQL语句优化呢??
SUM(case datediff(dd,c.pay_date,d.log_date)/30 when 0 then IsNull(d.expend_points, 0) end) * 0.05 * 0.2
+count(distinct u.user_account) AS amount,
SUM(case datediff(dd,c.pay_date,d.log_date)/30 when 0 then IsNull(d.expend_points, 0) end) AS points,
SUM(case datediff(dd,c.pay_date,d.log_date)/30 when 1 then IsNull(d.expend_points, 0) end) * 0.05 * 0.16 AS amount1,
SUM(case datediff(dd,c.pay_date,d.log_date)/30 when 1 then IsNull(d.expend_points, 0) end) AS points1,
SUM(case datediff(dd,c.pay_date,d.log_date)/30 when 2 then IsNull(d.expend_points, 0) end) * 0.05 * 0.08 AS amount2,
SUM(case datediff(dd,c.pay_date,d.log_date)/30 when 2 then IsNull(d.expend_points, 0) end) AS points2
FROM card_expend c, BILLDB.TWBillingRep.dbo.expenddetail d,
(select b.* from BILLDB.TWBilling.dbo.user_pay b ,(select min(pay_date) pay_date,user_account
from BILLDB.TWBilling.dbo.user_pay where points>=200 and pay_date > '2006.09.01 00:00:00' group by user_account) a
where b.user_account=a.user_account and b.pay_date>a.pay_date and b.pay_date > '2006.09.01 00:00:00'
AND LEFT(b.pay_flag, 1) = '1' ) u, v_connect_server v,
card_agent_detail a,agent_card_type t
WHERE c.user_account = d.user_account AND d.pay_id = u.pay_id AND d.connect_id = v.connect_id
AND DateAdd(mm, 3, c.pay_date) >= getdate()
AND u.pay_date <= DateAdd(mm,3 , c.pay_date)
AND c.card_no = a.card_no AND a.type_no = t.type_no AND t.flag = 'C' AND c.service_id = 'XMX' AND a.status ='E'
GROUP BY c.card_no, v.service_id
------------
上面是我写的SQL语句,由于BILLDB.TWBillingRep.dbo.expenddetail 这个表的资料有上千万笔资料,
BILLDB.TWBilling.dbo.user_pay 这个表有300多万笔资料,所以我的这个SQL语句执行的效率太低了,求各位帮帮忙,帮我优化一下,小第感激不尽.这样吧,我先解析这句话的意思
select b.* from BILLDB.TWBilling.dbo.user_pay b ,(select min(pay_date) pay_date,user_account
from BILLDB.TWBilling.dbo.user_pay where points>=200 and pay_date > '2006.09.01 00:00:00' group by user_account
假如表BILLDB.TWBilling.dbo.user_pay 的数据如下
user_account points pay_date
001 15 2006-9-1
001 200 2006-9-2
001 1 2006-9-2
002 300 2006-9-1
003 5 2006-9-5
003 200 2006-9-7
......
那么我要取的数据就是
user_account points pay_date
001 200 2006-9-2
001 1 2006-9-2
002 300 2006-9-1
003 200 2006-9-7
有什么好办法把这个SQL语句优化呢??
解决方案 »
- 请问DB格式的文件如何打开,需要装什么软件吗?
- 联合主键 外键 如何得到如下结果
- where cid=100 and name like '%abc%' 与 where name like '%abc%' and cid=100,效率有区别吗?
- 用SQL语句查询出二维表
- 问个简单问题!关于取系统时间。
- 急!在虚拟服务器上如何卸载SQL-SERVER2000企业版,添加删除程序里面没有这一项?
- 如何查询用户表的数量
- ****SQL数据库同步复制过程中的复杂问题???
- 分组统计和明细统计
- SQL查询 Group by 如何加上另一列的字段(不是分组的字段,也不是聚集函数)
- 用存储过程建完 全文索引 后的一些参数怎么设置??
- 如何接触SQL Server2005 sa用户的登陆锁定
t.*
from
BILLDB.TWBilling.dbo.user_pay t
where
not exists(select 1 from BILLDB.TWBilling.dbo.user_pay where user_account=t.user_account and pay_date>t.pay_date)
FROM BILLDB.TWBilling.dbo.user_pay b
WHERE b.pay_date > '2006.09.01 00:00:00'
AND LEFT(b.pay_flag, 1) = '1'
AND EXISTS (
SELECT 1
FROM BILLDB.TWBilling.dbo.user_pay a
WHERE b.user_account = a.user_account
AND a.pay_date < b.pay_date
)
from BILLDB.TWBilling.dbo.user_pay where points>=200 and pay_date > '2006.09.01 00:00:00' group by user_account) a
where b.user_account=a.user_account and b.pay_date>a.pay_date and b.pay_date > '2006.09.01 00:00:00'
AND LEFT(b.pay_flag, 1) = '1' 对不起,刚刚这个SQL语句没给完全,子陌大哥你MSN多少啊,我的业务逻辑一下子说不清楚,MSN上聊好么