mysql - SUM (some_column) WHERE some_column 匹配

Question

所以我有一个表，我正在尝试获取 SUM(activity_weight)，其中 Activity_typeid 是唯一的。

每场比赛都有无限数量的activity_typeid。

正如您在下面的代码中看到的，我想知道是否有一些 SQL 函数可以查找 id 唯一的某些内容的 SUM？

感谢您提前提供的任何帮助!

我在下面附上了我的 table 的照片和所需的输出

 SELECT a.userid, u.name, u.profilePic , 
  SUM(activity_weight) AS totalPoints , 

  //sum the points when the activity_typeid is unique and make an extra column for each of those sums
  SUM(CASE WHEN activity_typeid //is unique// THEN activity_weight    ELSE NULL END) AS specific_points , 

  FROM activity_entries a INNER JOIN users1 u ON u.id = a.userid 

  WHERE competitionId = '$competitionId' GROUP BY a.userid ORDER BY totalPoints

Answer 1

从图像看来你想要一个枢轴，我相信在 MySQL 中你可以通过这样做来实现这一点

SELECT a.userid, u.name, u.profilePic,
    SUM(activity_weight) total_points,
    SUM(CASE WHEN activity_typeid=22 THEN activity_weight ELSE 0 END) activity22,
    SUM(CASE WHEN activity_typeid=33 THEN activity_weight ELSE 0 END) activity33,
    SUM(CASE WHEN activity_typeid=55 THEN activity_weight ELSE 0 END) activity55    
FROM activity_entries a 
INNER JOIN users1 u ON u.id = a.userid 
WHERE competitionId = '$competitionId' 
GROUP BY a.userid 
ORDER BY totalPoints

编辑评论:以下可能有一些语法错误，但想法是动态创建sql然后执行它

-- generate sql for pivoted columns
SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN activity_typeid=',activity_typeid,
      ' THEN activity_weight ELSE 0 END) AS activity_', activity_typeid, 
    )
  ) INTO @sql
FROM activity_entries;

-- place above in full select query
-- n.b. the `$competitionId` could be a problem my MySQL is not v. good
SET @sql = CONCAT('SELECT a.userid, u.name, u.profilePic,
                          SUM(activity_weight) total_points,', @sql,
                  'FROM activity_entries 
                   JOIN users1 u ON u.id = a.userid 
                   WHERE competitionId = ''$competitionId''   
                   GROUP BY a.userid, u.name, u.profilePic
                   ORDER BY totalPoints');

-- execute the dynamic sql
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;