SQL server查询最大或最小数据对应的其他列数据

SQL server 查询 最大数据----对应的----数据时间

SQL server 查询 最小数据----对应的----数据时间

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