主页 / dba / 问题 / 157402
Accepted
Jon49
Asked: 2016-12-07 12:04:20 +0800 CST

JOIN 3 没有 Union ALL 的表，也没有具有多个 ID 的嵌套查询

  • 772

不确定这是否可能，但我想在没有UNION ALL或嵌套查询的情况下加入具有多个 ID 的多个表。您可以在此处查看相关问题，该问题在表格视图中显示所有内容。我想创建一个table view但不允许使用上面提到的内容。我已经想出了如何使用UNION ALL.

所以，我有一个Main表，它引用了一个名为Fact. 该Fact表包含可由 、 或 (revision) 引用LanguageID的ClientID数据StatusID。Main和表通过称为（为简单起见未显示）Fact的中间表相关联。FactLink

MainView我想得到的最终结果可索引表如下所示：

主视图

MainID | StatusOrder | LanguageID | ClientID | Description   | Disclaimer  | Other
-------+-------------+------------+----------+---------------+-------------+------
50     | 10          | 1          | 1        | Some text     | Disclaimer1 | Blah
50     | 10          | 2          | 1        | Otro texto    | NULL        | Blah
50     | 20          | 1          | 2        | Modified text | NULL        | Blah
55     | 10          | 1          | 1        | Some text 2   | Disclaimer2 | Blah Blah
55     | 10          | 1          | 2        | NULL          | Disclaimer3 | Blah Blah

DROP TABLE IF EXISTS #main
CREATE TABLE #main
(
    ID INT NOT NULL,
    DescriptionID INT NOT NULL,
    DisclaimerID INT NOT NULL,
    Other NVARCHAR(500)
)

Drop table if exists #fact
CREATE TABLE #fact
(
    FactID INT NOT NULL,
    LanguageID INT NOT NULL,
    StatusID INT NOT NULL,
    ClientID INT NOT NULL,
    Description NVARCHAR(MAX)
)

DROP TABLE IF EXISTS #status
CREATE TABLE #status
(
    StatusID INT NOT NULL,
    [Order] INT NOT NULL
)

INSERT INTO #main
    (ID, DescriptionID, DisclaimerID, Other)
    VALUES
    (50, 1, 2, 'Blah'),
    (55, 4, 3, 'Blah Blah')

INSERT INTO #fact
    (FactID, LanguageID, StatusID, ClientID, Description)
    VALUES
    (1, 1, 1, 1, N'Some text'),
    (1, 2, 1, 1, N'Otro texto'),
    (1, 1, 3, 2, N'Modified text'),
    (2, 1, 1, 1, N'Disclaimer1'),
    (3, 1, 1, 1, N'Disclaimer2'),
    (3, 1, 2, 1, N'Disclaimer3'),
    (4, 1, 1, 1, N'Some text 2')

INSERT INTO #status
    (StatusID, [Order])
    VALUES
    (1, 10),
    (2, 100),
    (3, 20)

这是我到目前为止所拥有的。但它不包括最后一个条目，也没有考虑StatusOrder. 我不知道从这里去哪里。

SELECT
    t.ID, t.Other,
    fDescription.Description,
    fDisclaimer.Description Disclaimer,
    COALESCE(fDescription.LanguageID, fDisclaimer.LanguageID) LanguageID,
    COALESCE(fDescription.ClientID, fDisclaimer.ClientID) ClientID,
    COALESCE(fDescription.StatusID, fDisclaimer.StatusID) StatusID
FROM #main t
JOIN #fact fDescription
    ON fDescription.FactID = t.DescriptionID
LEFT OUTER JOIN #fact fDisclaimer
    ON fDisclaimer.FactID = t.DisclaimerID
    AND fDisclaimer.ClientID = fDescription.ClientID
    AND fDisclaimer.LanguageID = fDescription.LanguageID
    AND fDisclaimer.StatusID = fDescription.StatusID
sql-server join

1 个回答

  1. Best Answer

    我会按要求回答问题，但我希望它不会解决您的实际问题。您希望将您在另一篇文章中的查询转换为可以转换为索引视图的查询。我看到的最大问题是您的数据模型要求将“事实”表的多行中的数据组合成单行。这样的要求与索引视图功能作斗争。许多T-SQL 限制似乎是围绕停止这种类型的操作（以及其他操作）而设计的：

    索引视图限制

    对于您的问题，该列表中最重要的条目是对 MAX、MIN、自联接、子查询、APPLY 和 UNPIVOT 的禁止。所有这些都可用于重写您的 UNION ALL 查询，但它们都不允许在索引视图中使用。有一种方法可以重写它，但你只会陷入更深的困境。您从以下查询开始：

    SELECT
  Main.ID,
  s.[Order],
  f.LanguageID,
  f.ClientID,
  f.Description,
  NULL Disclaimer,
  Main.Other
FROM Main
JOIN Fact f
ON f.FactID = Main.DescriptionID
JOIN Status s ON s.StatusID = f.StatusID
UNION ALL
SELECT
  Main.ID,
  s.[Order],
  f.LanguageID,
  f.ClientID,
  NULL Description,
  f.Description Disclaimer,
Main.Other
FROM Main
JOIN Fact f
ON f.FactID = Main.DisclaimerID
JOIN Status s ON s.StatusID = f.StatusID;

    至少有一种方法可以以有效的索引视图定义的方式重写该查询。首先创建一个只有两行的虚拟表：

    CREATE TABLE DIM_NUMBERS (NUM INT NOT NULL);
INSERT INTO DIM_NUMBERS VALUES (1), (2);

    然后，您可以像这样定义您的视图：

    CREATE VIEW dbo.X_VIEW
WITH SCHEMABINDING  
AS  
SELECT
  Main.ID,
  s.[Order],
  f.LanguageID,
  f.ClientID,
  CAST(CASE WHEN d.NUM = 1 THEN f.Description ELSE NULL END AS NVARCHAR(2000)) [Description],
  CAST(CASE WHEN d.NUM = 2 THEN f.Description ELSE NULL END AS NVARCHAR(2000)) Disclaimer,
  Main.Other
FROM dbo.Main
CROSS JOIN dbo.DIM_NUMBERS d
JOIN dbo.Fact f
  ON (f.FactID = Main.DescriptionID AND d.NUM = 1) OR (f.FactID = Main.DisclaimerID AND d.NUM = 2)
JOIN dbo.Status s ON s.StatusID = f.StatusID;

CREATE UNIQUE CLUSTERED INDEX IDX_X_VIEW   
    ON dbo.X_VIEW  (ID, [Order], LanguageID, ClientID, [Description], Disclaimer, Other);

    我已重写您的查询以不使用 UNION ALL，但我没有包含 GROUP BY，因为索引视图中不允许使用 MIN 和 MAX。没有任何聚合函数可以帮助您，因为您有一个 NVARCHAR 列。如果您有一个数字列，那么您可能可以使用 SUM(COALESCE(COLUMN_NAME, 0)) 列来解决这个问题，但您不能将 SUM 聚合应用于 NVARCHAR。

    我不会尝试将查询硬塞到索引视图定义中，而是尝试考虑其他解决方案。您可以将结果存储在表格中吗？根据描述列的长度，该列与索引视图之间的空间差异可能不会那么大。你能以某种有帮助的方式改变数据模型吗？

    • 3

相关问题