douji5397 2014-07-17 12:09
浏览 63
已采纳

用于对重复项进行分组和计数的SQL语句

I have a table called reports which looks a little like this:

|--|-------|------|-------------|-----------|------|
|ID|User_ID|Mod_ID|Mod_Timestamp|Mod_Comment|tstamp|
|--|-------|------|-------------|-----------|------|
|0 |   0   |   1  | 1387025342  |  XYZBLEH  |123123|
|--|-----------------------------------------------|

I also have another table called reports_users which looks like this:

|--|----|
|ID|Name|
|--|----|
|0 |Damo|
|--|----|

The user ID and Mod_Id from Report table both referance the ID from the Report_Users table, Now what i need is Sometimes inside the Mod_ID column there will be duplicates, What i would like to do is group these together and count them as sort of a tally of the duplicates so it looks a little something like this:

|--|----|-----|
|ID|Name|count|
|--|----|-----|
|0 |Damo|  16 |
|--|----|-----|

I can get the name to be added onto the table, but I cannot seem to group it or count them without causing problems, I've done this via a Inner Join statement but I cannot seem to get figure out how to do what I need. If anybody here could help with this I'd be most greatful.

Here is the SQL which I mentioned above

SELECT * FROM request as request_table INNER JOIN(SELECT name, id as uid from request_user)AS user ON(request_table.Mod_id = user.uid)

Regards, Damien

  • 写回答

1条回答 默认 最新

  • dongzanghong4379 2014-07-17 12:12
    关注

    No need at all for a subselect:

    SELECT reports.ID, reports_users.Name, count(reports.ID) AS count
    FROM reports
    LEFT JOIN reports_users ON reports.User_ID = reports.ID
    GROUP BY reports.ID
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
编辑
预览

报告相同问题?

手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部