MySQL
碰到一个sql问题
查询items表,统计color字段有多少种颜色,即统计某个字段不同值的个数
SELECT COUNT(color = 'red' OR NULL) AS 'red', COUNT(color = 'blue' OR NULL) AS 'blue' FROM items;
SELECT SUM(IF(color = 'red', 1, 0)) AS 'red', SUM(IF(color = 'blue', 1, 0)) AS 'blue' FROM items;
关于or、or null、count的介绍
or是短路逻辑或运算,当左边为true时,则不再继续运算右边,当左边为false时才继续运算右边;在mysql内部中true、false用1、0表示;ex***ull先计算exp如果为true则直接返回true(1),否则继续运算右边,而右边是null,遇到null直接返回null;count(exp|字段)函数统计指定表达式或者字段中非null记录的个数
SELECT TRUE, FALSE;
SELECT 1 OR NULL, 0 OR NULL, 1 AND NULL;
1 OR NULL | 0 OR NULL | 1 AND NULL |
1 | null | null |
SELECT COUNT(TRUE), COUNT(FALSE), COUNT(1), COUNT(0), COUNT(NULL);
COUNT(TRUE) | COUNT(FALSE) | COUNT(1) | COUNT(0) | COUNT(NULL) |
1 | 1 | 1 | 1 | 0 |
SELECT COUNT(TRUE OR NULL), COUNT(1 OR NULL), COUNT(FALSE OR NULL), COUNT(0 OR NULL), COUNT(NULL OR NULL);
COUNT(TRUE OR NULL) | COUNT(1 OR NULL) | COUNT(FALSE OR NULL) | COUNT(0 OR NULL) | COUNT(NULL OR NULL) |
1 | 1 | 0 | 0 | 0 |