MySQL 单个查询中的多个选择语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1775168/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Multiple select statements in Single query
提问by boss
I am generating a report in php (mysql),
我在 php (mysql) 中生成报告，
ex:
前任：
`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`
Like this I have 12 tables.
像这样我有12张桌子。
Can i make it in single query. If i did? Process gets slow?
我可以在单个查询中创建它吗？如果我做了？进程变慢？
回答by sathish
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
回答by Pentium10
If you use MyISAM tables, the fastest way is querying directly the stats:
如果您使用 MyISAM 表，最快的方法是直接查询统计信息：
select table_name, table_rows
from information_schema.tables
where
table_schema='databasename' and
table_name in ('user_table','cat_table','course_table')
If you have InnoDB you have to query with count() as the reported value in information_schema.tables is wrong.
如果您有 InnoDB，则必须使用 count() 进行查询，因为 information_schema.tables 中的报告值是错误的。
回答by Miguel Castaneda
You can certainly us the a Select Agregation statement as Postulated by Ben James, However This will result in a view with as many columns as you have tables. An alternate method may be as follows:
您当然可以使用 Ben James 假设的 Select Agregation 语句，但是这将导致视图的列数与表的数量相同。另一种方法可能如下：
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;
The Nice thing about an approch like this is that you can explicitly write the Union statements and generate a view or create a temp table to hold values that are added consecutively from a Proc cals using variables in place of your table names. I tend to go more with the latter, but it really depends on personal preference and application. If you are sure the tables will never change, you want the data in a single row format, and you will not be adding tables. stick with Ben James' solution. Otherwise I'd advise flexibility, you can always hack a cross tab struc.
像这样的方法的好处在于，您可以显式地编写 Union 语句并生成视图或创建一个临时表来保存使用变量代替表名从 Proc cals 连续添加的值。我更倾向于后者，但这实际上取决于个人喜好和应用。如果您确定表格永远不会改变，您希望数据采用单行格式，并且您不会添加表格。坚持本詹姆斯的解决方案。否则我建议灵活性，你总是可以破解交叉表结构。
回答by net.tunneler
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')
回答by Niclausel
I know this is an old stack but i will post this Multi-SQL select case
我知道这是一个旧堆栈，但我会发布这个多 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'
回答by Mani
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
