日期:2014-05-16  浏览次数:20713 次

mysql统计多表交叉组合总数
>mysql -h localhost -u root -p


mysql> use world;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)


mysql> select count(*) from city;
+----------+
| count(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.00 sec)


mysql> select count(*) from country;
+----------+
| count(*) |
+----------+
|      239 |
+----------+
1 row in set (0.00 sec)


mysql> select 4079*239
    -> ;
+----------+
| 4079*239 |
+----------+
|   974881 |
+----------+
1 row in set (0.00 sec)


这里就是计算交叉组合数量,因为他们之间一个相同的关联字段
mysql> select count(*) from city,country
    -> ;
+----------+
| count(*) |
+----------+
|   974881 |
+----------+
1 row in set (0.00 sec)


实际上mysql所做的操作就是把你所查询表的数量相乘得到的结果!
mysql> select count(*) from city,country,countrylanguage;
+-----------+
| count(*)  |
+-----------+
| 959282904 |
+-----------+
1 row in set (0.02 sec)

mysql> select count(*) from countrylanguage;
+----------+
| count(*) |
+----------+
|      984 |
+----------+
1 row in set (0.00 sec)

mysql> select 4079*239*984;
+--------------+
| 4079*239*984 |
+--------------+
|    959282904 |
+--------------+
1 row in set (0.00 sec)