外连接子查询最常见的植物物种

MySQL 5.5.43

我正在研究7200种大麻菌株的数据库，需要显示菌株列表以及其育种者声称的最受欢迎的物种。

该主题非常令人困惑，因此以下一些事实将帮助您了解我的困惑所在：

• 每个大麻株是以下物种之一；，苜蓿或Ruderalis，也可以是三者的杂交。
• 流行菌株可能有多达30个不同的育种者生产这种菌株的种子。
• 该菌株的每个育种者的杂交/遗传可能略有不同，并且报告的物种也不同。例如：Breeder1声称StrainX是100％的d稻，而Breeder2声称StrainX是大部分的d（可能是90％In和10％苜蓿）。显然，苜蓿植物的效果令人振奋，而In稻的效果却有些低落，因此记录每个品种的细微差异以达到药用目的非常重要。

示例应变：

对于一种非常受欢迎的菌株White Widow，这是我产生的结果集。它有29个不同的育种者，每个育种者声称拥有不同的物种。从结果中可以看到，此菌株最流行的物种是In /苜蓿（同等杂种）。

SELECT
  s.id,
  b.id AS breederID,
  b.breederName AS breederName,
  GROUP_CONCAT(DISTINCT sp.species ORDER BY sp.species ASC SEPARATOR '/') AS species
FROM strains AS s
LEFT JOIN strainBreedersDir AS sbd ON s.id = sbd.strainID
LEFT JOIN breeders AS b ON sbd.breederID = b.id
LEFT JOIN strainBreederSpeciesDir AS sbsd ON s.id = sbsd.strainID AND sbd.breederID = sbsd.breederID
LEFT JOIN species AS sp ON sbsd.speciesID = sp.id
WHERE s.id = 6782
GROUP BY s.id, sbd.breederID

我想要的结果

我想显示一个品系名称列表，然后在每个名称旁边显示一个育种者列表，以及所有育种者最受欢迎的/平均声称的物种。因此，正如我之前向您展示的，育种者为此菌株记录的最受欢迎的物种是印度In /苜蓿，并希望这样显示：

strainID  | strainName      | breeders                 | averageSpecies
--------------------------------------------------------------------------
6782      | White Widow     | Green House Seeds,       | Indica/Sativa
          |                 | Barney's Farm

我尝试过的是：

我没有在每个物种旁边显示最受欢迎的物种，而是在每个物种旁边显示了记录物种的第一个实例。我以为这样就足够了，但是一个物种的第一个实例可能是空的，因为目前大约有100个未知物种的菌株。因此，我不希望某个物种的第一个实例是“未知的”，因为实际上该菌株的其他育种者知道其中的物种。因此，我认为最好是确定记录最多的物种并显示出来。到目前为止，这是我必须去的地方：

SELECT
  s.id,
  s.strainName,
  GROUP_CONCAT(DISTINCT b.breederName ORDER BY b.breederName ASC separator ', ') AS breeders,
  COALESCE(NULLIF(ps.primarySpecies,''),'Unknown') AS primarySpecies
FROM strains AS s
LEFT JOIN strainBreedersDir AS sbd ON s.id = sbd.strainID
LEFT JOIN breeders AS b ON sbd.breederID = b.id
LEFT OUTER JOIN (
  SELECT
    sbd.breederID AS breederID,
    GROUP_CONCAT(DISTINCT sp.species ORDER BY sp.species ASC SEPARATOR '/') AS primarySpecies
  FROM strains AS s
  LEFT JOIN strainBreedersDir AS sbd ON s.id = sbd.strainID
  LEFT JOIN strainBreederSpeciesDir AS sbsd ON s.id = sbsd.strainID AND sbd.breederID = sbsd.breederID
  LEFT JOIN species AS sp ON sbsd.speciesID = sp.id
  GROUP BY s.id, sbd.breederID
) AS ps ON sbd.breederID = ps.breederID
WHERE s.id = 6782
GROUP BY s.id

结果

id   | strainName   | breeders           | species
----------------------------------------------------------
6782 | White Widow  | Green House Seeds, | Indica/Sativa
     |              | Barney's Farm      |

但是我无法弄清楚如何修改OUTER JOIN来显示最受欢迎的物种，而不仅仅是第一行。我尝试了许多不同的外部联接查询变体，但都失败了很多，而对所尝试的方法却一无所知。

如何显示最受欢迎的物种？

数据库结构：

strains

id (PK AUTO)     |  strainName (UNIQUE)
---------------------------------------------
6782             |  White Widow

-

strainBreedersDir

strainID (FK UNIQUE)     | breederID (UNIQUE)
---------------------------------------------
6782                     | 16
6782                     | 23

-

breeders

id (PK AUTO)      | breederName (UNIQUE)
---------------------------------------------
16                | Green House Seeds
23                | Barney's Farm

-

strainBreederSpeciesDir

strainID (FK UNIQUE)  | breederID (INT UNIQUE)  | speciesID (INT UNIQUE)
----------------------------------------------------------------------
6782                  | 16                      | 1
6782                  | 16                      | 2
6782                  | 23                      | 5

-

species

id (PK AUTO)  | species (UNIQUE)
-------------------------------------
1             | Indica
2             | Sativa
3             | Ruderalis
4             | Mostly Indica
5             | Mostly Sativa
6             | Mostly Ruderalis

这是一个SQLFIDDLE-由Juan Carlos Oropeza提供。