查询帮助,谢谢! [英] Query Help, thanks!
问题描述
问候!
请参阅下面的数据,对于每个帐户,我需要最新的
balance_date和相应的余额。任何人都可以帮我查询
吗?非常感谢!
创建表格
(帐户int
,balance_date datetime
,平衡金钱)
插入值(101,''1/31/2005'',5000)
插入值(101,' '2005年2月28日'',6000)
插入值(102,''5/31/2005'',15000)
插入值(102,''6/30/2005'',10000)
理想输出
101''2/28/2005''6000
102''6/30/2005''10000
这是一种方式
选择a.account,a.balance_date,a.balance
来自
(选择账户,max(balance_date)为balanceate
来自一个
组的帐户)aTemp加入一个a.account = aTemp.account
和a.balance_date = aTemp.balancedate
http://sqlservercode.blogspot.com
>创建表(帐户int
,balance_date datetime
,余额钱)
为什么所有列都可以为空?这张桌子的关键是什么?
你是否知道,当你将乘以或除以
时,MONEY会给你不精确的结果?
尝试:
SELECT帐户,balance_date,余额
来自AS T
WHERE balance_date =
(SELECT MAX(balance_date)
来自
WHERE account = T.account);
-
David Portas
SQL Server MVP
-
>
感谢您的回复!
由于我只是在创建表格(我通常使用的表格已经是可用的b / b),我不确定如何使用NULL设置列
选项和键。设置MONEY以避免计算问题的最佳方法是什么?我要感谢任何建议,谢谢!
Greetings!
Please see my data below, for each account, I would need the lastest
balance_date with the corresponding balance. Can anyone help me with
the query? Thanks a lot!
create table a
(account int
,balance_date datetime
,balance money)
insert into a values(101,''1/31/2005'', 5000)
insert into a values(101,''2/28/2005'', 6000)
insert into a values(102,''5/31/2005'', 15000)
insert into a values(102,''6/30/2005'', 10000)
Ideal Output
101 ''2/28/2005'' 6000
102 ''6/30/2005'' 10000
Here is one way
select a.account,a.balance_date,a.balance
from
(select account,max(balance_date) as balancedate
from a
group by account)aTemp join a on a.account =aTemp.account
and a.balance_date =aTemp.balancedate
http://sqlservercode.blogspot.com
> create table a(account int
,balance_date datetime
,balance money)
Why are all your columns nullable? What is/are the key/s of this table?
Are you aware that MONEY will give you imprecisely rounded results when
you multiply or divide?
Try:
SELECT account, balance_date, balance
FROM a AS T
WHERE balance_date =
(SELECT MAX(balance_date)
FROM a
WHERE account = T.account) ;
--
David Portas
SQL Server MVP
--
Thanks for the reply!
As I only occationally create tables (I normally use tables that are
already available), I am not sure how to set up columns with NULL
options and key. What is the best way to set up MONEY to avoid
calculation problems? I would appreciate any suggestions, thanks!
这篇关于查询帮助,谢谢!的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!