时间戳

首页 > 解决方案 > 更新 SQL Server 中 GROUP BY sum() 函数的查询问题

sql - 更新 SQL Server 中 GROUP BY sum() 函数的查询问题

问题描述

SELECT p.userId, u.username, SUM(p.prices)Price 
FROM tblPrices p, tblUsers u 
WHERE u.userId = p.userId
GROUP BY p.userId, u.username

我想更新Price结果

标签： sqlsql-serversql-server-2014

解决方案

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=cb7049e35720e92e5bee4a678b5283d3

在 SQL Server 中，您有两种创建表的途径。显式CREATE TABLE语句，您在其中显式定义表结构。另一个是通过 SELECT 语句的可选INTO子句管理的。SQL 引擎将识别结果集的形状，然后创建一个与您的结果完全匹配的表。这也将立即将查询的任何结果加载到表中，但这只能工作一次。如果您需要向表中添加更多数据，则必须将查询重写为传统INSERT语句，并且可能UPDATE取决于源数据的可变性。另一种方法是删除表并每次重新创建它。正确的选择取决于业务需求和数据适用性。

模拟您的环境

CREATE TABLE dbo.tblPrices
(
userId int NOT NULL
, prices decimal(18,2) NOT NULL
);

create table dbo.tblUsers
(
userId int NOT NULL
,username varchar(50) nOT NULL
);

insert into
dbo.tblUsers 
VALUES (1,'mo')
,(2, 'bill')
,(3, 'no sales');

insert into dbo.tblPrices
VALUES
(1, 13)
,(1, 7)
, (2, 17);

查询数据

-- create the table if it doesn't exist
-- blows up otherwise
SELECT
    P.userId
,   U.username
,   SUM(P.prices) AS Prices
INTO
    dbo.RESULTS
FROM
    dbo.tblPrices AS P
    INNER JOIN
        dbo.tblUsers AS U
        ON U.userId = P.userId
GROUP BY
    P.userId
,   U.username

;

-- What if we want to see all users, whether they had sales or not
-- Notice the change to a left join as well as reordering of the
-- tables and the use of the users userId as the prices might not exist
SELECT
    U.userId
,   U.username
,   SUM(P.prices) AS Prices
INTO
    dbo.RESULTS_ALL_USERS
FROM
    dbo.tblUsers AS U
    LEFT OUTER JOIN
        dbo.tblPrices aS P
        ON U.userId = P.userId
GROUP BY
    U.userId
,   U.username
;

检查结果

SELECT * FROM dbo.RESULTS AS R ORDER BY userID;
SELECT * FROM dbo.RESULTS_ALL_USERS AS R ORDER BY userID;

推荐阅读