主表如何統(tǒng)計(jì)在附表中的出現(xiàn)次數(shù)？

建立主表：

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

建立附表:

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

分別插入數(shù)據(jù):

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，對(duì)應(yīng)的在a2表中出現(xiàn)了多少次!!

posted on 2014-07-12 22:42?一生學(xué)習(xí),何來畢業(yè) 閱讀(...) 評(píng)論(...) 編輯 收藏

轉(zhuǎn)載于:https://www.cnblogs.com/shibazi/p/3840542.html

總結(jié)

以上是生活随笔為你收集整理的主表如何统计在附表中的出现次数？的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。