代码之家  ›  专栏  ›  技术社区  ›  Jebathon

SQL Server-使用多个结果集中的行创建视图

  •  0
  • Jebathon  · 技术社区  · 6 年前

    Select Count(*) From TableA
    
    Select Count(*) From TableB
    
    Select Count(*) From TableC
    
    Select Count(*) From TableD
    

    我想返回如下视图:

    -----------
    | Counts  |
    -----------
    | 2       | --from TableA
    -----------
    | 3       | --from TableB
    ----------
    | 4       | --from TableC
    ----------
    | 5       | --from TableD
    ----------
    

    我尝试使用UNION,例如:

    CREATE VIEW [Foo]
    AS
    SELECT COUNT(*) AS [Counts]
    FROM TableA
    UNION
    SELECT COUNT(*)
    FROM TableB
    UNION
    SELECT COUNT(*)
    FROM TableC
    UNION
    SELECT COUNT(*)
    FROM TableD
    

    但命令是错误的

    -----------
    | Counts  |
    -----------
    | 2       | --from TableA
    -----------
    | 3       | --from TableB
    ----------
    | 5       | --from TableD
    ----------
    | 4       | --from TableC
    ----------
    

    除了UNION之外,还有其他方法可以用来生成我想要的结果集吗?

    3 回复  |  直到 6 年前
        1
  •  2
  •   Ross Bush    6 年前

    非常接近,只需为订单添加一个派生字段。

    CREATE VIEW [Foo]
    AS
    SELECT TOP(100) PERCENT
    *
    FROM
    (
        SELECT COUNT(*) AS [Counts], RowOrder=1
        FROM TableA
        UNION
        SELECT COUNT(*), 2
        FROM TableB
        UNION
        SELECT COUNT(*), 3
        FROM TableC
        UNION
        SELECT COUNT(*), 4
        FROM TableD
    )AS X
    ORDER BY RowOrder
    

    CREATE VIEW [Foo]
    AS
    SELECT 
    *
    FROM
    (
        SELECT COUNT(*) AS [Counts], RowOrder=1
        FROM TableA
        UNION
        SELECT COUNT(*), 2
        FROM TableB
        UNION
        SELECT COUNT(*), 3
        FROM TableC
        UNION
        SELECT COUNT(*), 4
        FROM TableD
    )AS X
    
    ...
    SELECT * FROM Foo ORDER BY RowOrder
    
        2
  •  1
  •   Yogesh Sharma    6 年前

    UNION 内部为您进行排序,您需要 union all 或者定义显式排序:

    SELECT COUNT(*) AS [Counts]
    FROM TableA UNION ALL
    SELECT COUNT(*)
    FROM TableB UNION ALL
    SELECT COUNT(*)
    FROM TableC UNION ALL
    SELECT COUNT(*)
    FROM TableD;
    

    您还可以定义显式排序:

    SELECT COUNT(*) AS [Counts], 1 as SortOrder
    FROM TableA UNION 
    SELECT COUNT(*), 2
    FROM TableB UNION 
    SELECT COUNT(*), 3
    FROM TableC UNION 
    SELECT COUNT(*), 4
    FROM TableD;
    

    所以,你需要使用 order by Foo

    select f.*
    from Foo f
    order by SortOrder;
    
        3
  •  1
  •   Gordon Linoff    6 年前

    视图返回 无序 结果集。您可以尝试执行以下操作:

    create view v_counts as
        select . . .
        order by . . .
    

    您将得到错误消息:

    ORDER BY子句在视图、内联函数、派生表、子查询和公共表表达式中无效,除非还指定了TOP、OFFSET或FOR XML。

    正如这条信息所暗示的,有一种作弊的方法可以绕过这个问题,但我建议不要这样做——即使你作弊,观点也不是这样 放心 按顺序返回结果。

    而是包括表名:

    Select 'TableA' as which, Count(*) From TableA
    union all
    Select 'TableB' as which, Count(*) From TableB
    union all
    Select 'TableC' as which, Count(*) From TableC
    union all
    Select 'TableD' as which, Count(*) From TableD;