MySQL对group_concat()产生的列中的公共数据进行计数 [英] MySQL count the common data in a column resulted from group_concat()

问题描述

我正在尝试查找某些作者之间共有的共同作者的数量-当作者的名字和姓氏相同且其中一位作者没有中间名时.

I am trying to find the number of coauthors who are common between certain authors - When the first name and the last name of the authors is the same and one of the authors has no middle name.

在这种情况下，我们希望找到没有中间名的作者和具有中间名的作者共同的共同作者的数量.

In such scenarios, we are interested to find the number of coauthors who are common to the author with no middle name with the authors with middle names.

例如，[link] http://www.sqlfiddle.com/#！ 9/75243/1

For example, [link] http://www.sqlfiddle.com/#!9/75243/1

在此表中，我们的作者具有多个中间名，但名字和姓氏相同，即JACK SMITH, JACK A SMITH, JACK B SMITH.我们有兴趣找到

In this table, we have authors with multiple middle names but the same first and last names i.e, JACK SMITH, JACK A SMITH, JACK B SMITH. We are interested to find the number of coauthors common to

1. JACK SMITH with JACK A SMITH
2. JACK SMITH with JACK B SMITH

结果将包括num字段结果作为

The result would include num field result as

JACK A SMITH  1
JACK B SMITH  0

因为JACK A SMITH与JACK SMITH有一个共同作者，而JACK B SMITH与JACK SMITH没有共同的作者.

since JACK A SMITH has one coauthor in common with JACK SMITH and JACK B SMITH has no coauthors in common with JACK SMITH.

推荐答案

您可以将COUNT(DISTINCT ...)用于多列.因此，您可以使用它来计算不同的全名，而不必先将它们连接起来.

You can use COUNT(DISTINCT ...) with multiple columns. So you can use this to count the different full names without actually having to concatenate them first.

SELECT s1.fname, s1.mname, s1.lname, s1.name, COUNT(DISTINCT s2.fname, s2.mname, s2.lname) as num
FROM ( SELECT title, fname, mname, lname, CONCAT(fname, ' ',mname, ' ', lname) as name
  FROM sample ) as s1
LEFT JOIN sample AS s2
ON s1.title = s2.title AND s1.fname = s2.fname AND s1.lname = s2.lname AND s1.mname != s2.mname 
WHERE s1.mname != ''
GROUP BY s1.name
ORDER BY s1.lname, s1.fname, s1.mname 

我使用LEFT JOIN而不是JOIN来获得合著者为零的行.

I used a LEFT JOIN rather than JOIN in order to get the rows with zero coauthors.

这篇关于MySQL对group_concat()产生的列中的公共数据进行计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！