A表 有三列
pid name total
1 张三 1000
1 李四 2000
2 王五 2300
3 赵1 400
3 黄8 800
3 孙9 4000需要查询得到结果:
pid name total sum
1 张三 1000 3000
1 李四 2000 3000
2 王五 2300 2300
3 赵1 400 5200
3 黄8 800 5200
3 孙9 4000 5200根据pid分组求出total的和,然后在后面显示出来,请高手指导
pid name total
1 张三 1000
1 李四 2000
2 王五 2300
3 赵1 400
3 黄8 800
3 孙9 4000需要查询得到结果:
pid name total sum
1 张三 1000 3000
1 李四 2000 3000
2 王五 2300 2300
3 赵1 400 5200
3 黄8 800 5200
3 孙9 4000 5200根据pid分组求出total的和,然后在后面显示出来,请高手指导
AS (SELECT 1, '张三', 1000
UNION ALL
SELECT 1, '李四' ,2000
UNION ALL
SELECT 2, '王五' ,2300
UNION ALL
SELECT 3, '赵1' ,400
UNION ALL
SELECT 3, '黄8' ,800
UNION ALL
SELECT 3, '孙9' ,4000
)
SELECT *,b.total [sum]
FROM test a INNER JOIN (SELECT pid,SUM(total) total FROM test GROUP BY pid) b ON a.pid=b.pid
/*\
pid NAME total pid total sum
----------- ---- ----------- ----------- ----------- -----------
1 李四 2000 1 3000 3000
1 张三 1000 1 3000 3000
2 王五 2300 2 2300 2300
3 黄8 800 3 5200 5200
3 孙9 4000 3 5200 5200
3 赵1 400 3 5200 5200(6 行受影响)
*/
AS (SELECT 1, '张三', 1000
UNION ALL
SELECT 1, '李四' ,2000
UNION ALL
SELECT 2, '王五' ,2300
UNION ALL
SELECT 3, '赵1' ,400
UNION ALL
SELECT 3, '黄8' ,800
UNION ALL
SELECT 3, '孙9' ,4000
)
SELECT a.*,b.total [sum]
FROM test a INNER JOIN (SELECT pid,SUM(total) total FROM test GROUP BY pid) b ON a.pid=b.pid
/*
pid NAME total sum
----------- ---- ----------- -----------
1 李四 2000 3000
1 张三 1000 3000
2 王五 2300 2300
3 黄8 800 5200
3 孙9 4000 5200
3 赵1 400 5200(6 行受影响) */
insert tb select 1,N'张三',1000 UNION ALL
select 1,N'李四', 2000 UNION ALL
select 2,N'王五', 2300 UNION ALL
select 3,N'赵1', 400 UNION ALL
select 3,N'黄8', 800 UNION ALL
select 3,N'孙9', 4000SELECT A.*,B.SUMS FROM tb A JOIN (
SELECT PID,SUM(TOTAL)SUMS FROM tb
GROUP BY pid)B ON A.pid =B.pid
/*
pid name total SUMS
1 张三 1000 3000
1 李四 2000 3000
2 王五 2300 2300
3 赵1 400 5200
3 黄8 800 5200
3 孙9 4000 5200
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2012-10-10 15:47:41
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([pid] int,[name] varchar(4),[total] int)
insert [tb]
select 1,'张三',1000 union all
select 1,'李四',2000 union all
select 2,'王五',2300 union all
select 3,'赵1',400 union all
select 3,'黄8',800 union all
select 3,'孙9',4000
--------------开始查询--------------------------
select
* ,
(select SUM(total) from tb where pid=t.pid) as [sum]
from
[tb] t
----------------结果----------------------------
/* pid name total sum
----------- ---- ----------- -----------
1 张三 1000 3000
1 李四 2000 3000
2 王五 2300 2300
3 赵1 400 5200
3 黄8 800 5200
3 孙9 4000 5200(6 行受影响)
*/