急求多表运算后返回结果的SQL语句
表AccountInfo 跟 表MasterInfo,求按分类汇总之后进行多项式运算，
表AccountInfo:
ID 余额(唯一的，不会重复）
01 20
02 30
03 40
04 20
05 60
表MasterInfo:
ID 盈利
01 10
02 30
01 20
03 10
02 40
查询返回得结果（按总盈利率排序）:
ID 总盈利率(百分比）
02 70/（30-70）=-175%
01 30/(20-30)=-300%
。。。。。。
总盈利率的运算为：ID的总盈利之和/（余额-ID的总盈利之和）
余额项是唯一的，不需要汇总。盈利项需要汇总。
是sql2000数据库，求SQL语句的写法。
我这边是php远程调用mssql数据库按要求返回结果显示的，不需要改动跟保存数据库，查询单表可以正常返回显示，多表运算查询就不行，请帮忙写下SQL查询语句
[最优解释]
----------------------------
-- 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 VARCHAR(10), 盈利 INT )
-- INSERT INTO masterinfo
-- SELECT '01', 10
-- UNION ALL
-- SELECT '02', 30
-- UNION ALL
-- SELECT '01', 20
-- UNION ALL
-- SELECT '03', 10
-- UNION ALL
-- SELECT '02', 40
SELECT a.id,CONVERT(VARCHAR(10),CONVERT(INT,CONVERT(DECIMAL(9),盈利)/(CONVERT(DECIMAL(9),余额)-CONVERT(DECIMAL(9),盈利))*100))+'%'
FROM
(SELECT id,余额 FROM accountinfo)a INNER JOIN (
SELECT id,SUM(盈利)盈利 FROM masterinfo GROUP BY id)b ON a.id=b.id
ORDER BY CONVERT(DECIMAL(9,2),盈利)/(CONVERT(DECIMAL(9,2),余额)-CONVERT(DECIMAL(9,2),盈利))*100
/*
id
---------- -----------
01 -300%
02 -175%
03 33%
(3 行受影响)
*/
[其他解释]
--测试数据
create table #Accountinfo(id varchar(20),余额 int)
insert into #Accountinfo
select '01',20
union all
select '02',30
union all
select '03',40
union all
select '04',20
union all
select '05',60
create table #Masterinfo
(id varchar(20),盈利 int)
insert into #Masterinfo
select '01',10
union all
select '02',30
union all
select '01',20
union all
select '03',10
union all
select '02',40
---查询 修改下
select ID,cast(SUM(盈利)*1.0/((select 余额 from #AccountInfo where ID=a.ID)-SUM(盈利))*100 as decimal(10,0)) as 总盈利率
from #MasterInfo as a
group by ID
order by SUM(盈利)*1.0/((select 余额 from #AccountInfo where ID=a.ID)-SUM(盈利))*100 desc
[其他解释]
select ID,SUM(盈利)*1.0/((select 余额 from AccountInfo where ID=a.ID)-SUM(盈利))*100
from MasterInfo as a
group by ID
order by SUM(盈利)*1.0/((select 余额 from AccountInfo where ID=a.ID)-SUM(盈利))*100
[其他解释]
[quote=引用:]
SQL code
---查询 修改下
quote]
雪狼用了你的查询语句，返回不了结果哦
[其他解释]
----------------------------
-- 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
with t
as(
select
[ID],
SUM([盈利]) as [盈利]
from
[MasterInfo]
group by
[ID]
)
select
a.ID,
left(ltrim(t.盈利*100.0/(a.余额-t.盈利)),5)+'%' as 总盈利率
from
[AccountInfo] a
inner join
t
on
a.ID=t.ID
/*
ID 总盈利率
---- -----------
01 -300.%
02 -175.%
03 33.33%
(3 行受影响)
*/
[其他解释]
他的语句没啥问题啊。是不是你php绑定数据的时候有问题？
[其他解释]
大家的都对，是那字段总是返回不了值，换了旁边的字段就可以返回，然后再替换回来，就可以了。汗，谢谢大家的回复。