首页 > 代码库 > 主表如何统计在附表中的出现次数?

主表如何统计在附表中的出现次数?

建立主表:

create table a1 (id int ,name char(20));

建立附表:

create table a2 (tid int ,name char(20));

分别插入数据:

mysql> insert into a1 values (1,baidu);Query OK, 1 row affected (0.00 sec)mysql> insert into a1 values (2,qq);Query OK, 1 row affected (0.00 sec)mysql> insert into a1 values (3,sina);Query OK, 1 row affected (0.02 sec)mysql> insert into a2 values (1,000);Query OK, 1 row affected (0.00 sec)mysql> insert into a2 values (1,001);Query OK, 1 row affected (0.00 sec)mysql> insert into a2 values (1,002);Query OK, 1 row affected (0.00 sec)mysql> insert into a2 values (1,003);Query OK, 1 row affected (0.00 sec)mysql> insert into a2 values (2,200);Query OK, 1 row affected (0.00 sec)mysql> insert into a2 values (2,201);Query OK, 1 row affected (0.00 sec)mysql> insert into a2 values (2,202);Query OK, 1 row affected (0.00 sec)mysql> insert into a2 values (2,203);Query OK, 1 row affected (0.00 sec)mysql> insert into a2 values (2,204);Query OK, 1 row affected (0.00 sec)mysql> insert into a2 values (2,205);Query OK, 1 row affected (0.01 sec)mysql> insert into a2 values (3,300);Query OK, 1 row affected (0.01 sec)

sql语句:

select a.*,(select count(1) from a2 as b where b.tid=a.id) from a1 as a;

这样就能查询a1中所有的id,对应的在a2表中出现了多少次!!