MySQL GROUP BY在一个或两个列的子查询中，在其解释之外有一个WHERE子句

Question

今天，在SQL暂停了一段时间后，我遇到了以下查询：

有一个订单表：

CREATE TABLE `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_type` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'C',
  `order_number` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `postal_code` char(5) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

每个order都有：

• 唯一的CCD_ 2
• order_type可以是C、S、R，RS、RR、RC（它们的意思并不重要，它们是仅由系统内部使用）
• order_number，它是一个代码代表受订单约束的账单（请注意更多的订单可能绑定到同一个bill_number，我知道INT列会更好，但不是我创建了这个表）
• 和CCD_ 12，它是我的国家（与bill_number相同，INT列会更好，I知道）

此外，我有一个postal_codes表：

CREATE TABLE `postal_codes` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `postal_code` char(5) COLLATE utf8_unicode_ci NOT NULL,     
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

现在，我需要做的是：

给定一个5个字符的邮政编码，找到其id以及绑定到类型为S、C或R 的订单的票据计数

无论如何，这就是我提出的查询（对于postal_code->48890）：

SELECT b.id, COUNT(*) count
FROM (
   SELECT c.id, c.postal_code
   FROM (
      SELECT o.`postal_code` FROM `orders` o
      WHERE o.`order_type` IN ('S', 'C', 'R')
      AND o.`postal_code` IS NOT NULL AND o.`postal_code` NOT LIKE ''
      GROUP BY o.`order_number`, o.`postal_code`
   ) a
   JOIN `postal_codes` c
       ON a.`postal_code` = c.`postal_code`
   WHERE c.`postal_code` = "48890"
) b
GROUP BY b.id

这是一个Fiddle：http://sqlfiddle.com/#！9/3年8/4年

现在，它的工作原理与预期的一样，唯一需要注意的是，最内部的id0：我必须先按o.order_number分组，然后按o.postal_code分组，因为我在写上面的查询之前写了以下查询：

SELECT b.id, COUNT(*) count
    FROM (
       SELECT c.id, c.postal_code
       FROM (
          SELECT o.`postal_code` FROM `orders` o
          WHERE o.`order_type` IN ('S', 'C', 'R')
          AND o.`postal_code` IS NOT NULL AND o.`postal_code` NOT LIKE ''
          GROUP BY o.`order_number`
       ) a
       JOIN `postal_codes` c
           ON a.`postal_code` = c.`postal_code`
       WHERE c.`postal_code` = "48890"
    ) b
    GROUP BY b.id

有时没有按预期工作（我说sometimes是因为现在它似乎与我在小提琴中写的花哨的Schema一起工作，但有时会返回错误的COUNT(*)，而不是上面查询返回的COUNT(*)）

有人能告诉我这两个查询之间的区别吗？（第一个查询在最内部的子查询中有两个GROUP BY列，似乎总是对我有效，而第二个查询只有一个GROUP_BY列，有时有效（在这种情况下，如果我能找出原因，我会更新这个问题），有时无效，并返回错误的COUNT(*)）？为什么在GROUP BY中使用两列总是确保返回正确的最终COUNT(*)，而使用一列却不能？

我有点困惑。

感谢您的关注。

更新：我发布的Fiddle似乎不起作用，无论如何，以下是我使用的示例模式的SQL代码：

-- SQL Fiddle Build Schema
    CREATE TABLE `orders` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `order_type` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'C',
      `order_number` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
      `postal_code` char(5) COLLATE utf8_unicode_ci DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `postal_codes` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `postal_code` char(5) COLLATE utf8_unicode_ci NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO orders (order_type, order_number, postal_code) VALUES ("S", "192010NJDOS", "48890");
INSERT INTO orders (order_type, order_number, postal_code) VALUES ("C", "192010NJDOS", "48890");
INSERT INTO orders (order_type, order_number, postal_code) VALUES ("R", "192010NJDOS", "48890");
INSERT INTO orders (order_type, order_number, postal_code) VALUES ("S", "129019302KS", "48890");
INSERT INTO orders (order_type, order_number, postal_code) VALUES ("C", "129019302KS", "48890");
INSERT INTO orders (order_type, order_number, postal_code) VALUES ("C", "4444HHJSKAA", "48890");
INSERT INTO orders (order_type, order_number, postal_code) VALUES ("C", "4444HHJSKAA", "48890");
INSERT INTO orders (order_type, order_number, postal_code) VALUES ("S", "4444HHJSKAA", "48890");
INSERT INTO orders (order_type, order_number, postal_code) VALUES ("S", "4444HHJSKAA", "48890");
INSERT INTO orders (order_type, order_number, postal_code) VALUES ("S", "11111AAAAAA", "48890");
INSERT INTO orders (order_type, order_number, postal_code) VALUES ("C", "BBBBB222222", "48890");
INSERT INTO orders (order_type, order_number, postal_code) VALUES ("R", "FFFFFFFK933", "48890");
INSERT INTO orders (order_type, order_number, postal_code) VALUES ("RC", "777777SSSSS", "48890");
INSERT INTO orders (order_type, order_number, postal_code) VALUES ("RS", "777877SSSSS", "48890");
INSERT INTO orders (order_type, order_number, postal_code) VALUES ("RR", "779977SSSSS", "48890");
INSERT INTO orders (order_type, order_number, postal_code) VALUES ("S", "88818999999", "65889");
INSERT INTO orders (order_type, order_number, postal_code) VALUES ("C", "88818999999", "65889");
INSERT INTO orders (order_type, order_number, postal_code) VALUES ("R", "88818999999", "65889");
INSERT INTO orders (order_type, order_number, postal_code) VALUES ("S", "7777JJJJJJJ", "65889");
INSERT INTO orders (order_type, order_number, postal_code) VALUES ("C", "AJSJ8888LLL", "65889");
INSERT INTO orders (order_type, order_number, postal_code) VALUES ("RC", "AJSJ888899L", "65889");
INSERT INTO orders (order_type, order_number, postal_code) VALUES ("RC", "AJSJ888899L", "65889");
INSERT INTO orders (order_type, order_number, postal_code) VALUES ("RS", "AJSJ888899L", "65889");
INSERT INTO orders (order_type, order_number, postal_code) VALUES ("RR", "AJS7788899L", "65889");
INSERT INTO orders (order_type, order_number, postal_code) VALUES ("RR", "AMMMM88899L", "65889");
INSERT INTO orders (order_type, order_number, postal_code) VALUES ("S", "00001020202", "12033");
INSERT INTO orders (order_type, order_number, postal_code) VALUES ("C", "00001020202", "12033");
INSERT INTO orders (order_type, order_number, postal_code) VALUES ("R", "KKKKKKKKSK2", "12033");
INSERT INTO orders (order_type, order_number, postal_code) VALUES ("S", "KKKKKKKKSK2", "12033");
INSERT INTO orders (order_type, order_number, postal_code) VALUES ("C", "KKKKKKKKSK2", "12033");
INSERT INTO orders (order_type, order_number, postal_code) VALUES ("RC", "DKSKOKDOKOK", "12033");
INSERT INTO orders (order_type, order_number, postal_code) VALUES ("RS", "KKKKKKKKSK2", "12033");
INSERT INTO orders (order_type, order_number, postal_code) VALUES ("RR", "KKKKKK99999", "12033");
-- ---
INSERT INTO postal_codes (postal_code) VALUES ("48890");
INSERT INTO postal_codes (postal_code) VALUES ("65889");
INSERT INTO postal_codes (postal_code) VALUES ("12033");
-- SQL Fiddle 
 SELECT b.id, COUNT(*) count
        FROM (
           SELECT c.id, c.postal_code
           FROM (
              SELECT o.`postal_code` FROM `orders` o
              WHERE o.`order_type` IN ('S', 'C', 'R')
              AND o.`postal_code` IS NOT NULL AND o.`postal_code` NOT LIKE ''
              GROUP BY o.`order_number`
           ) a
           JOIN `postal_codes` c
               ON a.`postal_code` = c.`postal_code`
           WHERE c.`postal_code` = "48890"
        ) b
        GROUP BY b.id

Answer 1

您的查询似乎有点复杂。这里有一种表达方式：

  SELECT p.postal_code, p.id, count(*)
  FROM `orders` o JOIN
       postalcodes p
       ON o.postal_code = p.postal_code
  WHERE o.`order_type` IN ('S', 'C', 'R') AND
        p.postal_code = $Postal_Code
  GROUP BY p.postal_code, p.id;

请注意，严格来说，GROUP BY是不必要的，但如果您想要多个邮政编码的值，它是有用的。

另一方面，数据结构有点混乱。Orders中的邮政编码应该是主键id，而不是邮政编码。

编辑：

要获得零计数，您可以执行以下操作：

  SELECT p.postal_code, p.id, count(o.postal_code)
  FROM postalcodes p left join
       orders o
       ON o.postal_code = p.postal_code and
          o.order_type IN ('S', 'C', 'R')
  WHERE p.postal_code = $Postal_Code
  GROUP BY p.postal_code, p.id;