我有一个A表 里面有DataId 和 StartTime 和 EndTime 三个字段 它里面有100条数据 我需要写一个sql语句 查出DataId为1的
StartTime 等于2021-04-01 这天的数据 只要查出一条就可以了
我指定DataId 和 StartTime和EndTime 例StartTime 指定为2021-04-01 EndTime 为2021-04-30 他就开始从StartTime开始查 一直查到EndTime 截至 每次只要有一条数据就可以 输出总共多少天有数据 @int a =0 1天有一条数据就+1 然后从第二天开始查
如下：
select DataId,StartTime from A
where StartTime>='2021-04-01' and EndTime<='2021-04-30'
group by DataId,StartTime;
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
id INT,
ActiveTotal DECIMAL(15,4),
UpdateTime DATETIME
)
INSERT INTO t
SELECT 6,0.8400,'2018-10-10 09:33:007'
UNION ALL SELECT 9,0.8400,'2018-10-10 09:33:44.333'
UNION ALL SELECT 12,0.8200,'2018-10-10 09:34:02.487'
UNION ALL SELECT 15,0.8300,'2018-10-10 09:34:18.620'
UNION ALL SELECT 17,0.8200,'2018-10-10 09:34:43.100'
UNION ALL SELECT 19,0.8200,'2018-10-10 09:34:55.153'
UNION ALL SELECT 21,0.8200,'2018-10-10 09:35:04.777'
UNION ALL SELECT 1, 0.8100,'2018-10-10 18:02:27.447'
UNION ALL SELECT 32,0.8300,'2018-10-11 23:02:21.827'
UNION ALL SELECT 35,0.8200,'2018-10-12 23:02:19.320'
UNION ALL SELECT 39,0.8200,'2018-10-13 23:02:17.407'
UNION ALL SELECT 43,0.8100,'2018-10-14 23:02:15.887'
UNION ALL SELECT 47,0.8100,'2018-10-15 23:02:23.850'
UNION ALL SELECT 51,0.8300,'2018-10-16 23:02:22.097'
UNION ALL SELECT 55,0.8100,'2018-10-17 11:02:21.077'
UNION ALL SELECT 59,0.8100,'2018-10-17 11:02:21.077'
UNION ALL SELECT 60,0.8100,'2018-10-17 11:02:21.077'
UNION ALL SELECT 61,0.8100,'2018-10-17 11:02:21.077'
UNION ALL SELECT 62,0.8100,'2018-10-17 11:02:21.077'
UNION ALL SELECT 63,0.8100,'2018-10-17 11:02:21.077'
UNION ALL SELECT 64,0.8100,'2018-10-17 11:02:21.077'
UNION ALL SELECT 65,0.8100,'2018-10-17 11:02:21.077'
GO
----
SELECT t.id,t.ActiveTotal,t.UpdateTime
FROM (
SELECT ROW_NUMBER() OVER(
PARTITION BY CONVERT(CHAR(10), UpdateTime, 120) ORDER BY UpdateTime DESC
) AS rid,
*
FROM t
) AS t
WHERE rid = 1
ORDER BY t.UpdateTime
/*
id ActiveTotal UpdateTime
----------- --------------------------------------- -----------------------
1 0.8100 2018-10-10 18:02:27.447
32 0.8300 2018-10-11 23:02:21.827
35 0.8200 2018-10-12 23:02:19.320
39 0.8200 2018-10-13 23:02:17.407
43 0.8100 2018-10-14 23:02:15.887
47 0.8100 2018-10-15 23:02:23.850
51 0.8300 2018-10-16 23:02:22.097
55 0.8100 2018-10-17 11:02:21.077
*/
原文来自于：https://blog.csdn.net/yenange
试试这个，先查出满足条件的数据，再根据天数分组，每天取一条数据。
SELECT * FROM (
SELECT *,ROW_NUMBER() OVER(PARTITION BY YEAR(StartTime),MONTH(StartTime),DAY(StartTime) ORDER BY StartTime) num FROM a WHERE DataId=1 AND StartTime>='2021-04-01' AND EndTime<='2021-04-30'
) b WHERE num=1