使用变量通过一条 SELECT 语句对多个表进行 SQL 查询

Question

我对 SQL 还很陌生，但是考虑到下面的示例，有没有办法压缩查询？根据我的理解，SQL 没有数组，但是是否可以以某种方式存储表并执行一个 SELECT 语句，就像一个变量(Select Count(*) From Table_Name) As Table_Name)？

SELECT
(SELECT COUNT(*) FROM ActivityLog) AS ActivityLog,
(SELECT COUNT(*) FROM ActivityLogin) AS ActivityLogin,
(SELECT COUNT(*) FROM ActivityPlayContent) AS ActivityPlayContent,
(SELECT COUNT(*) FROM ChangeLog) AS ChangeLog,
(SELECT COUNT(*) FROM ContentApprovalLog) AS ContentApprovalLog,
(SELECT COUNT(*) FROM ExceptionLog) AS ExceptionLog,
(SELECT COUNT(*) FROM State) AS State

Answer 1

如果您经常需要此查询并希望使其更简单，您可以创建 VIEW 。然后，您必须像查询表一样查询 View 。

SELECT * FROM MyView

当您有多个复杂查询并且希望在其他查询中重用它们时(例如将两个 View 连接在一起或对 View 进行子查询)， View 特别有用。

按如下所示为您的查询创建 View

CREATE VIEW MyView AS
SELECT
(SELECT COUNT(*) FROM ActivityLog) AS ActivityLog,
(SELECT COUNT(*) FROM ActivityLogin) AS ActivityLogin,
(SELECT COUNT(*) FROM ActivityPlayContent) AS ActivityPlayContent,
(SELECT COUNT(*) FROM ChangeLog) AS ChangeLog,
(SELECT COUNT(*) FROM ContentApprovalLog) AS ContentApprovalLog,
(SELECT COUNT(*) FROM ExceptionLog) AS ExceptionLog,
(SELECT COUNT(*) FROM State) AS State