SQL server 查询 最大数据----对应的----数据时间
SQL server 查询 最小数据----对应的----数据时间
先看SQL执行完成的效果图
1. 1.建表
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[testA]') AND type IN ('U'))
DROP TABLE [dbo].[testA]
GO
CREATE TABLE [dbo].[testA] (
[id] int NOT NULL,
[code] varchar(255) COLLATE Chinese_PRC_CI_AS NULL,
[name] varchar(255) COLLATE Chinese_PRC_CI_AS NULL,
[tm] datetime NULL,
[valueN] int NULL
)
GO
ALTER TABLE [dbo].[testA] SET (LOCK_ESCALATION = TABLE)
GO
ALTER TABLE [dbo].[testA] ADD CONSTRAINT [PK__testA__3213E83FAB649435] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO
1. 2.添加数据
INSERT INTO [testA]([id], [code], [name], [tm], [valueN]) VALUES (1, '001', '张三', '2021-03-10 16:18:33.000', 10);
INSERT INTO [testA]([id], [code], [name], [tm], [valueN]) VALUES (2, '001', '张三', '2021-03-09 16:18:33.000', 9);
INSERT INTO [testA]([id], [code], [name], [tm], [valueN]) VALUES (3, '001', '张三', '2021-03-08 16:18:33.000', 8);
INSERT INTO [testA]([id], [code], [name], [tm], [valueN]) VALUES (4, '001', '张三', '2021-03-07 16:18:33.000', 7);
INSERT INTO [testA]([id], [code], [name], [tm], [valueN]) VALUES (5, '001', '张三', '2021-03-06 16:18:33.000', 6);
INSERT INTO [testA]([id], [code], [name], [tm], [valueN]) VALUES (6, '001', '张三', '2021-03-05 16:18:33.000', 5);
INSERT INTO [testA]([id], [code], [name], [tm], [valueN]) VALUES (7, '001', '张三', '2021-03-04 16:18:33.000', 4);
INSERT INTO [testA]([id], [code], [name], [tm], [valueN]) VALUES (8, '001', '张三', '2021-03-03 16:18:33.000', 3);
INSERT INTO [testA]([id], [code], [name], [tm], [valueN]) VALUES (9, '001', '张三', '2021-03-02 16:18:33.000', 2);
INSERT INTO [testA]([id], [code], [name], [tm], [valueN]) VALUES (10, '001', '张三', '2021-03-01 16:18:33.000', 7);
INSERT INTO [testA]([id], [code], [name], [tm], [valueN]) VALUES (11, '001', '张三', '2021-03-01 08:00:00.000', 0);
INSERT INTO [testA]([id], [code], [name], [tm], [valueN]) VALUES (12, '002', '李四', '2021-03-10 08:00:33.000', 9);
INSERT INTO [testA]([id], [code], [name], [tm], [valueN]) VALUES (13, '002', '李四', '2021-03-09 08:00:33.000', 99);
INSERT INTO [testA]([id], [code], [name], [tm], [valueN]) VALUES (14, '002', '李四', '2021-03-08 08:00:33.000', 88);
INSERT INTO [testA]([id], [code], [name], [tm], [valueN]) VALUES (15, '002', '李四', '2021-03-05 08:00:33.000', 9);
INSERT INTO [testA]([id], [code], [name], [tm], [valueN]) VALUES (16, '002', '李四', '2021-03-01 08:18:33.000', 9);
INSERT INTO [testA]([id], [code], [name], [tm], [valueN]) VALUES (17, '003', '王五', '2021-03-10 16:18:33.000', 100);
INSERT INTO [testA]([id], [code], [name], [tm], [valueN]) VALUES (18, '003', '王五', '2021-03-05 16:18:33.000', 50);
INSERT INTO [testA]([id], [code], [name], [tm], [valueN]) VALUES (19, '003', '王五', '2021-03-01 16:18:33.000', 60);
INSERT INTO [testA]([id], [code], [name], [tm], [valueN]) VALUES (20, '003', '王五', '2021-03-01 08:00:00.000', 10);
SELECT * FROM testA
1. 3.测试SQL是否正确
SELECT
t1.code,
t1.name,
MAX(t1.valueN) maxN,
(SELECT top 1 tm FROM testA WHERE valueN=MAX(t1.valueN)and tm>='2021-03-01 08:00' and tm<='2021-03-10 23:00') as maxNTm,
MIN(t1.valueN) minN,
(SELECT top 1 tm FROM testA WHERE valueN=MIN(t1.valueN)and tm>='2021-03-01 08:00' and tm<='2021-03-10 23:00') as minNTm
FROM testA t1
WHERE t1.tm>='2021-03-01 08:00' and t1.tm<='2021-03-10 23:00'
GROUP BY t1.code,t1.name