根据日期对数据库进行排序。 [英] to sort database according to date.

问题描述

您好，我正在开发一个项目，我必须生成一个应用程序的最新状态报告，该报告根据日期保存在数据库中作为AppNo。



给我的提示是，我应该首先根据特定AppNo的日期对数据库进行排序，当AppNo更改数据时，只是上面的数据表示该应用程序的最新状态。

但是我不知道如何排序......所以请帮助我...



谢谢..

Hello,I am working on a project where i have to generate a latest status report of an application which is saved in database as AppNo according to the date.

The hint given to me is that i should first sort my database according to date for a particular AppNo and when the AppNo changes the data just data just above that denotes the latest status of that Application.
But I donot know how to sort it...so kindly help me...

Thank you..

推荐答案

通常，要对您需要的数据进行排序 ORDER BY [ ^ ]条款。



看看例子：

Generally, to sort data you need to ues ORDER BY[^] clause.

Have a look at example:

--define temporary table
DECLARE @apps TABLE(AppNo INT, AppName VARCHAR(30), ModDate DATETIME)

--insert data
INSERT INTO @apps (AppNo, AppName, ModDate)
SELECT 1 AS AppNo, 'ApplicationNo1' AS ApName, '2010-05-11' AS ModDate
UNION ALL SELECT 2, 'ApplicationNo2', '2010-06-11'
UNION ALL SELECT 1, 'ApplicationNo1', '2010-09-01'
UNION ALL SELECT 3, 'ApplicationNo3', '2010-09-21'
UNION ALL SELECT 1, 'ApplicationNo1', '2011-03-15'
UNION ALL SELECT 2, 'ApplicationNo2', '2011-04-23'
UNION ALL SELECT 3, 'ApplicationNo3', '2012-06-13'
UNION ALL SELECT 2, 'ApplicationNo2', '2012-07-01'
UNION ALL SELECT 1, 'ApplicationNo1', '2012-10-18'
UNION ALL SELECT 1, 'ApplicationNo1', '2013-01-01'
UNION ALL SELECT 2, 'ApplicationNo2', '2013-03-01'

--get data sorted by AppNo and last ModDate
SELECT *
FROM @apps
ORDER BY AppNo, ModDate DESC

--get data sorted by AppNo and ModDate; the highest ModNo is for last ModDate
SELECT *, ROW_NUMBER() OVER(PARTITION BY AppNo ORDER BY ModDate DESC) AS ModNo 
FROM @apps

结果：

1。查询：

Results:
1. query:

AppNo AppName       ModDate                    ModNo
1     ApplicationNo1  2013-01-01 00:00:00.000
1     ApplicationNo1  2012-10-18 00:00:00.000
1     ApplicationNo1  2011-03-15 00:00:00.000
1     ApplicationNo1  2010-09-01 00:00:00.000
1     ApplicationNo1  2010-05-11 00:00:00.000
2     ApplicationNo2  2013-03-01 00:00:00.000
2     ApplicationNo2  2012-07-01 00:00:00.000
2     ApplicationNo2  2011-04-23 00:00:00.000
2     ApplicationNo2  2010-06-11 00:00:00.000
3     ApplicationNo3  2012-06-13 00:00:00.000
3     ApplicationNo3  2010-09-21 00:00:00.000

2。查询：

2. query:

AppNo   AppName         ModDate                    ModNo
1	ApplicationNo1	2013-01-01 00:00:00.000	   1
1	ApplicationNo1	2012-10-18 00:00:00.000	   2
1	ApplicationNo1	2011-03-15 00:00:00.000	   3
1	ApplicationNo1	2010-09-01 00:00:00.000	   4
1	ApplicationNo1	2010-05-11 00:00:00.000	   5
2	ApplicationNo2	2013-03-01 00:00:00.000	   1
2	ApplicationNo2	2012-07-01 00:00:00.000	   2
2	ApplicationNo2	2011-04-23 00:00:00.000	   3
2	ApplicationNo2	2010-06-11 00:00:00.000	   4
3	ApplicationNo3	2012-06-13 00:00:00.000	   1
3	ApplicationNo3	2010-09-21 00:00:00.000	   2

这篇关于根据日期对数据库进行排序。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！