单个查询中的多个选择语句

我正在用 php (mysql)生成一个报告,

例如:

`select count(id) as tot_user from user_table
select count(id) as tot_cat from cat_table
select count(id) as tot_course from course_table`

像这样，我有12张桌子。

我可以在一个单一的查询。如果我做到了? 过程变得缓慢？

455677 次浏览
小开

您当然可以使用由 Ben James 假设的 Select Agagation 语句，但是这将导致视图中的列与表中的列一样多。另一种方法可以如下:

SELECT COUNT(user_table.id) AS TableCount,'user_table' AS TableSource FROM user_table
UNION SELECT COUNT(cat_table.id) AS TableCount,'cat_table' AS TableSource FROM cat_table
UNION SELECT COUNT(course_table.id) AS TableCount, 'course_table' AS TableSource From course_table;

这种方法的好处在于，您可以显式地编写 Union 语句，并生成一个视图或创建一个临时表来保存使用变量代替表名从 Proc cal 连续添加的值。我更倾向于后者，但这实际上取决于个人的喜好和应用。如果确保表永远不会更改，则需要单行格式的数据，并且不需要添加表。坚持本 · 詹姆斯的解决方案。否则我会建议灵活性，你总是可以黑客交叉标签结构。

小开
最佳答案 
SELECT  (
SELECT COUNT(*)
FROM   user_table
) AS tot_user,
(
SELECT COUNT(*)
FROM   cat_table
) AS tot_cat,
(
SELECT COUNT(*)
FROM   course_table
) AS tot_course
小开

如果使用 MyISAM 表，最快的方法是直接查询统计数据:

select table_name, table_rows
from information_schema.tables
where
table_schema='databasename' and
table_name in ('user_table','cat_table','course_table')

如果有 InnoDB，则必须使用 count ()进行查询，因为 information _ schema. tables 中报告的值是错误的。

小开 
select RTRIM(A.FIELD) from SCHEMA.TABLE A where RTRIM(A.FIELD) =  ('10544175A')
UNION
select RTRIM(A.FIELD) from SCHEMA.TABLE A where RTRIM(A.FIELD) = ('10328189B')
UNION
select RTRIM(A.FIELD) from SCHEMA.TABLE A where RTRIM(A.FIELD) = ('103498732H')
小开

我知道这是一个旧的堆栈，但我会张贴这个多 SQL 选择情况

    SELECT bp.bizid, bp.usrid, bp.website,
ROUND((SELECT SUM(rating) FROM ratings WHERE bizid=bp.bizid)/(SELECT COUNT(*) FROM ratings WHERE bizid=bp.bizid), 1) AS 'ratings',
(SELECT COUNT(*) FROM bzreviews WHERE bizid=bp.bizid) AS 'ttlreviews',
bp.phoneno, als.bizname,
(SELECT COUNT(*) FROM endorsment WHERE bizid=bp.bizid) AS 'endorses'
, als.imgname, bp.`location`, bp.`ownership`,
(SELECT COUNT(*) FROM follows WHERE bizid=bp.bizid) AS 'followers',
bp.categories, bp.openhours, bp.bizdecri FROM bizprofile AS bp
INNER JOIN alluser AS als ON bp.usrid=als.userid
WHERE als.usertype='Business'
小开 
SELECT t1.credit,
t2.debit
FROM   (SELECT Sum(c.total_amount) AS credit
FROM   credit c
WHERE  c.status = "a") AS t1,
(SELECT Sum(d.total_amount) AS debit
FROM   debit d
WHERE  d.status = "a") AS t2

提交答案