Blog.XiaoMing.Xyz|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
UPDATE user_online_month_atu a
INNER JOIN (
SELECT
user_id,
sum(c.online_times) as online_times,
SUM(c.login_count) as login_count,
Sum(c.view_page_count) as view_page_count,
LEFT(c.log_date,length(c.log_date) – 2) as date
FROM
user_online_time_atu c
GROUP BY
c.user_id ,date
) b ON a.user_id = b.user_id AND a.`month`=b.date
SET a.online_time = b.online_times ,a.login_count=b.login_count,a.view_page_count=b.view_page_count
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
UPDATE user_online_month a
INNER JOIN (
SELECT
user_id,
sum(c.online_times) as online_times,
SUM(c.login_count) as login_count,
Sum(c.view_page_count) as view_page_count,
LEFT(c.log_date,length(c.log_date) – 2) as date
FROM
user_online_time c
GROUP BY
c.user_id ,date
) b ON a.user_id = b.user_id AND a.`month`=b.date
SET a.online_time = b.online_times ,a.login_count=b.login_count,a.view_page_count=b.view_page_count;
|
以上代码来自网友mysql，实测mssql有所不同,采用以下方法实现select结果更新: (待更多测试…)
INSERT INTO #demo (custid,custname)
SELECT DISTINCT KH01FNUMBER,KH02FNAME FROM #test ORDER BY KH01FNUMBER --把select结果插入到demo表中
UPDATE t1 SET t1.jgshu=t2.sumchuhetui1,t1.jgjin=t2.sumchuhetuimoney1
FROM #demo t1 JOIN (
SELECT KH01FNUMBER,
SUM(chuhetui) AS sumchuhetui1,
SUM(chuhetuimoney) AS sumchuhetuimoney1
FROM #test WHERE xsleixin LIKE '%测试%'
GROUP BY KH01FNUMBER) t2 ON t2.KH01FNUMBER=t1.custid --把select结果更新到demo表中
