1.4w+
社区成员
25.3w+
社区内容
- 主页
- 基础类
- 应用实例
- 新技术前沿
求一sql,有点难
两个表关联查询求总和的问题
表 BuyRecord，这个表描述用户股票购买记录，按时间排序(每当有用户交易时插入一条记录),HoldNum字段记录用户此次交易后拥有的此股票的数量。
例如：userid=10001的用户截至到2004-11-4 11:35:11拥有股票代码是Z001的股票数目是52，拥有股票代码是Z002的股票数目是8，因为这个用户没有买过Z003，所以没有关于Z03的记录
用户代码 股票代码 交易时间 拥有数量
UserID StockCode DateTime HoldNum
10001 Z001 2004-11-4 10:26:16 3
10001 Z002 2004-11-4 10:28:19 2
10001 Z001 2004-11-4 11:29:10 52
10002 Z002 2004-11-4 11:30:11 91
10001 Z002 2004-11-4 11:32:12 8
10002 Z003 2004-11-4 12:33:15 9
10003 Z002 2004-11-3 12:35:12
表StockCurrentValue，这个表记录股票每天价格的当前值,(11-3号的CurrentValue已经作废但表里要保留这条记录)
例如今天是4号，则目前Z001股票的当前价格是28.3433,Z002是48.3433,Z003是32.2346
股票代码 日期 当前价格
StockCode DateTime CurrentValue
Z001 2004-11-3 25.3456
Z001 2004-11-4 28.3433
Z002 2004-11-3 38.2343
Z002 2004-11-4 48.3433
Z003 2004-11-3 23.3433
Z003 2004-11-4 32.2346
查询目的：求UserID='10001'的用户当前拥有的所有股票的总价格。
也就是 52*28.3433+91*48.3433
这个sql语句如何写查询效率最高？
表 BuyRecord，这个表描述用户股票购买记录，按时间排序(每当有用户交易时插入一条记录),HoldNum字段记录用户此次交易后拥有的此股票的数量。
例如：userid=10001的用户截至到2004-11-4 11:35:11拥有股票代码是Z001的股票数目是52，拥有股票代码是Z002的股票数目是8，因为这个用户没有买过Z003，所以没有关于Z03的记录
用户代码 股票代码 交易时间 拥有数量
UserID StockCode DateTime HoldNum
10001 Z001 2004-11-4 10:26:16 3
10001 Z002 2004-11-4 10:28:19 2
10001 Z001 2004-11-4 11:29:10 52
10002 Z002 2004-11-4 11:30:11 91
10001 Z002 2004-11-4 11:32:12 8
10002 Z003 2004-11-4 12:33:15 9
10003 Z002 2004-11-3 12:35:12
表StockCurrentValue，这个表记录股票每天价格的当前值,(11-3号的CurrentValue已经作废但表里要保留这条记录)
例如今天是4号，则目前Z001股票的当前价格是28.3433,Z002是48.3433,Z003是32.2346
股票代码 日期 当前价格
StockCode DateTime CurrentValue
Z001 2004-11-3 25.3456
Z001 2004-11-4 28.3433
Z002 2004-11-3 38.2343
Z002 2004-11-4 48.3433
Z003 2004-11-3 23.3433
Z003 2004-11-4 32.2346
查询目的：求UserID='10001'的用户当前拥有的所有股票的总价格。
也就是 52*28.3433+91*48.3433
这个sql语句如何写查询效率最高？
...全文
lsxaa 2004年11月04日
楼主你自己测一下
mbm 2004年11月04日
ok,完全正确了.
lsxaa:太谢谢你了，同时也谢谢 pbsql(风云) 的帮助
结贴，另一贴已结，再次感谢
lsxaa:太谢谢你了，同时也谢谢 pbsql(风云) 的帮助
结贴，另一贴已结，再次感谢
lsxaa 2004年11月04日
to lsxaa(小李铅笔刀) :你统计的是所有的记录，只统计最后1笔的HoldNum
不是所有的最后这里有限制了...... 你用我最后写的这个语句
select a.userid,sum(a.HoldNum*b.CurrentValue) as TotalValue
from BuyRecord a inner join StockCurrentValue b
on a.StockCode=b.StockCode and datediff(day,b.datetime,getdate())=0
where a.UserID='10001'
and a.DateTime=(select top 1 DateTime from BuyRecord
where StockCode=a.StockCode and userid='10001'
order by datetime desc) --此处限制最后一条记录
group by a.userid
lsxaa 2004年11月04日
总价格，就是去掉那个a.StockCode
select a.userid,sum(a.HoldNum*b.CurrentValue) as TotalValue
from BuyRecord a inner join StockCurrentValue b
on a.StockCode=b.StockCode and datediff(day,b.datetime,getdate())=0
where a.UserID='10001'
and a.DateTime=(select top 1 DateTime from BuyRecord
where StockCode=a.StockCode and userid='10001'
order by datetime desc)
group by a.userid
select a.userid,sum(a.HoldNum*b.CurrentValue) as TotalValue
from BuyRecord a inner join StockCurrentValue b
on a.StockCode=b.StockCode and datediff(day,b.datetime,getdate())=0
where a.UserID='10001'
and a.DateTime=(select top 1 DateTime from BuyRecord
where StockCode=a.StockCode and userid='10001'
order by datetime desc)
group by a.userid
mbm 2004年11月04日
引用：
pbsql(风云) (HoldNum字段记录用户此次交易后拥有的此股票的数量，所以应该取每个股票的最后一笔才对
to lsxaa(小李铅笔刀) :你统计的是所有的记录，只统计最后1笔的HoldNum
pbsql(风云) (HoldNum字段记录用户此次交易后拥有的此股票的数量，所以应该取每个股票的最后一笔才对
to lsxaa(小李铅笔刀) :你统计的是所有的记录，只统计最后1笔的HoldNum
lsxaa 2004年11月04日
楼主给的和他的要求不对 52*28.3433+91*48.3433
应该是 52*28.3433+8*48.3433
就是我那个结果
应该是 52*28.3433+8*48.3433
就是我那个结果
lsxaa 2004年11月04日
create table BuyRecord(UserID varchar(5),StockCode varchar(4),[DateTime] datetime,HoldNum int)
insert into BuyRecord
select '10001','Z001','2004-11-4 10:26:16',3 union all
select '10001','Z002','2004-11-4 10:28:19',2 union all
select '10001','Z001','2004-11-4 11:29:10',52 union all
select '10002','Z002','2004-11-4 11:30:11',91 union all
select '10001','Z002','2004-11-4 11:32:12',8 union all
select '10002','Z003','2004-11-4 12:33:15',9 union all
select '10003','Z002','2004-11-4 12:35:12',6
create table StockCurrentValue(StockCode varchar(4),[DateTime] datetime,CurrentValue float)
insert into StockCurrentValue
select 'Z001','2004-11-3',25.3456 union all
select 'Z001','2004-11-4',28.3433 union all
select 'Z002','2004-11-3',38.2343 union all
select 'Z002','2004-11-4',48.3433 union all
select 'Z003','2004-11-3',23.3433 union all
select 'Z003','2004-11-4',32.2346
select a.userid,a.StockCode,sum(a.HoldNum*b.CurrentValue) as TotalValue
from BuyRecord a inner join StockCurrentValue b
on a.StockCode=b.StockCode and datediff(day,b.datetime,getdate())=0
where a.UserID='10001'
and a.DateTime=(select top 1 DateTime from BuyRecord
where StockCode=a.StockCode and userid='10001'
order by datetime desc)
group by a.userid,a.StockCode
--结果
userid StockCode TotalValue
------ --------- -----------------------------------------------------
10001 Z001 1473.8516
10001 Z002 386.74639999999999
（所影响的行数为 2 行）
insert into BuyRecord
select '10001','Z001','2004-11-4 10:26:16',3 union all
select '10001','Z002','2004-11-4 10:28:19',2 union all
select '10001','Z001','2004-11-4 11:29:10',52 union all
select '10002','Z002','2004-11-4 11:30:11',91 union all
select '10001','Z002','2004-11-4 11:32:12',8 union all
select '10002','Z003','2004-11-4 12:33:15',9 union all
select '10003','Z002','2004-11-4 12:35:12',6
create table StockCurrentValue(StockCode varchar(4),[DateTime] datetime,CurrentValue float)
insert into StockCurrentValue
select 'Z001','2004-11-3',25.3456 union all
select 'Z001','2004-11-4',28.3433 union all
select 'Z002','2004-11-3',38.2343 union all
select 'Z002','2004-11-4',48.3433 union all
select 'Z003','2004-11-3',23.3433 union all
select 'Z003','2004-11-4',32.2346
select a.userid,a.StockCode,sum(a.HoldNum*b.CurrentValue) as TotalValue
from BuyRecord a inner join StockCurrentValue b
on a.StockCode=b.StockCode and datediff(day,b.datetime,getdate())=0
where a.UserID='10001'
and a.DateTime=(select top 1 DateTime from BuyRecord
where StockCode=a.StockCode and userid='10001'
order by datetime desc)
group by a.userid,a.StockCode
--结果
userid StockCode TotalValue
------ --------- -----------------------------------------------------
10001 Z001 1473.8516
10001 Z002 386.74639999999999
（所影响的行数为 2 行）
lsxaa 2004年11月04日
datediff(day,b.datetime,getdate())=0
这里是b.datetime 是第二个表中的
这里是b.datetime 是第二个表中的
lsxaa 2004年11月04日
是这样 getdate() 是用来用来计算当前的股票价格的
和交易没有关系
pbsql(风云) 你再看看是这样吧
和交易没有关系
pbsql(风云) 你再看看是这样吧
mbm 2004年11月04日
10001 1001 .0000
10001 1002 .0000
10001 1003 .0000
10001 1004 .0000
10001 1005 .0000
10001 1006 .0000
10001 1007 .0000
10001 1008 .0000
to Lsxaa: 返回的结果不正确
10001 1002 .0000
10001 1003 .0000
10001 1004 .0000
10001 1005 .0000
10001 1006 .0000
10001 1007 .0000
10001 1008 .0000
to Lsxaa: 返回的结果不正确
pbsql 2004年11月04日
楼上的思路有问题：假如今天没有交易呢？所以用getdate()肯定不对的
我的要改一下：
select a.UserID,所有股票的总价格=sum(a.HoldNum*b.CurrentValue) from
(select t.UserID,t.StockCode,t.HoldNum from BuyRecord t,
(select UserID,StockCode,[DateTime]=max([DateTime])
from BuyRecord where t.UserID='100001' group by UserID,StockCode) a
where t.UserID=a.UserID and t.StockCode=a.StockCode
and t.[DateTime]=a.[DateTime]) a,--股票数
(select t.StockCode,t.CurrentValue from StockCurrentValue t,
(select StockCode,[DateTime]=max([DateTime])
from StockCurrentValue group by StockCode) a
where t.StockCode=a.StockCode and t.[DateTime]=a.[DateTime]) b--股票价格
where a.StockCode=b.StockCode
group by a.UserID
我的要改一下：
select a.UserID,所有股票的总价格=sum(a.HoldNum*b.CurrentValue) from
(select t.UserID,t.StockCode,t.HoldNum from BuyRecord t,
(select UserID,StockCode,[DateTime]=max([DateTime])
from BuyRecord where t.UserID='100001' group by UserID,StockCode) a
where t.UserID=a.UserID and t.StockCode=a.StockCode
and t.[DateTime]=a.[DateTime]) a,--股票数
(select t.StockCode,t.CurrentValue from StockCurrentValue t,
(select StockCode,[DateTime]=max([DateTime])
from StockCurrentValue group by StockCode) a
where t.StockCode=a.StockCode and t.[DateTime]=a.[DateTime]) b--股票价格
where a.StockCode=b.StockCode
group by a.UserID
lsxaa 2004年11月04日
或者这样 楼主要测试一下啊 要不然错了怎么办??? ^_^
select a.userid,a.StockCode,sum(a.HoldNum*b.CurrentValue) as TotalValue
from BuyRecord a inner join StockCurrentValue b
on a.StockCode=b.StockCode and datediff(day,b.datetime,getdate())=0
where a.UserID='10001'
and a.DateTime=(select top 1 DateTime from BuyRecord
where StockCode=a.StockCode and userid='10001'
order by datetime desc)
group by a.userid,a.StockCode
select a.userid,a.StockCode,sum(a.HoldNum*b.CurrentValue) as TotalValue
from BuyRecord a inner join StockCurrentValue b
on a.StockCode=b.StockCode and datediff(day,b.datetime,getdate())=0
where a.UserID='10001'
and a.DateTime=(select top 1 DateTime from BuyRecord
where StockCode=a.StockCode and userid='10001'
order by datetime desc)
group by a.userid,a.StockCode
mbm 2004年11月04日
pbsql,这样写会不会有执行效率问题？
lsxaa 2004年11月04日
pbsql(风云) 说的对 楼主等一下结贴
用这个试试
select a.userid,a.StockCode,sum(a.HoldNum*b.CurrentValue) as TotalValue
from BuyRecord a inner join StockCurrentValue b
on a.StockCode=b.StockCode and datediff(day,b.datetime,getdate())=0
and a.DateTime=(select top 1 DateTime from BuyRecord
where StockCode=a.StockCode and userid='10001'
order by datetime desc)
where a.UserID='10001'
group by a.userid,a.StockCode
用这个试试
select a.userid,a.StockCode,sum(a.HoldNum*b.CurrentValue) as TotalValue
from BuyRecord a inner join StockCurrentValue b
on a.StockCode=b.StockCode and datediff(day,b.datetime,getdate())=0
and a.DateTime=(select top 1 DateTime from BuyRecord
where StockCode=a.StockCode and userid='10001'
order by datetime desc)
where a.UserID='10001'
group by a.userid,a.StockCode
mbm 2004年11月04日
pbsql,谢谢提醒
fxcl2006 2004年11月04日
少了个y？
:P
:P
pbsql 2004年11月04日
呵呵，楼主都严重同意了，没测试么
pbsql 2004年11月04日
HoldNum字段记录用户此次交易后拥有的此股票的数量，所以应该取每个股票的最后一笔才对
select a.UserID,所有股票的总价格=sum(a.HoldNum*b.CurrentValue) from
(select t.UserID,t.StockCode,t.HoldNum from BuyRecord t,
(select UserID,StockCode,[DateTime]=max([DateTime])
from BuyRecord where t.UserID='100001' group by UserID,StockCode) a
where t.UserID=a.UserID and t.StockCode=a.StockCode
and t.[DateTime]=a.[DateTime]) a,--股票数
(select t.StockCode,t.CurrentValue from StockCurrentValue t,
(select StockCode,[DateTime]=max([DateTime])
from StockCurrentValue group by StockCode) a
where t.StockCode=a.StockCode and t.[DateTime]=a.[DateTime]) b--股票价格
group by a.UserID
select a.UserID,所有股票的总价格=sum(a.HoldNum*b.CurrentValue) from
(select t.UserID,t.StockCode,t.HoldNum from BuyRecord t,
(select UserID,StockCode,[DateTime]=max([DateTime])
from BuyRecord where t.UserID='100001' group by UserID,StockCode) a
where t.UserID=a.UserID and t.StockCode=a.StockCode
and t.[DateTime]=a.[DateTime]) a,--股票数
(select t.StockCode,t.CurrentValue from StockCurrentValue t,
(select StockCode,[DateTime]=max([DateTime])
from StockCurrentValue group by StockCode) a
where t.StockCode=a.StockCode and t.[DateTime]=a.[DateTime]) b--股票价格
group by a.UserID
wisgtalt 2004年11月04日
select sum(b.HoldNum * a.CurrentValue) from BuyRecord a, StockCurrentValue b where a.StockCode = b.StockCode and b.userid = '10001' group by b.userid
计算出来就是10001用户拥有的所有票的金额了.
因为要统计所有,所以只按用户分组合计就行.
mbm 2004年11月04日
严重感谢lsxaa，结帖