有一个表,有两列,第一列表示日期(Day),第二列表示数据(Data)
现要求仅使用SQL语句输出每年的最大值和最小值
谢谢!
------解决方案--------------------------------------------------------
每年的什么最大值?数据吗?
select year(day),min(data),max(data) from tb group by year(day)
------解决方案--------------------------------------------------------
;WITH T([Day],[Data]) AS(
SELECT '2012-1-15',15 UNION ALL
SELECT '2009-3-15',14 UNION ALL
SELECT '2010-6-15',20 UNION ALL
SELECT '2011-1-15',30 UNION ALL
SELECT '2010-7-25',11 UNION ALL
SELECT '2011-5-17',20 UNION ALL
SELECT '2013-6-14',57 UNION ALL
SELECT '2012-4-15',19
)
SELECT YEAR([DAY]) AS [Day],MAX([Data]) AS [Data]
FROM T
GROUP BY YEAR([Day])
------解决方案--------------------------------------------------------
select [day],[data] from tbl a where not exists(select 1 from tbl b where year(a.[day])=year(b.[day]) and a.[day]<b.[day]) or not exists(select 1 from tbl b where year(a.[day])=year(b.[day]) and a.[day]>b.[day]) order by 1 asc