视图中具有静态值的另一个表中的SQL Server列

debugcn 发表于 Dev

用户名

有没有一种方法可以让另一个表中的列的值在View>示例中始终相同：

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

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

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

加雷斯

这实际上取决于您的表设置。除非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输出：

表'#tblStudentPrefixes'。扫描计数10000，逻辑读取10000

表'#tblStudents'。扫描计数1，逻辑读取142

密钥是tblStudentPrefixes上的1000个逻辑读取。不被限制为唯一的另一个问题是，如果重复，则查询将失败，并显示以下错误：

子查询返回了1个以上的值。当子查询遵循=，！=，<，<=，>，> =或将子查询用作表达式时，这是不允许的。

如果不能限制PrefixName唯一性，则可以使用以下命令停止对每一行的执行并避免错误TOP：

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

IO现在变为：

表'#tblStudentPrefixes'。扫描计数1，逻辑读取1

表'#tblStudents'。扫描计数1，逻辑读取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;

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

在此处输入图片说明

因此，总而言之，它取决于表的设置是否将针对每一行执行，但是不管您是否向优化器提供了更好的机会（如果您切换到交叉联接）。

编辑

鉴于您需要从in中tblstudent没有匹配项的情况下返回行，并且当前的构造不唯一，因此最好的解决方案是：SeniorPrefixtblStudentPrefixesPrefixName

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;