t1表数据。 +------+------+------+------+ | id | name | dept | flag | +------+------+------+------+ | 1 | w | b | 0 | | 1 | w | b | 1 | | 2 | w | b | 1 | | 2 | w | c | 0 | | 2 | a | c | 0 | | 2 | a | e | 1 | | 2 | a | e | 1 | | 2 | w | c | 1 | +------+------+------+------+需要的结果是这样的: group by id, name . 获取 flag为0的个数f0num,flag为0的 distinct dept的个数f0disdeptnum, flag为1的个数f1num,flag为1的distinct dept的个数f1disdeptnum。谢谢
多谢提醒,详细的建表和结果信息在下边。 mysql数据库。create table t1(id int, name char(10), dept char(10), flag int); insert into t1 values(1, 'w', 'b', 0); insert into t1 values(1, 'w', 'b', 1); insert into t1 values(2, 'w', 'b', 1); insert into t1 values(2, 'w', 'c', 0); insert into t1 values(2, 'a', 'c', 0); insert into t1 values(2, 'a', 'e', 1); insert into t1 values(2, 'a', 'e', 1); insert into t1 values(2, 'w', 'c', 1); insert into t1 values(1, 'w', 'b', 0); insert into t1 values(1, 'w', 'e', 0);select * from t1; +------+------+------+------+ | id | name | dept | flag | +------+------+------+------+ | 1 | w | b | 0 | | 1 | w | b | 1 | | 2 | w | b | 1 | | 2 | w | c | 0 | | 2 | a | c | 0 | | 2 | a | e | 1 | | 2 | a | e | 1 | | 2 | w | c | 1 | | 1 | w | b | 0 | | 1 | w | e | 0 | +------+------+------+------+想要的结果: id name f0disdeptnum f0num f1disdeptnum f1num 1 w 2 3 1 1 2 w 1 1 2 2 2 a 1 1 1 2group by id, name . 获取 flag为0的个数f0num,flag为0的 distinct dept的个数f0disdeptnum, flag为1的个数f1num,flag为1的distinct dept的个数f1disdeptnum。
select B.id,B.name, (select count(distinct(dept)) from t1 A where A.id = B.id and A.name=B.name and A.flag=0) f0disdeptnum, sum(case when B.flag=0 then 1 else 0 end) f0num, (select count(distinct(dept)) from t1 A where A.id = B.id and A.name=B.name and A.flag=1) f1disdeptnum, sum(case when B.flag=1 then 1 else 0 end) f1num from t1 B group by B.id,B.name
t1表数据。
+------+------+------+------+
| id | name | dept | flag |
+------+------+------+------+
| 1 | w | b | 0 |
| 1 | w | b | 1 |
| 2 | w | b | 1 |
| 2 | w | c | 0 |
| 2 | a | c | 0 |
| 2 | a | e | 1 |
| 2 | a | e | 1 |
| 2 | w | c | 1 |
+------+------+------+------+需要的结果是这样的:
group by id, name . 获取 flag为0的个数f0num,flag为0的 distinct dept的个数f0disdeptnum, flag为1的个数f1num,flag为1的distinct dept的个数f1disdeptnum。谢谢
多谢提醒,详细的建表和结果信息在下边。 mysql数据库。create table t1(id int, name char(10), dept char(10), flag int);
insert into t1 values(1, 'w', 'b', 0);
insert into t1 values(1, 'w', 'b', 1);
insert into t1 values(2, 'w', 'b', 1);
insert into t1 values(2, 'w', 'c', 0);
insert into t1 values(2, 'a', 'c', 0);
insert into t1 values(2, 'a', 'e', 1);
insert into t1 values(2, 'a', 'e', 1);
insert into t1 values(2, 'w', 'c', 1);
insert into t1 values(1, 'w', 'b', 0);
insert into t1 values(1, 'w', 'e', 0);select * from t1;
+------+------+------+------+
| id | name | dept | flag |
+------+------+------+------+
| 1 | w | b | 0 |
| 1 | w | b | 1 |
| 2 | w | b | 1 |
| 2 | w | c | 0 |
| 2 | a | c | 0 |
| 2 | a | e | 1 |
| 2 | a | e | 1 |
| 2 | w | c | 1 |
| 1 | w | b | 0 |
| 1 | w | e | 0 |
+------+------+------+------+想要的结果:
id name f0disdeptnum f0num f1disdeptnum f1num
1 w 2 3 1 1
2 w 1 1 2 2
2 a 1 1 1 2group by id, name . 获取 flag为0的个数f0num,flag为0的 distinct dept的个数f0disdeptnum, flag为1的个数f1num,flag为1的distinct dept的个数f1disdeptnum。
(select count(distinct(dept)) from t1 A where A.id = B.id and A.name=B.name and A.flag=0) f0disdeptnum,
sum(case when B.flag=0 then 1 else 0 end) f0num,
(select count(distinct(dept)) from t1 A where A.id = B.id and A.name=B.name and A.flag=1) f1disdeptnum,
sum(case when B.flag=1 then 1 else 0 end) f1num from t1 B group by B.id,B.name