SUM ( some_column )与some_column匹配

Question

因此，我有一个表，我试图得到和(Activity_weight)，其中activity_typeid是唯一的。

每个竞赛都有无限数量的活动类型。

正如您在下面的代码中所看到的，我想知道是否有一些SQL函数可以找到id是唯一的东西的和，例如？

谢谢你提前提供帮助！

我附上了一张我的桌子的照片，下面是我想要的输出

​

​

 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;

Answer 2

SELECT SUM(something), ... FROM table t1 WHERE NOT EXISTS
(
    SELECT id FROM table t2 WHERE t1.id <> t2.id AND t1.checkfield = t2.checkfield
)
GROUP BY ...

基本上，您希望选择checkfield中所有有值的记录的和，其中没有对该字段具有相同值的记录。这样做很容易:只需查看同一表中是否有一个具有相同值但具有不同id的记录。

不过，我不确定这是不是你想要的。这只是总结了独特的记录。如果活动id不是唯一的，则根本不包括它，甚至一次都不包含。

Answer 3

你可以试试：

GROUP BY ...
HAVING SUM(activity_weight) AND COUNT(activity_typeid)=1

只用于进行比较的聚合函数在HAVING子句中