求一sql,有点难

[mbm]

两个表关联查询求总和的问题
表 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语句如何写查询效率最高？

[mbm]

严重感谢lsxaa，结帖

[wisgtalt]

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用户拥有的所有票的金额了.
因为要统计所有,所以只按用户分组合计就行.

[pbsql]

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

[pbsql]

呵呵，楼主都严重同意了，没测试么

[fxcl2006]

少了个y？
:P

[mbm]

pbsql,谢谢提醒

[lsxaa]

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

[mbm]

pbsql,这样写会不会有执行效率问题？

[lsxaa]

或者这样 楼主要测试一下啊 要不然错了怎么办??? ^_^
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

[pbsql]

楼上的思路有问题：假如今天没有交易呢？所以用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

[mbm]

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: 返回的结果不正确

[lsxaa]

是这样 getdate() 是用来用来计算当前的股票价格的

和交易没有关系

pbsql(风云) 你再看看是这样吧

[lsxaa]

datediff(day,b.datetime,getdate())=0


这里是b.datetime 是第二个表中的

[lsxaa]

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 行）

[lsxaa]

楼主给的和他的要求不对 52*28.3433+91*48.3433

应该是 52*28.3433+8*48.3433

就是我那个结果

[mbm]

引用：
pbsql(风云) (HoldNum字段记录用户此次交易后拥有的此股票的数量，所以应该取每个股票的最后一笔才对

to lsxaa(小李铅笔刀) :你统计的是所有的记录，只统计最后1笔的HoldNum

[lsxaa]

总价格，就是去掉那个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

[lsxaa]

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

[mbm]

ok,完全正确了.
lsxaa:太谢谢你了，同时也谢谢 pbsql(风云) 的帮助

结贴，另一贴已结，再次感谢

[lsxaa]

楼主你自己测一下