php - MySQL : search function into a aggregated column by "group by"

Question

我有 3 个表:成员、照片和标记。成员(member)可以发布照片并对其他成员(member)的照片进行排名。

我正在用 PHP 编写一个脚本，该脚本返回一张照片(使用 random())，该照片可以由在其成员(member)区域中连接的用户进行排名。因此，我做了以下查询(我评论了显然包含问题的行):

 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

嗯，我写了一个“group by”子句来仅返回包含我需要显示的信息的行(距离、年龄、成员 ID、照片 ID 等)。问题是，当多个成员已经对同一张照片进行了排名时，此查询可以返回用户已经排名的照片。我发现这是因为当我说“where rk.ID_membre <> '96' OR ISNULL(note)”我对 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`