Database kk
The name states
Zhang SAN 1
Zhang SAN 0
Li si 0
Li si 0
Zhang SAN 2
.
The number of grouped by the name query states

CodePudding user response:

Select the name, the count (*) from table group by name;//by grouping NMAE, query the number of STATES
Select the name, states, count (*) from table group by name, states;//by NMAE, STATES grouping, query the number of STATES

CodePudding user response:

To achieve this select name, count (states=0) c0, count (states> 1) c1 from kk groupby name, statements must be wrong, to solve

CodePudding user response:

 
SELECT 
Aa. The name, 
Count (*) cb 
The FROM 
Kk aa 

Left the JOIN (
SELECT 
The name, 
Count (*) cx 
The FROM 
Kk 
WHERE 
23 and school_id=
(
States=1 
The OR states=3 
The OR states=4 
) 
GROUP BY 
The name 

) bb ON aa. Name=bb. Name 
WHERE 
23 and school_id=
(
States=0 
The OR states=2 
The OR states=5 
The OR states=6 
) 
GROUP BY 
The name 

Using this method showed that left the join data, do not know why

CodePudding user response:

Mysql> The create table tt (name varchar (20), states int);
Query OK, 0 rows affected (0.27 SEC)

Mysql> Insert into the select 'zhang' tt, 1;
Query OK, 1 row affected (0.24 SEC)
Records: 1 Duplicates: 0 Warnings: 0

Mysql> Insert into the select 'zhang' tt, 0;
Query OK, 1 row affected (0.13 SEC)
Records: 1 Duplicates: 0 Warnings: 0

Mysql> Insert into tt select 'bill', 0;
Query OK, 1 row affected (0.13 SEC)
Records: 1 Duplicates: 0 Warnings: 0

Mysql> Insert into tt select 'bill', 0;
Query OK, 1 row affected (0.07 SEC)
Records: 1 Duplicates: 0 Warnings: 0

Mysql> Insert into the select 'zhang' tt, 2;
Query OK, 1 row affected (0.34 SEC)
Records: 1 Duplicates: 0 Warnings: 0

Mysql> Insert into tt select 'bill', 2;
Query OK, 1 row affected (0.46 SEC)
Records: 1 Duplicates: 0 Warnings: 0

SELECT the NAME,
The sum (CASE WHEN states=1 THEN 1 ELSE 0 END),
The sum (CASE WHEN states & gt; 1 THEN 1 ELSE 0 END)
The FROM
Tt
GROUP BY
NAME

CodePudding user response:

The upstairs, is I need,