SQL查询与临时表的内部联接

debugcn 发表于 Dev

ripsin

我正在尝试使用此处显示的示例在临时表中设置值：SQL输出：是否可以创建一个临时输出列？

我已经创建了初始表，在该表中，我使用列名来建立新的“ operdesc”值列的填充。

这是我的工作表查询，可在其中获取所需的COLUMN_NAME值。我想添加一个临时列“ OPERAND”：

SELECT     COLUMN_NAME, DATA_TYPE, 'OPERAND' AS TempField, 
                  CASE WHEN COLUMN_NAME = 'Street' THEN '=' WHEN COLUMN_NAME = 'Town' THEN 'CW' END AS OPERAND FROM         INFORMATION_SCHEMA.COLUMNS WHERE    (TABLE_SCHEMA = 'dbo') AND (TABLE_NAME = 'Customers') ORDER BY COLUMN_NAME

但是，我想更进一步，并使用临时连接对其进行简化。我遵循以下示例：SQL输出：是否可以创建一个临时输出列？但是却挂断了我的剧本。这是我所拥有的，但无法正常工作，希望我已经快结束了：

WITH XOperLU (xopername, xoperdesc)
 AS
 (
  SELECT xopername, CAST(xoperdesc AS VARCHAR(20))
    FROM (
          VALUES ('Street', 'SS'), 
                 ('Town', 'TW')                    
         ) AS XOperLU (xopername, xoperdesc)
 ) SELECT COLUMN_NAME as T1.COLUMN_NAME, DATA_TYPE as T1.DATA_TYPE, S1.xoperdesc AS Description FROM INFORMATION_SCHEMA.COLUMNS  AS T1 WHERE    (TABLE_SCHEMA = 'dbo') AND (TABLE_NAME = 'Clients') 
   INNER JOIN XOperLU AS S1
      ON S1.xopername = T1.COLUMN_NAME;

“客户”表是一个视图。

提前非常感谢！

西蒙

您几乎可以，但是您T1在列别名中使用了别名，而不是在列中使用了别名，最重要的是，您WHERE之前使用过别名JOIN。这有效：

WITH XOperLU (xopername, xoperdesc)
 AS
 (
  SELECT xopername, CAST(xoperdesc AS VARCHAR(20))
    FROM (
          VALUES ('Street', 'SS'), 
                 ('Town', 'TW')                    
         ) AS XOperLU (xopername, xoperdesc)
 ) SELECT T1.COLUMN_NAME as COLUMN_NAME, T1.DATA_TYPE as DATA_TYPE, S1.xoperdesc AS Description FROM INFORMATION_SCHEMA.COLUMNS  AS T1 
   INNER JOIN XOperLU AS S1
      ON S1.xopername = T1.COLUMN_NAME
      WHERE    (TABLE_SCHEMA = 'dbo') AND (TABLE_NAME = 'Clients')

如果要查看所有未将指定说明设置为其他内容的其他列，例如“ XX”，则可以使用左连接：

WITH XOperLU (xopername, xoperdesc)
 AS
 (
  SELECT xopername, CAST(xoperdesc AS VARCHAR(20))
    FROM (
          VALUES ('Street', 'SS'), 
                 ('Town', 'TW')                    
         ) AS XOperLU (xopername, xoperdesc)
 ) SELECT T1.COLUMN_NAME as COLUMN_NAME, T1.DATA_TYPE as DATA_TYPE, ISNULL(S1.xoperdesc,'XX') AS Description FROM INFORMATION_SCHEMA.COLUMNS  AS T1 
   LEFT JOIN XOperLU AS S1
      ON S1.xopername = T1.COLUMN_NAME
      WHERE    (TABLE_SCHEMA = 'dbo') AND (TABLE_NAME = 'Clients')

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。