資料庫kk
name states
張三 1
張三 0
李四 0
李四 0
張三 2
....
按name分組查詢states的個數
uj5u.com熱心網友回復：
select name,count(*) from table group by name; //按NMAE分組，查詢STATES個數select name,states,count(*) from table group by name,states; //按NMAE、STATES分組，查詢STATES個數
uj5u.com熱心網友回復：
想實作這種select name,count(states=0) c0,count(states>1) c1 from kk groupby name，陳述句肯定不對，求解uj5u.com熱心網友回復：
SELECT
aa.name,
count(*) cb
FROM
kk aa
left 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的資料，不知道為什么
uj5u.com熱心網友回復：
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: 0
mysql> insert into tt select '張三',0;
Query OK, 1 row affected (0.13 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into tt select '李四',0;
Query OK, 1 row affected (0.13 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into tt select '李四',0;
Query OK, 1 row affected (0.07 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into tt select '張三',2;
Query OK, 1 row affected (0.34 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into tt select '李四',2;
Query OK, 1 row affected (0.46 sec)
Records: 1 Duplicates: 0 Warnings: 0
SELECT 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
uj5u.com熱心網友回復：
樓上的，是樓主需要的吧。轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/75205.html
標籤：MySQL
上一篇：求個高效SQL陳述句！