gpt4 book ai didi

SQL 查询内部连接与临时表

转载 作者:行者123 更新时间:2023-12-04 16:08:43 26 4
gpt4 key购买 nike

我正在尝试使用此处显示的示例在临时表中设置值:SQL output: Is it possible to create a temporary output column?

我已经创建了我的初始表,我在其中使用列名作为新列值“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 output: Is it possible to create a temporary output column?但我挂断了我的剧本。这就是我所拥有的,但它不起作用,希望我很接近:

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;

“客户”表是一个 View 。

非常感谢!

最佳答案

你几乎没问题,但你在列别名中使用了 T1 别名而不是列,最重要的是你在 JOIN 之前有 WHERE。这有效:

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')

关于SQL 查询内部连接与临时表,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/19342680/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com