MySQL：按"group by"将搜索功能搜索到聚合列中

Question

我有3张桌子：成员，照片和标记。成员可以发布图片并对其他成员的照片进行排名。

我正在用PHP做一个脚本，它返回一张照片（使用random（）），可以由连接在其会员区的用户进行排名。因此，我进行了以下查询（我评论了显然包含问题的行）：

 SELECT ph.*, 
    COUNT(note) 'nb_note', 
    ROUND(DEGREES(ACOS((SIN(RADIANS( 48.86 )) * SIN (RADIANS( v.latitude_deg ))) + (COS(RADIANS( 48.86 )) * COS(RADIANS( v.latitude_deg )) * COS( RADIANS( 2.34445 - v.longitude_deg))))) * 111.13384) 'distance', 
    ((UNIX_TIMESTAMP() - UNIX_TIMESTAMP(mb.anniv)) / 3600 / 24 / 365) AS 'age' , mb.sexe, pr.orientation, mb.pseudo, mb.anniv,mb.ID 'ID_membre' 
    FROM photos__ ph 
    LEFT JOIN photos__rank rk ON rk.ID_photo = ph.ID 
    LEFT JOIN photos__signalements sg ON sg.ID_photo = ph.ID 
    INNER JOIN membre__ mb ON mb.ID = ph.ID_membre 
    INNER JOIN membre__profil pr ON pr.ID_membre = mb.ID 
    INNER JOIN site__villes v ON v.ID = pr.ID_ville 
    // '96' is the currently connected member, written into the query by PHP
    WHERE mb.ID <> '96'
// THE FOLLOWING LINE SHOULD PREVENT THE QUERY TO RETURN PICTURES ALREADY RANKED // BY THE USER CURRENTLY CONNECTED, BUT IT DOESN'T WORK :
    AND (rk.ID_membre <> '96' OR ISNULL(note)) // THIS LINE DOESN'T WORK
// SAME PROBLEMS WITH PICTURES ALREADY REPORTED BY THE USER '96' (the connected):    
 AND (sg.ID_membre <> '96' OR ISNULL(sg.ID)) // AND THIS ONE AS WELL
    AND ph.innotable = 0 
    AND mb.sexe = 'f' 
    AND pr.orientation IN ('hetero', 'bi')
    GROUP BY ph.ID HAVING distance < 10 AND age >= 16 
    ORDER BY RAND() LIMIT 1

好吧，我写了一个"分组依据"子句来仅返回包含我需要显示的信息（距离、年龄、成员 ID、照片 ID 等）的行。问题是，当多个成员已经对同一张照片进行排名时，此查询可以返回用户已经排名的照片。我发现这是因为当我说"哪里rk.ID_membre <>'96'或ISNULL（注意）"我对MySQL说："如果照片还没有标记，或者您找到的第一个标记与'96'不同，您可以返回照片"。如何说"如果没有标记，您可以返回，或者如果有，所有标记必须与'96'不同）。

我需要一个 SQL 函数作为 COUNT 或 AVG，如果聚合列中是否有 int，则返回该函数。我会做

  SELECT .. all the other infos .., 
IS_THERE('96' IN photo) AS 'already_ranked', 
IS_THERE('96' IN signalements) 'already_reported' 
    ..blablabla...
    WHERE/HAVING already_reported = 0 AND already_ranked = 0
    GROUP BY photos.ID

不要犹豫，告诉我是否有另一种更快或更简单的方法来进行此查询。

Answer 1

考虑：

SELECT `id`, SUM(CASE WHEN LOCATE('96', `field`) > 0 THEN 1 ELSE 0 END) AS cnt
FROM `table`
GROUP BY `id`