日期:2014-05-17 浏览次数:20514 次
----------------------------
-- Author :TravyLee(物是人非事事休,欲语泪先流!)
-- Date :2012-11-06 22:04:06
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 6.1 <X86> (Build 7600: )
--
----------------------------
--> 测试数据:[AccountInfo]
if object_id('[AccountInfo]') is not null
drop table [AccountInfo]
go
create table [AccountInfo](
[ID] varchar(2),
[余额] int
)
insert [AccountInfo]
select '01',20 union all
select '02',30 union all
select '03',40 union all
select '04',20 union all
select '05',60
--> 测试数据:[MasterInfo]
if object_id('[MasterInfo]') is not null
drop table [MasterInfo]
go
create table [MasterInfo](
[ID] varchar(2),
[盈利] int
)
insert [MasterInfo]
select '01',10 union all
select '02',30 union all
select '01',20 union all
select '03',10 union all
select '02',40
go
--SQL Server2000
select
a.ID,
left(ltrim(t.盈利*100.0/(a.余额-t.盈利)),5)+'%' as 总盈利率
from
[AccountInfo] a
inner join
(
select
[ID],
SUM([盈利]) as [盈利]
from
[MasterInfo]
group by
[ID]
)t
on
a.ID=t.ID
/*
ID 总盈利率
---- -----------
01 -300.%
02 -175.%
03 33.33%
(3 行受影响)
*/
--CREATE TABLE accountinfo(ID VARCHAR(10) , 余额 INT )
-- INSERT INTO accountinfo
-- SELECT '01' , 20
-- UNION ALL
-- SELECT '02', 30
-- UNION ALL
-- SELECT '03', 40
-- UNION ALL
-- SELECT '04', 20
-- CREATE TABLE masterinfo(ID&nb