mysql - 列出每条记录出现的次数 SQL

Question

我有两个表，我需要按每个租赁号码列出投诉数量。我的表格如下:

租赁表

RENTAL_NO   RENTAL_DA   R   LEA     LEASE_STA   LEASE_END   STAFF   APT_NO
100101          12-MAY-07   O   One     01-JUN-07   31-MAY-08   SA200   201
100102          21-MAY-07   O   Six     01-JUN-07   30-NOV-07   SA220   102
100103      12-OCT-07   O   Six     01-NOV-07   30-APR-08   SA240   203
100104      06-MAR-08   O   One     01-APR-08   31-MAR-09   SA210   101
100105      15-APR-08   O   One     01-MAY-08   30-APR-09   SA220   104
100106      15-JUL-08   S   One     01-AUG-08   31-JUL-09   SA200   100

投诉表

COMPLAINT_NO    COMPLAINT    RENTAL_COMPLAINT           APT_COMPLAINT  RENTAL_NO    APT_NO  S
10010           12-DEC-07    kitchen sink clogged       100103         203          F
10011           17-AUG-08    water heater not working   100105         104          F
10012           17-SEP-08    room heater problem        100105         104
10013           17-SEP-08    AC not working                            103
10014           20-OCT-08    car parking not proper     100103
10015           08-NOV-08    delay in payment           100104                      F
10016           16-NOV-08    utility not working                       202    

============================================= ====================================所以我需要这样的东西:

apt_no       Number of complaints
10000         3
10100         1
.
.
.

我还是个新手，我已经尝试了很多，但我似乎没有做对。任何帮助将不胜感激。

Answer 1

试试这个:

    select APT_COMPLAINT, count(complaint_no) as CComplaint
    from complaints
    group by APT_COMPLAINT