SQL Server，使用多个选择语句查看
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2732712/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server, View using multiple select statements
提问by phil
I've banging my head for hours, it seems simple enough, but here goes:
我已经敲了几个小时的头，这看起来很简单，但这里是：
I'd like to create a view using multiple select statements that outputs a Single record-set Example:
我想使用输出单个记录集示例的多个选择语句创建一个视图：
CREATE VIEW dbo.TestDB
AS
SELECT X AS 'First'
FROM The_Table
WHERE The_Value = 'y'
SELECT X AS 'Second'
FROM The_Table
WHERE The_Value = 'z'
i wanted to output the following recordset:
我想输出以下记录集：
Column_1 | Column_2
'First' 'Second'
any help would be greatly appreciated! -Thanks.
任何帮助将不胜感激！-谢谢。
回答by Greg Roberts
A union might be what you want, but keep in mind you need to have the exact same columns so you would need to add a null column to each select.
联合可能是您想要的，但请记住，您需要拥有完全相同的列，因此您需要为每个选择添加一个空列。
SELECT X AS 'First', Null as 'Second'
FROM The_Table
WHERE The_Value = 'y'
Union
SELECT null as 'First', X AS 'Second'
FROM The_Table
WHERE The_Value = 'z'
This will combine the two results and give you only the unique combinations. My guess is that this isn't what you are looking for.
这将结合两个结果，并只为您提供独特的组合。 我的猜测是，这不是你要找的。
Is there a reason why you can't accomplish this in one query or do some subqueries perhaps? Maybe you can provide a more concrete example.
是否有理由不能在一个查询中完成此操作或执行某些子查询？ 也许你可以提供一个更具体的例子。
回答by KM.
if you want this:
如果你想要这个：
Column_1 | Column_2
'First' null
null 'Second'
you can use the UNION like suggested in the other answers, but if you really want then on the same row like in your question:
您可以像其他答案中建议的那样使用 UNION，但如果您真的想要，那么在您的问题中的同一行中使用：
Column_1 | Column_2
'First' 'Second'
try something like this:
尝试这样的事情：
CREATE VIEW dbo.TestDB
AS
SELECT
dt.First,dt2.Second
FROM (SELECT
X AS 'First',ROW_NUMBER() OVER(ORDER BY X) AS RowNumber
FROM The_Table
WHERE The_Value = 'y'
) dt
LEFT OUTER JOIN (SELECT
X AS 'Second',ROW_NUMBER() OVER(ORDER BY X) AS RowNumber
FROM The_Table
WHERE The_Value = 'z'
) dt2 ON dt.RowNumber=dt2.RowNumber
go
I'm not sure how to join the tables, no info about PKs or how to join them is given.
我不确定如何加入表格，没有提供有关 PK 或如何加入表格的信息。
回答by SQLMenace
Untested
未经测试
CREATE VIEW dbo.TestDB
AS
SELECT CASE The_Value when 'y' then x else NULL END AS 'First',
CASE The_Value when 'z' then x else NULL END AS 'Second'
FROM The_Table
WHERE The_Value in( 'y','z')
