mysql - 我如何在子查询中分组或如何实现我的输出

Question

这是我的查询:

SELECT dte,((SELECT COUNT(prdt) as P1 FROM table
WHERE prdt LIKE '%P1%' AND stat= 'Done' AND dte  BETWEEN '08/01/2016' AND '09/07/2016') as P1,

(SELECT COUNT(prdt) as P2 FROM table
WHERE prdt LIKE '%P2%' AND stat= 'Done' AND dte  BETWEEN '08/01/2016' AND '09/07/2016') as P2,

(SELECT COUNT(prdt) as P3 FROM table
WHERE prdt LIKE '%P3%' AND stat= 'Done' AND dte  BETWEEN '08/01/2016' AND '09/07/2016') as P3

FROM table WHERE dte BETWEEN '08/01/2016' AND '09/07/2016' GROUP BY dte

表格

+-------------------+
|  dte1  |prdct|stat|
+--------+-----+----+
|8/1/2016|  P1 |Done|
+--------+-----+----+
|8/2/2016|  P2 |Done|
+--------+-----+----+
|8/3/2016|  P1 |Done|
+--------+-----+----+
|8/1/2016|  P3 |Done|
+--------+-----+----+

这是我想要的输出:

+--------+----+----+----+
|  Dte1  | P1 | P2 | P3 |
+--------+----+----+----+
|8/1/2016|  1 |NULL|  1 |
+--------+----+----+----+
|8/2/2016|NULL|  1 |NUll|
+--------+----+----+----+
|8/3/2016| 1  |NULL|NULL|
+--------+----+----+----+

Answer 1

为什么在 SELECT 列表中需要子查询？无需多次扫描表格。

dte BETWEEN 谓词中的文字看起来像是DATE 值。但对于 DATE 字面量而言，这些格式无效。如果 dte 列是字符串类型(CHAR 或 VARCHAR)并且您要将日期值存储在 dte 列中。 .. 你做错了。

考虑将 dte(字符串)值 '08/15/1999' 作为字符串进行比较的查询中发生的情况。例如，测试这个条件:

 SELECT '08/15/1999' BETWEEN '08/01/2016' AND '09/16/2016'

评估为 TRUE。 (就字符串比较而言，这是正确的。但如果我们将这些值视为日期，1999 年的日期将在 2016 年的两个日期之间是没有意义的。)

但将不稳定的日期格式问题放在一边，并解决您提出的问题...

---

只需使用条件聚合 编写一个查询。例如:

  SELECT t.dte
       , SUM( t.stat = 'Done' AND t.prdt LIKE '%P1%' ) AS P1
       , SUM( t.stat = 'Done' AND t.prdt LIKE '%P2%' ) AS P2
       , SUM( t.stat = 'Done' AND t.prdt LIKE '%P3%' ) AS P3
    FROM table t
   WHERE t.dte BETWEEN '2016-08-01' AND '2016-09-07'
   GROUP BY t.dte

该查询将返回“零”计数，这与规范不符。为了符合规范并返回 NULL 值代替零，我们可以使用方便的 NULLIF 函数将零计数替换为 NULL:

  SELECT t.dte
       , NULLIF(SUM( t.stat = 'Done' AND t.prdt LIKE '%P1%' ),0) AS P1
       , NULLIF(SUM( t.stat = 'Done' AND t.prdt LIKE '%P2%' ),0) AS P2
       , NULLIF(SUM( t.stat = 'Done' AND t.prdt LIKE '%P3%' ),0) AS P3
    FROM table t
   WHERE t.dte BETWEEN '2016-08-01' AND '2016-09-07'
   GROUP BY t.dte

要获得更符合 ANSI 标准的版本，请替换 MySQL 速记

   NULLIF(SUM( t.stat = 'Done' AND t.prdt LIKE '%P1%' ),0)

与

   SUM( CASE WHEN t.stat = 'Done' AND t.prdt LIKE '%P1%' THEN 1 END )

---

如果有一些(深不可测的)原因您需要在 SELECT 列表中使用子查询，您可以使用相关子查询。只需从子查询中的外部查询中引用 dte 的值即可。

SELECT t.dte
     , ( SELECT NULLIF(COUNT(*),0)
           FROM table t1
          WHERE t1.dte = t.dte
            AND t1.prdt LIKE '%P1%'
            AND t1.stat = 'Done'
       ) AS P1
     , ( SELECT NULLIF(COUNT(*),0)
           FROM table t2
          WHERE t2.dte = t.dte
            AND t2.prdt LIKE '%P2%'
            AND t2.stat = 'Done'
       ) AS P1
     , ( SELECT NULLIF(COUNT(*),0)
           FROM table t3
          WHERE t3.dte = t.dte
            AND t3.prdt LIKE '%P3%'
            AND t3.stat = 'Done'
       ) AS P3
    FROM table t
   WHERE t.dte BETWEEN '2016-08-01' AND '2016-09-07'
   GROUP BY t.dte

在 SELECT 列表中使用相关子查询通常比使用条件聚合模式更昂贵。这是因为子查询会针对外部查询返回的每一行执行。如果外部查询返回一千行，则子查询将执行一千次。