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

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

sql-server sql

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 年前

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

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;

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;