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

Question

MySQL 5.5.43

我正在处理一个包含 7,200 种大麻菌株的数据库，需要显示菌株列表以及它们的育种者声称的最受欢迎的物种。

这个主题很令人困惑，所以这里有一些事实可以帮助您理解我的困惑所在:

• 每种大麻菌株都是以下物种之一；籼稻、苜蓿或 Ruderalis，也可能是三者的杂交。
• 流行菌株可能有多达 30 个不同的育种者生产该菌株的种子。
• 这一品系的每个育种者可能具有略微不同的杂交/遗传学并报告不同的物种。例如:Breeder1 声称 StrainX 是 100% 籼稻，而 Breeder2 声称 StrainX 主要是籼稻(可能是 90% 籼稻和 10% 苜蓿)。显然，苜蓿植物的效果令人振奋，而籼稻则有点令人沮丧，因此出于药用目的记录每个品种的细微差异非常重要。

应变示例:

对于一种非常流行的菌株，称为白寡妇，这是我制作的结果集。它有 29 个不同的育种者，每个育种者都拥有不同的物种。正如您在结果中看到的那样，该品系最受欢迎的品种是籼稻/苜蓿(均等杂交种)。

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

我想要的结果

我想显示一个菌株名称列表，在每个菌株名称旁边我想显示一个育种者列表和所有育种者最流行/平均声称的物种。因此，正如我之前向您展示的那样，该品系育种者记录的最受欢迎的物种是 Indica/Sativa，并且希望这样显示:

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

HERE IS AN SQLFIDDLE - 由 Juan Carlos Oropeza 提供。

Answer 1

我将讨论您想从工作查询中汇总的知识。

我可能会以不同的方式执行此操作，但由于我不会更改您的工作查询，因此这可能会满足您的需求。子查询作为 GROUP_CONCAT 使事情变得有点困难，因为我们指望那个领域，我不能直接在那里计数(除非有人能告诉我更好的方法)然后我从中选择一个 MAX 或 AVG。您可以将 MAX 切换为 AVG。

SELECT MAX(aggregated.theCount),
aggregated.id,
aggregated.breederID,
aggregated.breeders as mostPopularBreeders,
aggregated.species as mostPopularSpecies,
AllStrainBreeders.allBreeders as strainBreeders
      FROM(
    SELECT 
        speciesWithBreeder.id,
        speciesWithBreeder.breederID,
        speciesWithBreeder.breederName,
        GROUP_CONCAT(DISTINCT speciesWithBreeder.breederName ORDER BY speciesWithBreeder.breederName ASC separator ', ') AS breeders,
        speciesWithBreeder.species,
        COUNT(*) as theCount
    FROM(
        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
        INNER JOIN species AS sp ON sbsd.speciesID = sp.id
        WHERE s.id = 6782
        GROUP BY s.id, sbd.breederID) 
    AS speciesWithBreeder
GROUP BY speciesWithBreeder.species
ORDER BY COUNT(*) DESC
  ) as aggregated 
  LEFT JOIN(
    SELECT 
    sbd.strainID,
    GROUP_CONCAT(DISTINCT b.breederName ORDER BY b.breederName ASC SEPARATOR ',') AS allBreeders
    FROM breeders b  
    LEFT JOIN strainBreedersDir sbd ON sbd.breederID = b.id AND sbd.strainID = 6782
    GROUP BY sbd.strainID
   ) as AllStrainBreeders      
   ON aggregated.id = AllStrainBreeders.strainID
GROUP BY aggregated.id