表b中的id2是表a的id2关联b
id2 fa
1 Y
2 N
3 Y
4 Yaid id2 name f
1 1 aa Y
2 1 bb Y
3 1 CC Y4 2 DD Y
5 2 DD Y
6 2 RR N7 3 ss Y
8 3 tt N9 4 ff N我要总计表a 条件是去除字段f=N和fa=N后 按f=Y字段条数多的在前 总计用mun表示结果如下num id2
3 1
1 3
按f字段多的在前
id2 fa
1 Y
2 N
3 Y
4 Yaid id2 name f
1 1 aa Y
2 1 bb Y
3 1 CC Y4 2 DD Y
5 2 DD Y
6 2 RR N7 3 ss Y
8 3 tt N9 4 ff N我要总计表a 条件是去除字段f=N和fa=N后 按f=Y字段条数多的在前 总计用mun表示结果如下num id2
3 1
1 3
按f字段多的在前
from a inner join b on a.id2=b.id2
where a.f!='N' and b.fa!='N'
group by a.id2
order by 1 desc
+------+------+------+------+
| id | id2 | name | f |
+------+------+------+------+
| 1 | 1 | aa | Y |
| 2 | 1 | bb | Y |
| 3 | 1 | CC | Y |
| 4 | 2 | DD | Y |
| 5 | 2 | DD | Y |
| 6 | 2 | RR | N |
| 7 | 3 | ss | Y |
| 8 | 3 | tt | N |
| 9 | 4 | ff | N |
+------+------+------+------+
9 rows in set (0.00 sec)mysql> select * from b;
+------+------+
| id2 | fa |
+------+------+
| 1 | Y |
| 2 | N |
| 3 | Y |
| 4 | Y |
+------+------+
4 rows in set (0.00 sec)mysql> select count(*) as num,a.id2
-> from a inner join b on a.id2=b.id2
-> where a.f!='N' and b.fa!='N'
-> group by a.id2
-> order by 1 desc;
+-----+------+
| num | id2 |
+-----+------+
| 3 | 1 |
| 1 | 3 |
+-----+------+
2 rows in set (0.00 sec)mysql>
-> from a inner join b using(id2)
-> where a.f!='N' and b.fa!='N'
-> group by id2
-> order by 1 desc;
+-----+------+
| num | id2 |
+-----+------+
| 3 | 1 |
| 1 | 3 |
+-----+------+
2 rows in set (0.00 sec)mysql> select count(*) as num,a.id2
-> from a , b
-> where a.id2=b.id2 and a.f!='N' and b.fa!='N'
-> group by a.id2
-> order by 1 desc;
+-----+------+
| num | id2 |
+-----+------+
| 3 | 1 |
| 1 | 3 |
+-----+------+
2 rows in set (0.00 sec)mysql>