在 SQL 中垂直联合列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13801450/
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
Union Columns Vertically in SQL
提问by Saeid
Assume these Tables:
假设这些表：
Group: (Id, Title): {1,G1}, {2,G2}, {3,G3}, {4, G4}
组：（ID，标题）： {1,G1}, {2,G2}, {3,G3}, {4, G4}
Category: (Id, Title): {1, Cat1}, {2, Cat2}, {3, Cat3}, {4, Cat4}
类别：（ID，标题）：{1, Cat1}, {2, Cat2}, {3, Cat3}, {4, Cat4}
Product: (Id, GroupId, CategoryId, Name):
产品：（Id、GroupId、CategoryId、名称）：
{1, 1, 1, G1C1P1},
{2, 1, 2, G1C2P2},
{3, 1, 2, G1C2P3},
{4, 2, 2, G2C2P4},
{5, 2, 2, G2C2P5},
{6, 3, 1, G3C1P6},
{7, 3, 3, G3C3P7}
Dealer: (Id, Name): {1, 'Dealer1'}, {2, 'Dealer2'}, {3, 'Dealer3'}
经销商: (Id, Name): {1, 'Dealer1'}, {2, 'Dealer2'}, {3, 'Dealer3'}
ProductDealer(Id, ProductId (UK), DealerId, LastSale, Number):
ProductDealer(Id, ProductId (UK), DealerId, LastSale, Number):
{1, 1, 1, 5, '2012-12-10 12:34:31'},
{2, 2, 2, 120, '2012-11-10 12:34:31'},
{3, 5, 1, 75, '2012-12-02 12:34:31'}
So I going to create a full view of product, this is my first try:
所以我要创建一个产品的完整视图，这是我的第一次尝试：
SELECT
[PR].[Id],
[PR].[Name],
[PR].[GroupId],
[GR].[Title] AS [Group],
[PR].[CategoryId],
[CA].[Title] AS [Category]
FROM [dbo].[Product] AS [PR]
INNER JOIN [dbo].[Group] AS [GR] ON [PR].[GroupId] = [GR].[Id]
INNER JOIN [dbo].[Category] AS [CA] ON [PR].[CategoryId] = [CA].[Id]
Then I think to add ProductDealercolumns to view, so I try this one:
然后我想添加ProductDealer列来查看，所以我尝试了这个：
SELECT
[PR].[Id],
[PR].[Name],
[PR].[GroupId],
[GR].[Title] AS [Group],
[PR].[CategoryId],
[CA].[Title] AS [Category],
ISNULL(
(SELECT [PD].[Id] FROM [dbo].[ProductDealer] AS [PD]
WHERE [PD].[ProductId] = [PR].[Id]),
CAST(-1 AS BIGINT)
) AS [ProductDealerId],
ISNULL(
(SELECT [DE].[Id] FROM [dbo].[Dealer] AS [DE]
INNER JOIN [dbo].[ProductDealer] AS [PD] ON [DE].[Id] = [PD].[DealerId]
WHERE [PD].[ProductId] = [PR].[Id]),
CAST(-1 AS BIGINT)
) AS [DealerId],
ISNULL(
(SELECT [DE].[Name] FROM [dbo].[Dealer] AS [DE]
INNER JOIN [dbo].[ProductDealer] AS [PD] ON [DE].[Id] = [PD].[DealerId]
WHERE [PD].[ProductId] = [PR].[Id]),
CAST('HaveNotDealer' AS NVARCHAR)
) AS [Dealer],
ISNULL(
(SELECT [PD].[LastSale] FROM [dbo].[ProductDealer] AS [PD]
WHERE [PD].[ProductId] = [PR].[Id]),
CAST('0001-01-01 00:00:01' AS DATETIME2)
) AS [LastSale],
ISNULL(
(SELECT [PD].[Number] FROM [dbo].[ProductDealer] AS [PD]
WHERE [PD].[ProductId] = [PR].[Id]),
CAST(0 AS BIGINT)
) AS [SaleNumber]
FROM [dbo].[Product] AS [PR]
INNER JOIN [dbo].[Group] AS [GR] ON [PR].[GroupId] = [GR].[Id]
INNER JOIN [dbo].[Category] AS [CA] ON [PR].[CategoryId] = [CA].[Id]
As you see for each column in ProductDealerI write a full select query I don't know is there any better way to do this? something like union this columns to first view, what is your suggestion to best implement it?
正如你看到的，ProductDealer我写了一个完整的选择查询中的每一列，我不知道有没有更好的方法来做到这一点？像联合这个专栏这样的东西可以首先查看，你有什么建议来最好地实施它？
Update
更新
In ProductDealerTable, the ProductIdis UK, So each Product could have one dealer or nothing, I want if the product have a dealer get ProductDealerColumns in view and if not get my default values like: (-1, 'HaveNotDealer').
在ProductDealer表中，ProductId是英国，所以每个产品可以有一个经销商或没有，我希望如果产品有经销商ProductDealer，请查看列，如果没有，则获取我的默认值，例如：（-1，'HaveNotDealer'）。
回答by Alexey A.
SELECT
[PR].[Id],
[PR].[Name],
[PR].[GroupId],
[GR].[Title] AS [Group],
[PR].[CategoryId],
[CA].[Title] AS [Category],
ISNULL([PD].[Id],
CAST(-1 AS BIGINT)) AS [ProductDealerId],
ISNULL([D].Id,
CAST(-1 AS BIGINT)) as DealerId,
ISNULL([D].Name,
CAST('HaveNotDealer' AS NVARCHAR)) as DealerName,
ISNULL(PD.LastSale,
CAST('0001-01-01 00:00:01' AS DATETIME2)) as LastSale,
ISNULL([PD].Number,
CAST(0 AS BIGINT)) as SaleNumber
FROM [dbo].[Product] AS [PR]
INNER JOIN [dbo].[Group] AS [GR] ON [PR].[GroupId] = [GR].[Id]
INNER JOIN [dbo].[Category] AS [CA] ON [PR].[CategoryId] = [CA].[Id]
LEFT OUTER JOIN [dbo].[ProductDealer] AS [PD] ON [PR].[Id] = [PD].[ProductId]
LEFT OUTER JOIN [dbo].[Dealer] AS [D] ON [PD].DealerId = [D].Id
回答by onedaywhen
Here is the "vertical UNION" version:
这是“垂直联合”版本：
WITH "Group"
AS
(
SELECT *
FROM (
VALUES (1,'G1'), (2,'G2'), (3,'G3'), (4, 'G4')
) AS T (Id, Title)
),
Category
AS
(
SELECT *
FROM (
VALUES (1, 'Cat1'), (2, 'Cat2'), (3, 'Cat3'), (4, 'Cat4')
) AS T (Id, Title)
),
Product
AS
(
SELECT *
FROM (
VALUES (1, 1, 1, 'G1C1P1'),
(2, 1, 2, 'G1C2P2'),
(3, 1, 2, 'G1C2P3'),
(4, 2, 2, 'G2C2P4'),
(5, 2, 2, 'G2C2P5'),
(6, 3, 1, 'G3C1P6'),
(7, 3, 3, 'G3C3P7')
) AS T (Id, GroupId, CategoryId, Name)
),
Dealer
AS
(
SELECT *
FROM (
VALUES (1, 'Dealer1'), (2, 'Dealer2'), (3, 'Dealer3')
) AS T (Id, Name)
),
ProductDealer
AS
(
SELECT *
FROM (
VALUES (1, 1, 1, 5, '2012-12-10 12:34:31'),
(2, 2, 2, 120, '2012-11-10 12:34:31'),
(3, 5, 1, 75, '2012-12-02 12:34:31')
) AS T (Id, ProductId, DealerId, Number, LastSale)
)
SELECT PR.Id, PR.Name, PR.GroupId, GR.Title AS "Group",
PR.CategoryId, CA.Title AS Category,
PD.Id AS ProductDealerId,
D.Id DealerId,
D.Name AS DealerName,
PD.LastSale AS LastSale,
PD.Number AS SaleNumber
FROM Product AS PR
JOIN "Group" AS GR ON PR.GroupId = GR.Id
JOIN Category AS CA ON PR.CategoryId = CA.Id
JOIN ProductDealer AS PD ON PR.Id = PD.ProductId
JOIN Dealer AS D ON PD.DealerId = D.Id
UNION
SELECT PR.Id, PR.Name, PR.GroupId, GR.Title AS "Group",
PR.CategoryId, CA.Title AS Category,
PD.Id AS ProductDealerId,
CAST(-1 AS BIGINT) DealerId,
CAST('HaveNotDealer' AS NVARCHAR) AS DealerName,
PD.LastSale AS LastSale,
PD.Number AS SaleNumber
FROM Product AS PR
JOIN "Group" AS GR ON PR.GroupId = GR.Id
JOIN Category AS CA ON PR.CategoryId = CA.Id
JOIN ProductDealer AS PD ON PR.Id = PD.ProductId
WHERE PD.DealerId NOT IN ( SELECT Id FROM Dealer )
UNION
SELECT PR.Id, PR.Name, PR.GroupId, GR.Title AS "Group",
PR.CategoryId, CA.Title AS Category,
CAST(-1 AS BIGINT) AS ProductDealerId,
CAST(-1 AS BIGINT) DealerId,
CAST('HaveNotDealer' AS NVARCHAR) AS DealerName,
CAST('0001-01-01 00:00:01' AS DATETIME2) AS LastSale,
CAST(0 AS BIGINT) AS SaleNumber
FROM Product AS PR
JOIN "Group" AS GR ON PR.GroupId = GR.Id
JOIN Category AS CA ON PR.CategoryId = CA.Id
WHERE PR.Id NOT IN ( SELECT ProductId FROM ProductDealer );
回答by HeyWatchThis
Perhaps only answering this question in "spirit", but this is the "Union Columns Vertically" answer I was looking for when I stumbled upon this Question =)
也许只是在“精神”中回答这个问题，但这是我偶然发现这个问题时正在寻找的“垂直联合列”的答案=）
with aa(col1, col2) as (
select * from (values (1,2) ) blah
),
bb(col3, col4) as (
select * from (values (5,6) ) blah
)
select select aa.col1, aa.col2, bb.col3, bb.col4
from aa , bb
Gives
给
col1|col2|col3|col4
1 |2 |5 |6
DisclaimerThe above only works cleanly for single row aa, bb.
免责声明以上仅适用于单行aa，bb。
回答by RBarryYoung
Like this:
像这样：
SELECT
[PR].[Id],
[PR].[Name],
[PR].[GroupId],
[GR].[Title] AS [Group],
[PR].[CategoryId],
[CA].[Title] AS [Category],
PD.ID AS ProductDealerID,
PD.DealerId,
DE.Name AS Dealer,
PD.LastSale,
PD.Number AS SaleNumber
FROM [dbo].[Product] AS [PR]
INNER JOIN [dbo].[Group] AS [GR] ON [PR].[GroupId] = [GR].[Id]
INNER JOIN [dbo].[Category] AS [CA] ON [PR].[CategoryId] = [CA].[Id]
LEFT JOIN ProductDealer AS [PD] ON PD.ProductId = PR.ID
LEFT JOIN Dealer AS DE ON DE.ID = PD.DealerId
