SQL Server中的SQL Select语句问题 [英] Sql select statement in sql server problem

问题描述

每个人都快乐，

我有一张桌子

列是
1.贷款类型
2.利息率
3.天数
4.EffectiveDate

这些是该表的行

Hai Every One,

I have a table like

Columns are
1.Loantype
2.Rate OF Interest
3.Days
4.EffectiveDate

These are the rows for that table

1   12  30  2011-11-24 00:00:00.000
1   14  60  2011-11-25 00:00:00.000
3   14  60  2011-10-11 00:00:00.000
2   12  30  2011-11-24 00:00:00.000
3   11  120 2011-11-25 00:00:00.000
4   15  60  2011-11-26 00:00:00.000
2   14  60  2011-11-25 00:00:00.000
4   11  30  2011-11-26 00:00:00.000
4   12  60  2011-11-27 00:00:00.000
3   12  120 2011-11-22 00:00:00.000
4   14  90  2011-11-30 00:00:00.000
4   16  120 2011-11-30 00:00:00.000
2   14  60  2011-11-25 00:00:00.000
1   10  30  2012-01-01 00:00:00.000
1   15  30  2012-01-03 00:00:00.000
3   16  30  2012-01-01 00:00:00.000
2   10  30  2012-01-01 00:00:00.000
2   15  60  2012-01-04 00:00:00.000

我想为每种贷款类型选择最新的利率
例如:
贷款类型1的30天有2个利率，我想最新的利率即2012年1月3日的10个利率
我的结果应该像这样

i want to select latest rate of interest for every loan type
ex:
for loan type 1 has 2 rate of interest for 30 days i want latest one i.e. 10 on 2012-01-03
My result should like this

1	15	30	2012-01-03 00:00:00.000
     1	14	60	2011-11-25 00:00:00.000
     2	14	60	2011-11-25 00:00:00.000
     2	14	60	2011-11-25 00:00:00.000
     3	16	30	2012-01-01 00:00:00.000
     3	14	60	2011-11-12 00:00:00.000

请帮助我为上述结果集编写选择状态

在此先感谢

Please help me write select state for above result set

Thanks In Advance

推荐答案

如何:

How about:

;with [cte] as 
(
select 
    Loantype, 
    Days,
    [Rate OF Interest], 
    rowNumber = row_number() over (partition by Loantype, Day order by EffectiveDate desc)
from [table]
)
select 
    Loantype,
    Days,
    [Rate OF Interest]
from [cte]
where rowNumber = 1

SELECT LAST(Rate_Of_Interest) FROM TABLE WHERE Loantype = 1.

什么将检索给定贷款类型的最新利率值.

但是，如果您想了解最新的知识，就如我想尝试解释的最后 EffectiveDate 的意思，您可以执行以下操作:

对于给定的贷款类型:

What will retrieve the lastest rate of interest value for a given loantype.

But if you want to know the lastest, in the meaning of the last EffectiveDate as I think you are trying to explain, you can simply do:

For a given loantype:

SELECT MAX(EffectiveDate)FROM TABLE WHERE Loantype = 1

对于每个人:

For every one:

SELECT * FROM TABLE WHERE EffectiveDate = (SELECT MAX(DATE) FROM TABLE)

您会将有效日期设置为列类型日期，以便可以分析字符串(显然是日期)

您还可以执行存储过程，以便检索当前日期(使用 getDate )，并可以使用 DATEDIFF 查找差异.

更多信息 http://www.sql-server-performance.com/2007/datetime-2008/ [此处]



让我知道这是否对您有帮助.

You will ned the effective date as column type date, so the string can be parsed (as date obviously)

You can also do a stored procedure so you can retrieve the present date (using getDate) and get you can find the difference using DATEDIFF.

More info http://www.sql-server-performance.com/2007/datetime-2008/[here]



Let me know if this helps you.

可能不是最好的，但超出了我的脑海

May not be the best, but off the top of my head

SELECT [columns] FROM [table]
WHERE Date = (SELECT MAX(date) FROM table)

这篇关于SQL Server中的SQL Select语句问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！