欢迎光临
个人知识库,ERP、IT知识分享和应用

sql update select 从查询结果中更新数据 (mysql / mssql)

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表中

 

 

未经允许不得转载:Blog.XiaoMing.Xyz » sql update select 从查询结果中更新数据 (mysql / mssql)
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址