create table yh (用户编号 int,银行帐号 char (6),用户地址 char (2),划款金额 [decimal](12, 2), 银行标志 int)insert into yh select 1, '072339','A1', 10, 1 union select 2, '072339','B1', 5, 1 union select 3, '069508','C1', 5, 1 union select 4, '312505','D1', 10, 1 union select 5, '312505','E1', 10, 1---测试 select 用户地址,sum(划款金额) as 划款总金额 from yh a where a.用户编号 in (select top 1 用户编号 from yh where 银行帐号=a.银行帐号 ) group by 用户地址---结果 用户地址 划款总金额 ------- --------- A1 10.00 C1 5.00 D1 10.00
create table yh (用户编号 int,银行帐号 char (6),用户地址 char (2),划款金额 [decimal](12, 2), 银行标志 int)insert into yh select 1, '072339','A1', 10, 1 union select 2, '072339','B1', 5, 1 union select 3, '069508','C1', 5, 1 union select 4, '312505','D1', 10, 1 union select 5, '312505','E1', 10, 1 ---测试 select a.用户地址,b.划款金额 as 划款总金额 from yh a, (select 银行帐号,sum(划款金额) as 划款金额 from yh group by 银行帐号) b where a.银行帐号=b.银行帐号 and a.用户编号 in (select top 1 用户编号 from yh where 银行帐号=a.银行帐号 )order by a.用户地址---结果 用户地址 划款总金额 ------- --------- A1 15.00 C1 5.00 D1 20.00
大家不能猜
group by (银行帐号)
create table yh (用户编号 int,银行帐号 char (6),用户地址 char (2),划款金额 [decimal](12, 2), 银行标志 int)insert into yh
select 1, '072339','A1', 10, 1 union
select 2, '072339','B1', 5, 1 union
select 3, '069508','C1', 5, 1 union
select 4, '312505','D1', 10, 1 union
select 5, '312505','E1', 10, 1---测试
select 用户地址,sum(划款金额) as 划款总金额 from yh a where a.用户编号 in (select top 1 用户编号 from yh where 银行帐号=a.银行帐号
)
group by 用户地址---结果
用户地址 划款总金额
------- ---------
A1 10.00
C1 5.00
D1 10.00
select 1, '072339','A1', 10, 1 union
select 2, '072339','B1', 5, 1 union
select 3, '069508','C1', 5, 1 union
select 4, '312505','D1', 10, 1 union
select 5, '312505','E1', 10, 1 ---测试
select a.用户地址,b.划款金额 as 划款总金额 from yh a,
(select 银行帐号,sum(划款金额) as 划款金额 from yh group by 银行帐号) b where a.银行帐号=b.银行帐号 and a.用户编号
in (select top 1 用户编号 from yh where 银行帐号=a.银行帐号
)order by a.用户地址---结果
用户地址 划款总金额
------- ---------
A1 15.00
C1 5.00
D1 20.00