update warehouse.dbo.everyday_server set leijiamount=(select sum(c.order_amount) as amount from wanhui2.dbo.info_recharge c,warehouse.dbo.everyday_type d
where c.order_status=1 and c.order_admin_user=0 and (c.order_type=0 or c.order_type=2)
and d.plat='wanhui2' and c.user_channel=d.user_channel and convert(varchar(10),dateadd(s,c.user_add_date+28800,'1970-01-01'),120)>=a.kaifuriqi
and c.game_id=a.game_id and c.server_id=a.server_id and d.zongleixing=a.zongleixing
and datediff(dd,dateadd(s,c.order_submit_time+28800,'1970-01-01'),getdate())>0
group by c.game_id,c.server_id,d.zongleixing
),leijirenshu=(select count(distinct c.user_id) as usercount from wanhui2.dbo.info_recharge c,warehouse.dbo.everyday_type d
where c.order_status=1 and c.order_admin_user=0 and (c.order_type=0 or c.order_type=2)
and d.plat='wanhui2' and c.user_channel=d.user_channel and convert(varchar(10),dateadd(s,c.user_add_date+28800,'1970-01-01'),120)>=a.kaifuriqi
and c.game_id=a.game_id and c.server_id=a.server_id and d.zongleixing=a.zongleixing
and datediff(dd,dateadd(s,c.order_submit_time+28800,'1970-01-01'),getdate())>0
group by c.game_id,c.server_id,d.zongleixing)
from warehouse.dbo.everyday_server a where a.plat='wanhui2' and a.user_channel='推广' and a.user_type='新用户'
------解决方案--------------------
写这么乱
两个字查询貌似可以合并
------解决方案--------------------
- SQL code
UPDATE warehouse.dbo.everyday_serverSET leijiamount = T.amount, leijirenshu = T.usercount FROM warehouse.dbo.everyday_server aINNER JOIN ( SELECT c.game_id , c.server_id , d.zongleixing, COUNT(DISTINCT c.user_id) AS usercount,SUM(c.order_amount) AS amount FROM wanhui2.dbo.info_recharge c , warehouse.dbo.everyday_type d WHERE c.order_status = 1 AND c.order_admin_user = 0 AND ( c.order_type = 0 OR c.order_type = 2 ) AND d.plat = 'wanhui2' AND c.user_channel = d.user_channel AND CONVERT(VARCHAR(10), DATEADD(s, c.user_add_date + 28800, '1970-01-01'), 120) >= a.kaifuriqi AND DATEDIFF(dd, DATEADD(s, c.order_submit_time + 28800, '1970-01-01'), GETDATE()) > 0 GROUP BY c.game_id , c.server_id , d.zongleixing ) T ON a.game_id = T.game_id AND T.server_id = a.server_id AND T.zongleixing = a.zongleixingWHERE a.plat = 'wanhui2' AND a.user_channel = '推广' AND a.user_type = '新用户'
------解决方案--------------------
上面代码是一个示意，可以把对应子查询精简。
具体调试，LZ得在环境中自己处理一下。
下列代码，参考，2005版本以上适用。
- SQL code
WITH TTAS(SELECT c.game_id , c.server_id , d.zongleixing, COUNT(DISTINCT c.user_id) AS usercount,SUM(c.order_amount) AS amount FROM wanhui2.dbo.info_recharge c , warehouse.dbo.everyday_type d, warehouse.dbo.everyday_server a WHERE c.order_status = 1 AND c.order_admin_user = 0 AND ( c.order_type = 0 OR c.order_type = 2 ) AND d.plat = 'wanhui2' AND c.user_channel = d.user_channel AND CONVERT(VARCHAR(10), DATEADD(s, c.user_add_date + 28800, '1970-01-01'), 120) >= a.kaifuriqi AND DATEDIFF(dd, DATEADD(s, c.order_submit_time + 28800, '1970-01-01'), GETDATE()) > 0 and a.game_id = c.game_id AND c.server_id = a.server_id AND d.zongleixing = a.zongleixing GROUP BY c.game_id , c.server_id , d.zongleixing)UPDATE warehouse.dbo.everyday_serverSET leijiamount = TT.amount, leijirenshu = TT.usercount FROM warehouse.dbo.everyday_server aINNER JOIN TT ON a.game_id = TT.game_id AND TT.server_id = a.server_id AND TT.zongleixing = a.zongleixing WHERE a.plat = 'wanhui2' AND a.user_channel = '推广' AND a.user_type = '新用户'