sql - 如何在单个选择语句中使用不同条件进行多个 COUNT
问题描述
我在下面做了三个选择,每个都返回一个值。如何仅进行选择并返回三个值,每个值都有其条件?所有信息都在一张表中。
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';
您还可以避免提及 else 部分 case when 语句,因为默认值为 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';
推荐阅读
- linux - 为什么proc_read()是Linux中与/proc相关的函数,被“反复”调用,直到返回0?
- javascript - 尽管重新启动浏览器,ember-simple-auth 用户仍保持登录状态
- reactjs - VSCode - 更新文件名更改的导入路径?
- javascript - Vuetify - 观察 v-app-bar 的变化,例如 onScroll()
- apache-kafka - RocksDBException - Kafka 消费者没有可用的锁
- reactjs - 如何在chartjs中的多系列饼图中显示名称
- powershell - Powershell Remove-Item 与 cmd rd /s /q "path"
- magento2 - Magento 2 - 本地环境配置
- html - 搜索项的 flex 错误
- javascript - 将嵌套的 json 对象转换为 Multer 的 formdata javascript