22,210
社区成员
发帖
与我相关
我的任务
分享
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'buy')
BEGIN
DROP TABLE buy
END
GO
CREATE TABLE buy
(
buyid INT,
Clientid INT,
buy_cost INT
)
GO
INSERT INTO buy
SELECT 1, 1, 100 UNION
SELECT 2, 1, 50 UNION
SELECT 3, 2, 200
GO
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'ItemTable')
BEGIN
DROP TABLE ItemTable
END
GO
CREATE TABLE ItemTable
(
item_id INT,
buyid INT
)
GO
INSERT INTO ItemTable
SELECT 1, 1 UNION
SELECT 2, 1 UNION
SELECT 3, 1 UNION
SELECT 4, 2 UNION
SELECT 5, 3
GO
/*
Clientid totalcost
2 200
1 150
*/
SELECT Clientid,SUM(buy_cost) AS totalcost
FROM buy
GROUP BY Clientid
Clientid totalcost
1 150
2 200