34,588
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
id INT IDENTITY(1,1) PRIMARY KEY,
d DATETIME NOT NULL,
[type] INT,
flag VARCHAR(10)
)
GO
SET NOCOUNT ON
INSERT INTO t (d,[type],flag) VALUES (GETDATE(),1,'')
INSERT INTO t (d,[type],flag) VALUES (GETDATE(),2,'')
INSERT INTO t (d,[type],flag) VALUES (DATEADD(minute,1,GETDATE()-1),1,'1,+1')
INSERT INTO t (d,[type],flag) VALUES (DATEADD(minute,2,GETDATE()-1),2,'2,+2')
INSERT INTO t (d,[type],flag) VALUES (DATEADD(minute,3,GETDATE()-1),1,'1,+3')
INSERT INTO t (d,[type],flag) VALUES (DATEADD(minute,4,GETDATE()-1),2,'2,+4')
GO
--增加索引,便于效率提高
CREATE INDEX ix_t_d ON t(d);
GO
--今天数据
SELECT * FROM t WHERE d>CONVERT(CHAR(10),GETDATE(),120)
UNION ALL
--以前的离今天最近的数据,每种取一条
SELECT id,d,[type],t.flag FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY [type] ORDER BY d desc) AS rid,*
FROM t WHERE d<CONVERT(CHAR(10),GETDATE(),120)
) AS t
WHERE t.rid=1
/*
id d type flag
----------- ----------------------- ----------- ----------
1 2018-09-05 17:36:04.720 1
2 2018-09-05 17:36:04.720 2
5 2018-09-04 17:39:04.720 1 1,+3
6 2018-09-04 17:40:04.720 2 2,+4
*/
SELECT * FROM 表 WHERE DATEDIFF(DAY,GETDATE(),时间字段)=0
UNION
SELECT TOP 1 * FROM 表 WHERE DATEDIFF(DAY,GETDATE(),时间字段)<>0 ORDER BY 时间字段 DESC