sql-server - View 中具有静态值的另一个表中的 SQL Server 列

Question

有没有办法让另一个表中的列的值在 View 中始终相同>示例:

SELECT *, 
   (SELECT value FROM tblStudentPrefixes WHERE PrefixName = 'SeniorPrefix')
   AS StudentPrefix
FROM tblStudents

上面的嵌套查询会在每一行执行吗？有没有办法执行一次并用于所有行。

请注意，我专门谈论的是 View ，而不是存储过程。我知道这可以在存储过程中完成。

Answer 1

这实际上取决于您的餐 table 布置。除非 prefixName 被限制为唯一，否则您可能会遇到错误，即子查询返回多行。如果它没有被限制为唯一，但恰好对于 SeniorPrefix 是唯一的，那么您的查询将被执行 1000 次。为了演示我使用了以下 DDL:

CREATE TABLE #tblStudents (ID INT IDENTITY(1, 1), Filler CHAR(100));
INSERT #tblStudents (Filler)
SELECT TOP 10000 NULL
FROM sys.all_objects a, sys.all_objects b;

CREATE TABLE #tblStudentPrefixes (Value VARCHAR(10), PrefixName VARCHAR(20));
INSERT #tblStudentPrefixes (Value, PrefixName) VALUES ('A Value', 'SeniorPrefix');

运行查询会得到以下 IO 输出:

Table '#tblStudentPrefixes'. Scan count 10000, logical reads 10000

Table '#tblStudents'. Scan count 1, logical reads 142

关键是 tblStudentPrefixes 上的 1000 次逻辑读取。它不被限制为唯一的另一个问题是，如果您有重复项，您的查询将失败并出现错误:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

如果您不能将 PrefixName 限制为唯一，那么您可以停止它对每一行执行并使用 TOP 避免错误:

SELECT *, 
   (SELECT TOP 1 value FROM #tblStudentPrefixes WHERE PrefixName = 'SeniorPrefix' ORDER BY Value)
   AS StudentPrefix
FROM #tblStudents

IO 现在变成:

Table '#tblStudentPrefixes'. Scan count 1, logical reads 1

Table '#tblStudents'. Scan count 1, logical reads 142

但是，我仍然建议在此处切换到 CROSS JOIN:

SELECT  s.*, p.Value AS StudentPrefix
FROM    #tblStudents AS s
        CROSS JOIN 
        (   SELECT TOP 1 value 
            FROM #tblStudentPrefixes 
            WHERE PrefixName = 'SeniorPrefix'
            ORDER BY Value
        ) AS p;

执行计划的检查表明使用表假脱机的子选择对于单个值来说是非常不必要的:

因此总而言之，它是否会针对每一行执行取决于您的表设置，但无论您是否在切换到交叉连接时都为优化器提供了更好的机会。

---

编辑

鉴于当 tblStudentPrefixes 中的 SeniorPrefix 不匹配时，您需要从 tblstudent 返回行，并且 PrefixName 当前不是唯一的，那么最好的解决方案是:

SELECT *, 
   (SELECT MAX(value) FROM #tblStudentPrefixes WHERE PrefixName = 'SeniorPrefix')
   AS StudentPrefix
FROM #tblStudents;

如果您确实将其限制为唯一，那么以下 3 个查询会产生(本质上)相同的计划和相同的结果，这只是个人喜好:

SELECT *, 
   (SELECT value FROM #tblStudentPrefixes WHERE PrefixName = 'SeniorPrefix')
   AS StudentPrefix
FROM #tblStudents;

SELECT  s.*, p.Value AS StudentPrefix
FROM    #tblStudents AS s
        LEFT JOIN #tblStudentPrefixes AS p
            ON p.PrefixName = 'SeniorPrefix';

SELECT  s.*, p.Value AS StudentPrefix
FROM    #tblStudents AS s
        OUTER APPLY 
        (   SELECT Value
            FROM #tblStudentPrefixes 
            WHERE PrefixName = 'SeniorPrefix'
        ) AS p;