使用SELECT比较两个表的最新结果

Question

我需要从一个表中比较最新的输入值，最新限制输入了另一个表。然后，我想返回一个响应，该响应表明限制是否已超过。我对SQL有些新，但这是我为获得每个表中最新值所做的：

SELECT  
(SELECT a.Current_Speed FROM (SELECT TOP 1 Current_Speed, date_entered FROM [Measured] ORDER BY date_entered DESC) AS a) AS Value1,
(SELECT b.Speed_Limit FROM (SELECT TOP 1 Speed_Limit, date_entered FROM [Limits] ORDER BY date_entered DESC) as b) AS Limit1

就这样做，这有效，尽管我认为必须有一种比使用五个选择更容易的方法。它返回名为value1和title1的列中的正确值。然后，我尝试将案例语句添加到结尾，如下：

SELECT  
(SELECT a.Current_Speed FROM (SELECT TOP 1 Current_Speed, date_entered FROM [dbo].[Measured] ORDER BY date_entered DESC) AS a) AS Value1,
(SELECT b.Speed_Limit FROM (SELECT TOP 1 Speed_Limit, date_entered FROM [dbo].[Limits] ORDER BY date_entered DESC) as b) AS Limit1,
CASE WHEN Value1 > Limit1 THEN 'Over'
     WHEN Limit1 > Value1 THEN 'Under'
     ELSE 'Perfect!' END AS Result

但是，这是不起作用的，因为执行查询时，我会获得value1和limit1的"无效列"响应。

我敢肯定，必须有一种方法来实现我想要的东西，但我似乎找不到它。

任何帮助都非常感谢。

Answer 1

将subquies放在 FROM子句中：

SELECT (CASE WHEN m.Current_Speed > l.Speed_Limit THEN 'Over'
             WHEN l.Speed_Limit > m.Current_Speed THEN 'Under'
             ELSE 'Perfect!'
        END) AS Result
FROM (SELECT TOP 1 m.* FROM [dbo].[Measured] m ORDER BY date_entered DESC
     ) m CROSS JOIN
     (SELECT TOP 1 l.* FROM [dbo].[Limits] l ORDER BY date_entered DESC
     ) l;

请注意逻辑的简化：

• 每个值只需要一个级别。
• 子征服可以利用select *的便利性来获取所有列。SQL Server足够聪明，只能使用所需的列。
• 表别名与定义子征的表有关。这使得遵循查询逻辑变得容易得多。

Answer 2

查看这是否适合您想要的东西。

Select CASE WHEN Value1 > Limit1 THEN 'Over'
     WHEN Limit1 > Value1 THEN 'Under'
     ELSE 'Perfect!' END AS Result
From (
SELECT  
(SELECT a.Current_Speed FROM (SELECT TOP 1 Current_Speed, date_entered FROM [dbo].[Measured] ORDER BY date_entered DESC) AS a) AS Value1,
(SELECT b.Speed_Limit FROM (SELECT TOP 1 Speed_Limit, date_entered FROM [dbo].[Limits] ORDER BY date_entered DESC) as b) AS Limit1
) A

Answer 3

;WITH x AS
(
    SELECT (SELECT TOP 1 Current_Speed 
            FROM [Measured] 
            ORDER BY date_entered DESC) AS Value1,
           (SELECT TOP 1 Speed_Limit 
            FROM [Limits] 
            ORDER BY date_entered DESC) AS Limit1
)
SELECT Value1, 
       Limit1,
       CASE WHEN Value1 > Limit1 THEN 'Over'
            WHEN Limit1 > Value1 THEN 'Under'
            ELSE 'Perfect!' END AS Result
FROM x;

或

SELECT Value1, 
       Limit1,
       CASE WHEN Value1 > Limit1 THEN 'Over'
            WHEN Limit1 > Value1 THEN 'Under'
            ELSE 'Perfect!' END AS Result
FROM (SELECT (SELECT TOP 1 Current_Speed 
              FROM [Measured] 
              ORDER BY date_entered DESC) AS Value1,
             (SELECT TOP 1 Speed_Limit 
              FROM [Limits] 
              ORDER BY date_entered DESC) AS Limit1) x