表日期 货物
2010-01-01 大米
2010-01-01 花生
2010-01-02 面条
2010-01-02 白菜
2010-01-02 花生
2010-01-03 食用油
2010-01-03 大米
2010-01-03 花生
2010-01-03 面包
2010-01-04 大米
2010-01-04 豆腐
2010-01-04 面条
2010-01-05 大米
2010-01-06 花生
2010-01-07 盐
求:SQL语句查询最后四日(即上表中日期为2010-01-04,2010-01-05,2010-01-06,2010-01-07)的货物.并按日期排序.
2010-01-01 大米
2010-01-01 花生
2010-01-02 面条
2010-01-02 白菜
2010-01-02 花生
2010-01-03 食用油
2010-01-03 大米
2010-01-03 花生
2010-01-03 面包
2010-01-04 大米
2010-01-04 豆腐
2010-01-04 面条
2010-01-05 大米
2010-01-06 花生
2010-01-07 盐
求:SQL语句查询最后四日(即上表中日期为2010-01-04,2010-01-05,2010-01-06,2010-01-07)的货物.并按日期排序.
set @today='2010-01-07'select * from 表 where datediff(d,日期,@today)<=3 order b 日期
order by 日期
from (
select *,dense_rank() over (order by [日期] desc) as seq
from tb
) t
where seq<=4
order by [日期]
出错:
服务器: 消息 195,级别 15,状态 10,行 3
'dense_rank' 不是可以识别的 函数名。
会出错:
服务器: 消息 195,级别 15,状态 10,行 3
'dense_rank' 不是可以识别的 函数名。
select *
from tb
where [日期] in (select distinct top 4 [日期] from tb order by [日期] desc)
order by [日期]
日期 from table)a order by 日期 desc)
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([日期] [datetime],[货物] [nvarchar](10))
INSERT INTO [tb]
SELECT '2010-01-01','大米' UNION ALL
SELECT '2010-01-01','花生' UNION ALL
SELECT '2010-01-02','面条' UNION ALL
SELECT '2010-01-02','白菜' UNION ALL
SELECT '2010-01-02','花生' UNION ALL
SELECT '2010-01-03','食用油' UNION ALL
SELECT '2010-01-03','大米' UNION ALL
SELECT '2010-01-03','花生' UNION ALL
SELECT '2010-01-03','面包' UNION ALL
SELECT '2010-01-04','大米' UNION ALL
SELECT '2010-01-04','豆腐' UNION ALL
SELECT '2010-01-04','面条' UNION ALL
SELECT '2010-01-05','大米' UNION ALL
SELECT '2010-01-06','花生' UNION ALL
SELECT '2010-01-07','盐'
-->SQL查询如下:SELECT TOP 4 WITH TIES *
FROM [tb] t
ORDER BY 1 DESC
/*
日期 货物
----------------------- ----------
2010-01-07 00:00:00.000 盐
2010-01-06 00:00:00.000 花生
2010-01-05 00:00:00.000 大米
2010-01-04 00:00:00.000 大米
2010-01-04 00:00:00.000 豆腐
2010-01-04 00:00:00.000 面条(6 行受影响)
*/?
order by 日期 desc
select 日期, [货物]
from tb
where 日期 in (select distinct top 4 日期
from tb
order by 日期 desc)
order by 日期 desc
日期 货物
2010-01-07 盐
2010-01-06 花生
2010-01-05 大米
2010-01-04 大米
2010-01-04 豆腐
2010-01-04 面条
(1) I presume you want to pick the last date from your table records to be used as a reference for the last 4 days
(2) What happens if there are gap in the dates? For example, assume you only have records for Jan 1, Jan 10, Jan 11, Jan 20. Does last 4 day mean all records from Jan 17 to Jan 20, or do you really want all records from Jan 1 to Jan 20?