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

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