表中的所有行都有一个type
字段,其值为0或1。
我需要在一次查询中计算0和1的行数。因此,结果应该类似于:
type0 | type1
------+------
1234 | 4211
如何实现这一点?
select type, count(type) from tbl_table group by type;
Lessee...
SELECT
SUM(CASE type WHEN 0 THEN 1 ELSE 0 END) AS type0,
SUM(CASE type WHEN 1 THEN 1 ELSE 0 END) AS type1
FROM
tableX;
这个内容尚未经过测试。
type
列的方式。 - staticsan你可能想要使用标量操作的子查询:
SELECT (SELECT COUNT(*) FROM table WHERE type = 0) AS type0,
(SELECT COUNT(*) FROM table WHERE type = 1) AS type1;
在MySQL中测试如下:
CREATE TABLE t (id INT NOT NULL AUTO_INCREMENT, type INT);
INSERT INTO t VALUES (NULL, 0);
INSERT INTO t VALUES (NULL, 0);
INSERT INTO t VALUES (NULL, 1);
INSERT INTO t VALUES (NULL, 1);
INSERT INTO t VALUES (NULL, 1);
SELECT (SELECT COUNT(*) FROM t WHERE type = 0) AS type0,
(SELECT COUNT(*) FROM t WHERE type = 1) AS type1;
+-------+-------+
| type0 | type1 |
+-------+-------+
| 2 | 3 |
+-------+-------+
1 row in set (0.00 sec)
Type Count
-----------
type0 1234
type1 4221
你可以使用类似这样的代码:
SELECT CONCAT('type', [type]) as Type, COUNT(*) as Count
FROM MyTable
GROUP BY Type