select name,count(*) from table group by name; //按NMAE分组,查询STATES个数 select name,states,count(*) from table group by name,states; //按NMAE、STATES分组,查询STATES个数
想实现这种select name,count(states=0) c0,count(states>1) c1 from kk groupby name,语句肯定不对,求解
SELECT aa.name, count(*) cb FROM kk aaleft JOIN ( SELECT name, count(*) cx FROM kk WHERE school_id=23 and ( states = 1 OR states = 3 OR states = 4 ) GROUP BY name
) bb ON aa.name = bb.name WHERE school_id=23 and ( states = 0 OR states = 2 OR states = 5 OR states = 6 ) GROUP BY name 用这种方法显示不出left join的数据,不知道为什么
mysql> create table tt (name varchar(20),states int); Query OK, 0 rows affected (0.27 sec)mysql> insert into tt select '张三',1; Query OK, 1 row affected (0.24 sec) Records: 1 Duplicates: 0 Warnings: 0mysql> insert into tt select '张三',0; Query OK, 1 row affected (0.13 sec) Records: 1 Duplicates: 0 Warnings: 0mysql> insert into tt select '李四',0; Query OK, 1 row affected (0.13 sec) Records: 1 Duplicates: 0 Warnings: 0mysql> insert into tt select '李四',0; Query OK, 1 row affected (0.07 sec) Records: 1 Duplicates: 0 Warnings: 0mysql> insert into tt select '张三',2; Query OK, 1 row affected (0.34 sec) Records: 1 Duplicates: 0 Warnings: 0mysql> insert into tt select '李四',2; Query OK, 1 row affected (0.46 sec) Records: 1 Duplicates: 0 Warnings: 0SELECT NAME, sum( CASE WHEN states = 1 THEN 1 ELSE 0 END ), sum( CASE WHEN states > 1 THEN 1 ELSE 0 END ) FROM tt GROUP BY NAME
select name,states,count(*) from table group by name,states; //按NMAE、STATES分组,查询STATES个数
SELECT
aa.name,
count(*) cb
FROM
kk aaleft JOIN (
SELECT
name,
count(*) cx
FROM
kk
WHERE
school_id=23 and
(
states = 1
OR states = 3
OR states = 4
)
GROUP BY
name
) bb ON aa.name = bb.name
WHERE
school_id=23 and
(
states = 0
OR states = 2
OR states = 5
OR states = 6
)
GROUP BY
name
用这种方法显示不出left join的数据,不知道为什么
Query OK, 0 rows affected (0.27 sec)mysql> insert into tt select '张三',1;
Query OK, 1 row affected (0.24 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> insert into tt select '张三',0;
Query OK, 1 row affected (0.13 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> insert into tt select '李四',0;
Query OK, 1 row affected (0.13 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> insert into tt select '李四',0;
Query OK, 1 row affected (0.07 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> insert into tt select '张三',2;
Query OK, 1 row affected (0.34 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> insert into tt select '李四',2;
Query OK, 1 row affected (0.46 sec)
Records: 1 Duplicates: 0 Warnings: 0SELECT NAME,
sum( CASE WHEN states = 1 THEN 1 ELSE 0 END ),
sum( CASE WHEN states > 1 THEN 1 ELSE 0 END )
FROM
tt
GROUP BY
NAME