为什么我加入了笛卡尔项目？

Question

我正在尝试为报告创建一个查询。我有一张licenses表和一张users表，license_assignments作为许多表为用户分配许可座位：

mysql> CREATE TABLE license_assignments ( `uid` int(10) unsigned DEFAULT NULL, `lid` int(1) unsigned NOT NULL, `delta` int(10) unsigned NOT NULL, PRIMARY KEY (`lid`, `delta`), KEY `uid` (`uid`));
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO license_assignments VALUES (1, 2, 1), (1,2,2), (1,2,3), (NULL, 2, 4), (NULL, 2, 5), (NULL, 2, 6);
Query OK, 6 rows affected (0.03 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * FROM license_assignments;
+------+-----+-------+
| uid  | lid | delta |
+------+-----+-------+
| NULL |   2 |     4 |
| NULL |   2 |     5 |
| NULL |   2 |     6 |
|    1 |   2 |     1 |
|    1 |   2 |     2 |
|    1 |   2 |     3 |
+------+-----+-------+
6 rows in set (0.00 sec)

我要创建的报告必须向我显示属于特定许可证的许可席位的总数.

mysql> select COUNT(lid) FROM license_assignments all_licenses WHERE lid = 2;
+------------+
| COUNT(lid) |
+------------+
|          6 |
+------------+
1 row in set (0.00 sec)

..。其中有多少席位仍未分配(没有相关的用户记录)：

mysql> select COUNT(lid) FROM license_assignments unassigned_licenses WHERE lid = 2 AND uid IS NULL;
+------------+
| COUNT(lid) |
+------------+
|          3 |
+------------+
1 row in set (0.00 sec)

然而，当我将这两个查询与一个自然连接放在一起时，我得到了笛卡尔积(3x6= 18)：

mysql> select COUNT(all_licenses.lid) as all_licenses_count, COUNT(unassigned.lid) as unassigned_count FROM license_assignments unassigned, license_assignments all_licenses WHERE unassigned.lid = 2 AND unassigned.uid IS NULL AND all_licenses.lid = 2;
+--------------------+------------------+
| all_licenses_count | unassigned_count |
+--------------------+------------------+
|                 18 |               18 |
+--------------------+------------------+
1 row in set (0.00 sec)

考虑到我只需要添加一个GROUP BY，我就这样做了，但这并没有帮助：

mysql> select COUNT(all_licenses.lid) as all_licenses_count, COUNT(unassigned.lid) as unassigned_count FROM license_assignments unassigned, license_assignments all_licenses WHERE unassigned.lid = 2 AND unassigned.uid IS NULL AND all_licenses.lid = 2 GROUP BY all_licenses.lid, unassigned.lid;
+--------------------+------------------+
| all_licenses_count | unassigned_count |
+--------------------+------------------+
|                 18 |               18 |
+--------------------+------------------+
1 row in set (0.00 sec)

然后我认为自然的连接会让我绊倒，所以我尝试了内在的连接：

mysql> select COUNT(all_licenses.lid) as all_licenses_count, COUNT(unassigned.lid) as unassigned_count FROM license_assignments unassigned INNER JOIN license_assignments all_licenses ON all_licenses.lid = unassigned.lid WHERE unassigned.uid IS NULL;
+--------------------+------------------+
| all_licenses_count | unassigned_count |
+--------------------+------------------+
|                 18 |               18 |
+--------------------+------------------+
1 row in set (0.00 sec)

我有什么不懂的？我希望执行一个查询，给出这个结果：

mysql> select COUNT( ... ;
+--------------------+------------------+
| all_licenses_count | unassigned_count |
+--------------------+------------------+
|                 6 |               3 |
+--------------------+------------------+
1 row in set (0.00 sec)

但我的套理论显然是生疏的。我该怎么办？

顺便说一下：

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.5.31-1~dotdeb.0 |
+-------------------+

Answer 1

您将得到一个笛卡尔积，因为其中一组有六行lid=2，另一组有三行lid=2，集合中的每一行都与另一组中的每一行相匹配。

这里的联接问题是，您需要确保set 1中的一行最多与set 2中的一行匹配.您需要一个唯一键上的连接谓词。

如果您绝对需要使用一个JOIN来获得这个结果集，那么这样做是可行的：

 SELECT COUNT(a.lid) AS all_licenses_count
      , COUNT(u.lid) AS unassigned_count
   FROM license_assignments a
   LEFT
   JOIN license_assignments u
     ON u.lid = a.lid
    AND u.delta = a.delta
    AND u.uid IS NULL
  WHERE a.lid = 2

注意，连接谓词在lid和delta上都是匹配的(在表定义中定义为唯一键)。因此，我们保证集合1 (a)中的一行最多匹配集2 (u)中的一行。

正如其他答案所指出的，使用像这样的连接并不是获得这个结果的最有效的方法。

返回等效结果的方法有几种，但最有效的方法通常是通过一次遍历表来选择“所有”行，然后使用一个执行条件测试的表达式来确定该行是否应该包含在另一个计数或和聚合中。

我会写这样的东西：

 SELECT SUM(1)             AS all_licenses_count
      , SUM(a.uid IS NULL) AS unassigned_count
   FROM license_assignments a
  WHERE a.lid = 2

Answer 2

查询比您想象的要简单得多:)

SELECT
  COUNT(*) all_licenses_count,
  COUNT(*) - COUNT(uid) unassigned_count
FROM license_assignments
WHERE lid = 2

COUNT(*)计数行，而COUNT(uid)计数具有uid不为空的行。

输出：

| ALL_LICENSES_COUNT | UNASSIGNED_COUNT |
|--------------------|------------------|
|                  6 |                3 |

小提琴这里.

Answer 3

正如上面提到的@Mike，您不需要只加入COUNT()ing。不知道你为什么需要加入，无论如何，如果你想要的话，你缺少加入条件。举个例子。

SELECT
  unassigned.lid, unassigned.delta
FROM
  license_assignments unassigned JOIN
  license_assignments all_licenses 
  ON unassigned.lid = all_licenses.lid AND unassigned.delta = all_licenses.delta
WHERE
  unassigned.lid = 2 
  AND unassigned.uid IS NULL 
  AND all_licenses.lid = 2
+-----+-------+
| lid | delta |
+-----+-------+
|   2 |     4 |
|   2 |     5 |
|   2 |     6 |
+-----+-------+
3 rows in set (0.00 sec)

如果您检查下面的查询，您可以找出问题所在。

SELECT uid, lid, delta 
FROM license_assignments all_licenses
WHERE lid = 2;
+------+-----+-------+
| uid  | lid | delta |
+------+-----+-------+
|    1 |   2 |     1 |
|    1 |   2 |     2 |
|    1 |   2 |     3 |
| NULL |   2 |     4 |
| NULL |   2 |     5 |
| NULL |   2 |     6 |
+------+-----+-------+
6 rows in set (0.00 sec)

SELECT uid, lid, delta 
FROM license_assignments all_licenses
WHERE lid = 2 AND uid IS NULL;
+------+-----+-------+
| uid  | lid | delta |
+------+-----+-------+
| NULL |   2 |     4 |
| NULL |   2 |     5 |
| NULL |   2 |     6 |
+------+-----+-------+
3 rows in set (0.00 sec)