----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-09-22 15:30:33
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([Column1] varchar(1),[Column2] int,[Column3] varchar(1),[Column4] int,[Column5] int)
insert [huang]
select 'A',500,'E',50,null union all
select 'A',500,'F',100,null union all
select 'A',500,'G',30,null union all
select 'B',700,'E',20,null union all
select 'B',700,'F',60,null union all
select 'B',700,'G',40,null union all
select 'B',700,'H',70,null union all
select 'C',400,'F',80,null union all
select 'C',400,'G',20,null union all
select 'D',600,'F',10,null union all
select 'D',600,'G',20,null union all
select 'D',600,'H',30,null
--------------开始查询--------------------------select column1,Column2,Column3,Column4,(SELECT SUM(column4) FROM huang b WHERE a.column1=b.column1)column5
from [huang] a
ORDER BY column1,Column5
----------------结果----------------------------
/*
column1 Column2 Column3 Column4 column5
------- ----------- ------- ----------- -----------
A 500 E 50 180
A 500 F 100 180
A 500 G 30 180
B 700 E 20 190
B 700 F 60 190
B 700 G 40 190
B 700 H 70 190
C 400 F 80 100
C 400 G 20 100
D 600 F 10 60
D 600 G 20 60
D 600 H 30 60
*/
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-09-22 15:30:33
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([Column1] varchar(1),[Column2] int,[Column3] varchar(1),[Column4] int,[Column5] int)
insert [huang]
select 'A',500,'E',50,null union all
select 'A',500,'F',100,null union all
select 'A',500,'G',30,null union all
select 'B',700,'E',20,null union all
select 'B',700,'F',60,null union all
select 'B',700,'G',40,null union all
select 'B',700,'H',70,null union all
select 'C',400,'F',80,null union all
select 'C',400,'G',20,null union all
select 'D',600,'F',10,null union all
select 'D',600,'G',20,null union all
select 'D',600,'H',30,null
--------------开始查询--------------------------select column1,Column2,Column3,Column4,(SELECT SUM(column4) FROM huang b WHERE a.column1=b.column1)column5
from [huang] a
ORDER BY column1,Column5
----------------结果----------------------------
/*
column1 Column2 Column3 Column4 column5
------- ----------- ------- ----------- -----------
A 500 E 50 180
A 500 F 100 180
A 500 G 30 180
B 700 E 20 190
B 700 F 60 190
B 700 G 40 190
B 700 H 70 190
C 400 F 80 100
C 400 G 20 100
D 600 F 10 60
D 600 G 20 60
D 600 H 30 60
*/
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货