我必须从服务器检索和排序数据。首先,这个查询太长了,我认为它可以更短,但我不知道该怎么做。其次,我为每种传感器类型获得单独的表。但是最好只有一个表,其中每个传感器类型有一个列。难点在于选择每个传感器的不同的前4个,并根据正确的DateTime
对它们进行排序。
SELECT TOP(2)
[clRegId] AS Id, [clRegDateTimestamp] AS DateTime,
([clRegSysNbr] + 1) AS CelNr,
((([clRegValue] - 4860.00)/20.00) - 30.00) AS Value,
CASE
WHEN clRegId = 0 THEN ''airTemp''
END AS Sensor
FROM
[Database].[dbo].[registrations]
WHERE
clRegSysNbr = 10
AND clRegId = 0
AND clRegMain = 3
AND clRegConfigId = 3
ORDER BY
DateTimestamp DESC
SELECT TOP(2)
[clRegId] AS Id, [clRegDateTimestamp] AS DateTime,
([clRegSysNbr] + 1) AS CelNr,
([clRegValue] / 40.0) AS Value,
CASE
WHEN clRegId = 1 THEN ''product cold''
END AS Sensor
FROM
[Database].[dbo].[registrations]
WHERE
clRegSysNbr = 10
AND clRegId = 1
AND clRegMain = 3
AND clRegConfigId = 3
ORDER BY
DateTimestamp DESC
这段代码返回如下单独的表作为结果:
来自空气传感器的第一个表:
<表类>Id DateTime CelNr 价值 传感器 tbody><<tr>0 7/11/2022 13:20:00 11 0, 6道明> 0 7/11/2022 13:15:00 11 0, 5道明> 表类>
- 这个查询绝不是'复杂'或'太长',很多报告最终连接多个表并有10-20列。
- 你的数据是在同一个表中,所以,而不是做两个单独的语句,你可以连接表本身,以获得结果在一行,像多个左连接在同一个表
那么,你的查询看起来像:
SELECT TOP(2)
[air.clRegDateTimestamp] AS DateTime,
([air.clRegSysNbr] + 1) AS CelNr,
((([air.clRegValue] - 4860.00)/20.00) - 30.00) AS Air,
([cold.clRegValue] / 40.0) AS Cold
FROM
[Database].[dbo].[registrations] air
LEFT JOIN (
SELECT
[clRegDateTimestamp] AS DateTime,
([clRegSysNbr] + 1) AS CelNr,
([clRegValue] / 40.0) AS Value
FROM [Database].[dbo].[registrations]
WHERE
clRegSysNbr = 10
AND clRegId = 1 -- cold
AND clRegMain = 3
AND clRegConfigId = 3) cold ON cold.DateTime = air.DateTime
WHERE
clRegSysNbr = 10
AND clRegId = 0 -- air
AND clRegMain = 3
AND clRegConfigId = 3
ORDER BY
DateTimestamp DESC