mySQL:基于不同条件的多个COUNT() [英] mySQL: Multiple COUNT()s based on differing criteria

问题描述

好的，所以我想做的是基于不同的条件在同一张表上执行不同的COUNT()，而没有嵌套查询(效率)或子查询(因此可以将其制成视图).这可能吗?

Alright, so what I'm trying to do is perform different COUNT()s on the same table based on different criteria without nested queries (efficiency) or subqueries (so it can be made into a view). Is this possible?

示例:表中有一个日期列-查询应该能够产生&之前的行数计数在一个固定的日期之后.

Example: a table has a date column - the query should be able to produce a count of the number of rows prior to & after a constant date.

推荐答案

您正在使用MySQL，因此您可以利用其功能，即布尔表达式的false值为0，为true的值为1. 1的SUM()等于表达式为true的COUNT().

You're using MySQL, so you can take advantage of its feature that a boolean expression evaluates to 0 for false and 1 for true. The SUM() of 1's is equal to a COUNT() where the expression is true.

SELECT SUM( date_column < '2009-08-21' ) AS prior_to_date,
       SUM( date_column > '2009-08-21' ) AS after_date
FROM MyTable;

PS:请不要在其他支持SQL标准行为的数据库品牌上尝试此操作，即布尔表达式会产生布尔值，而不是0或1的整数.

PS: Don't try this on other brands of database that uphold the SQL standard behavior, i.e. a boolean expression yields a boolean, not a 0 or 1 integer.

这篇关于mySQL:基于不同条件的多个COUNT()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！