请教一个sql的写法（本地无法测试，所以上来问问）

Question

本帖最后由 lifeixie 于 2011-01-06 17:41:47 编辑

Answer 1

SELECT A.number ,COUNT(B.*) FROM 
fonoirs_changenumber A INNER JOIN cdr B ON INSTR(B.userfield,A.number)>0 GROUP BY A.number

Answer 2

SELECT A.number ,IFNULL(COUNT(B.*),0) FROM  
fonoirs_changenumber A LEFT JOIN cdr B ON INSTR(B.userfield,A.number)>0 GROUP BY A.number

Answer 3

SELECT A.number,COUNT(B.userfield) FROM   
fonoirs_changenumber A LEFT JOIN cdr B ON INSTR(B.userfield,A.number)>0 
GROUP BY A.number

Answer 4

mysql> select * from cdr;
+-----------+
| userfield |
+-----------+
| aaa-1333  |
| aaa-1333  |
| aaa-1333  |
| aaa-1444  |
| aaa-1444  |
| aaa-1555  |
| aaa-1666  |
| aaa-1666  |
+-----------+
8 rows in set (0.00 sec)mysql> select * from fonoirs_changenumber;
+--------+
| number |
+--------+
|   1333 |
|   1444 |
|   1555 |
|   1666 |
|   1777 |
+--------+
5 rows in set (0.00 sec)mysql> select number,sum(if(locate(f.number,c.userfield)>0,1,0)) from fonoirs_ch
angenumber f left join cdr c
    -> on locate(f.number,c.userfield) >0
    -> group by number;
+--------+---------------------------------------------+
| number | sum(if(locate(f.number,c.userfield)>0,1,0)) |
+--------+---------------------------------------------+
|   1333 |                                           3 |
|   1444 |                                           2 |
|   1555 |                                           1 |
|   1666 |                                           2 |
|   1777 |                                           0 |
+--------+---------------------------------------------+
5 rows in set (0.00 sec)mysql> select number,sum(if(locate(f.number,c.userfield)>0,1,0)) as cunt from fonoirs_changenumber f left join cdr c
    -> on locate(f.number,c.userfield) >0
    -> group by number;
+--------+------+
| number | cunt |
+--------+------+
|   1333 |    3 |
|   1444 |    2 |
|   1555 |    1 |
|   1666 |    2 |
|   1777 |    0 |
+--------+------+
5 rows in set (0.00 sec)

Answer 5

mysql> SELECT A.number,COUNT(B.userfield) FROM
    -> fonoirs_changenumber A LEFT JOIN cdr B ON INSTR(B.userfield,A.number)>0    -> GROUP BY A.number;
+--------+--------------------+
| number | COUNT(B.userfield) |
+--------+--------------------+
| 1333   |                  3 |
| 1444   |                  2 |
| 1555   |                  1 |
| 1666   |                  2 |
| 1777   |                  0 |
+--------+--------------------+
5 rows in set (0.00 sec)