如何使用MySQL分组和计数 [英] how to group by and count using MySQL

问题描述

我有这样的数据：

 
 ID post_author post_title guid 
 3309 21如果有人还没有在SQL 2008等待SQL 2008 R2，因为它是近的发布？ http://sql.stackexchange.com/questions/379/should-somebody-not-yet-on-sql-2008-wait-for-sql-2008-r2-since-its-near-release 
 1695 429我们如何礼貌地拒绝好意思，从祖母的意见？ http://moms4mom.stackexchange.com/questions/1208/how-do-we-politely-decline-well-meaning-advice-from-the-grandmother 
 556 173关于如何成为一个伟大的爸爸http ：//moms4mom.stackexchange.com/questions/1042/books-on-how-to-be-a-great-dad 
 160 30建立一个冰球网cam http://photo.stackexchange.com/ question / 8 / building-an-ice-hockey-net-cam 
 159 30通用商业照片发布表格http://photo.stackexchange.com/questions/4/generic-commercial-photo-release-form 
 

我需要创建一个查询，将GUID字段（根URL）的一部分上的数据分组，并计算每个的POST_AUTHOR。 / p>

我要查找的结果将是这样：

 
作者
 http://sql.stackexchange.com 1 
 http://moms4mom.stackexchange.com 2 
 http://photo.stackexchange.com 2 
 
解决方案
 
如果有人帮我构建sql， p>编写SQL FUNCTION  - 调用它，例如，guid_extract（guid），它提取相关信息，然后可以将其添加到select :: 
  SELECT stuff，otherstuff，guid_extract（guid）as site 
 ... 
 GROUP BY网站; 
  
 
I have data which looks like this:
ID  post_author post_title  guid
3309    21  Should somebody not yet on SQL 2008 wait for SQL 2008 R2, since it's near release?  http://sql.stackexchange.com/questions/379/should-somebody-not-yet-on-sql-2008-wait-for-sql-2008-r2-since-its-near-release
1695    429 How do we politely decline well meaning advice from the Grandmother?    http://moms4mom.stackexchange.com/questions/1208/how-do-we-politely-decline-well-meaning-advice-from-the-grandmother
556 173 Books on how to be a great dad  http://moms4mom.stackexchange.com/questions/1042/books-on-how-to-be-a-great-dad
160 30  Building an ice hockey net cam  http://photo.stackexchange.com/questions/8/building-an-ice-hockey-net-cam
159 30  Generic commercial photo release form   http://photo.stackexchange.com/questions/4/generic-commercial-photo-release-form
I need to create a query that groups the data on part of the GUID field (the root URL) and counts the POST_AUTHOR for each.

The result I am looking for would be like this:
Site    Count of Authors
http://sql.stackexchange.com    1
http://moms4mom.stackexchange.com   2
http://photo.stackexchange.com  2
I would be grateful if someone help me construct the sql.
解决方案
Write a SQL FUNCTION - call it for instance, guid_extract(guid), which extracts the pertinent info, then you can add it to a column in your select::
SELECT stuff, otherstuff, guid_extract(guid) as site
  ...
  GROUP BY site;


                        
这篇关于如何使用MySQL分组和计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！