sql查询：查询药品的信息和被收藏的次数

一个药品表：有药品信息

一个收藏表：有一个外键指向药品表的主键id

通过sql实现查询药品的信息和被收藏的次数

用过一个左连接 + 分组，最后count(收藏表的药品id)，因为如果没有被收藏的话，左连接后的药品id为null，count时不会被统计

<select id="getDrug" resultType="drug">
    select d.d_id,d.d_img,d.d_price,d.d_count,count(c.c_did) collect_count
    from hospital.drug d
    left join hospital.collect c
    on d.d_id = c.c_did
    group by d.d_id;
</select>

mysql> select d.d_id,d.d_img,d.d_price,d.d_count,count(c.c_did)
    ->         from hospital.drug d
    ->         left join hospital.collect c
    ->         on d.d_id = c.c_did
    ->         group by d.d_id;
+------+----------------------------------+---------+---------+----------------+
| d_id | d_img                            | d_price | d_count | count(c.c_did) |
+------+----------------------------------+---------+---------+----------------+
|     1 | http://10.3.236.167/drug/1.jpg   |      24 |       0 |             0 |
|     2 | http://10.3.236.167/drug/2.jpg   |      19 |       0 |             1 |
|     3 | http://10.3.236.167/drug/3.jpg   |      18 |       0 |             0 |
|     4 | http://10.3.236.167/drug/4.jpg   |      26 |       0 |             0 |
|     5 | http://10.3.236.167/drug/5.jpg   |      17 |       0 |             0 |
|     6 | http://10.3.236.167/drug/6.jpg   |      27 |       0 |             0 |
|     7 | http://10.3.236.167/drug/7.jpg   |      22 |       0 |             0 |
|     8 | http://10.3.236.167/drug/8.jpg   |      45 |       0 |             0 |
|     9 | http://10.3.236.167/drug/9.jpg   |      53 |       0 |             0 |
|    10 | http://10.3.236.167/drug/10.jpg  |      27 |       0 |             0 |
|    11 | http://10.3.236.167/drug/11.jpg  |      32 |       0 |             0 |
|    12 | http://10.3.236.167/drug/12.jpg  |      35 |       0 |             0 |
|    13 | http://10.3.236.167/drug/13.jpg  |      34 |       0 |             0 |
|    14 | http://10.3.236.167/drug/14.jpg  |      26 |       0 |             0 |
|    15 | http://10.3.236.167/drug/15.jpg  |      28 |       0 |             0 |
|    16 | http://10.3.236.167/drug/16.jpg  |      17 |       0 |             0 |
|    17 | http://10.3.236.167/drug/17.jpg  |      59 |       0 |             0 |
|    18 | http://10.3.236.167/drug/18.jpg  |      53 |       0 |             0 |
|    19 | http://10.3.236.167/drug/19.jpg  |      34 |       0 |             0 |
|    20 | http://10.3.236.167/drug/20.jpg  |      39 |       0 |             0 |         +------+----------------------------------+---------+---------+----------------+