如何获得最大列使用SQL?
问题描述:
我有一张桌子,年份,月份,日期如下。如何获得最大列使用SQL?
Year Month Day
2015 10 28
2015 10 29
2015 10 29
2015 10 30
2015 10 30
2015 10 30
2015 11 4
2015 11 5
2015 11 9
2015 11 19
2016 1 31
2016 4 1
我想获得最高的一年,在这一年,那年的一天,相应的最高的月份,月柱
输出:
2016 4 1
如果表没有按'不包含 2016 1 31 2016 4 1
行的输出将是 2015 11 19
个
答
DECLARE @temp TABLE
(
Y SMALLINT,
M TINYINT,
D TINYINT,
PRIMARY KEY CLUSTERED (Y DESC, M DESC, D DESC) WITH(IGNORE_DUP_KEY=ON)
)
INSERT INTO @temp (Y, M, D)
VALUES
(2015, 10, 28), (2015, 10, 29),
(2015, 10, 29), (2015, 10, 30),
(2015, 10, 30), (2015, 10, 30),
(2015, 11, 4), (2015, 11, 5),
(2015, 11, 9), (2015, 11, 19),
(2016, 1 , 31), (2016, 4 , 1)
SELECT TOP(1) *
FROM @temp
ORDER BY Y DESC, M DESC, D DESC
结果 -
Y M D
------ ---- ----
2016 4 1
答
可以在SQL中使用最多的功能。
SELECT max(Year) as Year, Month, Day FROM table_name.
希望得到这个帮助!
答
选中此示例。
DECLARE @temp TABLE
(
Y SMALLINT,
M TINYINT,
D TINYINT,
PRIMARY KEY CLUSTERED (Y DESC, M DESC, D DESC) WITH(IGNORE_DUP_KEY=ON)
)
INSERT INTO @temp (Y, M, D)
VALUES
(2015, 10, 28), (2015, 10, 29),
(2015, 10, 29), (2015, 10, 30),
(2015, 10, 30), (2015, 10, 30),
(2015, 11, 4), (2015, 11, 5),
(2015, 11, 9), (2015, 11, 19),
(2016, 1 , 31), (2016, 4 , 1)
; with cte as
(
select cast (cast (y as varchar(15)) +'-' + cast (m as varchar(15)) + '-' + cast (m as varchar(15)) as date) dt , y , m, d
from @temp
)
select top 1 y,m,d from cte order by dt desc
delete @temp where y = '2016'
; with cte as
(
select cast (cast (y as varchar(15)) +'-' + cast (m as varchar(15)) + '-' + cast (m as varchar(15)) as date) dt , y , m, d
from @temp
)
select top 1 y,m,d from cte order by dt desc
答
如果你一定值代表有效日期,您可以将它们转换为日期,并获得最大的是:
SELECT DATEPART(year, MAX(CAST(Y as varchar(4)) + RIGHT('0' + CAST(M AS varchar(2)), 2) + RIGHT('0' + CAST(D AS varchar(2)), 2))),
DATEPART(month, MAX(CAST(Y as varchar(4)) + RIGHT('0' + CAST(M AS varchar(2)), 2) + RIGHT('0' + CAST(D AS varchar(2)), 2))),
DATEPART(dd, MAX(CAST(Y as varchar(4)) + RIGHT('0' + CAST(M AS varchar(2)), 2) + RIGHT('0' + CAST(D AS varchar(2)), 2)))
FROM @temp
答
下面是该查询(更换你的表名)
SELECT TOP 1 YEAR, MONTH, DAY
FROM YOUR_TABLE
ORDER BY YEAR DESC, MONTH DESC, DAY DESC
你的意思是你想每年的最新日期? –
为什么不以desc命令? –