如何在select语句中使用不同条件进行多个COUNT

html5 • • 问答

我在下面做了三个选择，每个选择都返回一个值。如何仅选择并返回三个值，每个值都有其条件？所有信息都在一张表中。

SELECT COUNT (GRA_HRFIM) as "Conferidos antes 17h" FROM GRADE WHERE GRA_HRFIM < '17:00:00' AND GRA_DATADIGITA = '04/28/2021'

SELECT COUNT (GRA_HORADIGITA) as "Importados antes 17h" FROM GRADE WHERE GRA_HORADIGITA < '17:00:00' AND GRA_DATADIGITA = '04/28/2021'

SELECT COUNT (GRA_HRFIM) as "Total pedidos" FROM GRADE WHERE GRA_DATADIGITA = '04/28/2021'

回答

GRA_DATADIGITA = '04/28/2021'所有计数的条件都相同，因此在 where 子句中使用它。现在您只需选择即可获得第三次计数，COUNT (GRA_HRFIM)但对于第一次和第二次计数，您需要使用case when语句来选择条件计数。

SELECT COUNT(case when GRA_HRFIM < '17:00:00' then GRA_HRFIM else null end)
         AS "Conferidos antes 17h",
       COUNT(case when GRA_HORADIGITA < '17:00:00' then GRA_HORADIGITA else null end)
         AS  "Importados antes 17h",
       COUNT(GRA_HRFIM)
         AS "Total pedidos"
  FROM GRADE
 WHERE GRA_DATADIGITA = '04/28/2021';

您还可以避免在语句中提及 case 的 else 部分，因为默认值为 null。

SELECT COUNT(case when GRA_HRFIM < '17:00:00' then GRA_HRFIM end)
             AS "Conferidos antes 17h",
           COUNT(case when GRA_HORADIGITA < '17:00:00' then GRA_HORADIGITA end)
             AS  "Importados antes 17h",
           COUNT(GRA_HRFIM)
             AS "Total pedidos"
      FROM GRADE
     WHERE GRA_DATADIGITA = '04/28/2021';

  • You can leave off the `else null`, as that is the default.
  • If to shorten the query text yet further then there can be one more optimization. Since we do usual `count( ...` rather than `count(distinct ...` the specific values do not matter and only nullness does. So query like `count(case when GRA_HRFIM < '17:00:00' then 1 end)` would work just the same, too 🙂
以上是如何在select语句中使用不同条件进行多个COUNT的全部内容。
THE END
分享
二维码
< <上一篇
下一篇>>