表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查询语句
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查询语句
from MasterInfo as a
group by ID
order by SUM(盈利)*1.0/((select 余额 from AccountInfo where ID=a.ID)-SUM(盈利))*100
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
----------------------------------------------------------------
-- 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 行受影响)
*/
-- 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 行受影响)
*/
-- 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 行受影响)
*/