在单个查询中有多个select语句

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?

我可以在单个查询中实现吗?如果我做了吗?过程变慢?

4 个解决方案

#1

194

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

#2

If you use MyISAM tables, the fastest way is querying directly the stats:

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

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中报告的值。表是错的。

#3

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')

#4

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连续添加的值。我倾向于选择后者，但这取决于个人偏好和应用。如果您确信表永远不会更改，那么您希望数据以单行格式，并且不会添加表。坚持本·詹姆斯的解决方案。否则我建议灵活性，你总是可以破解一个交叉标签结构。

#1

194

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

#2