代码之家 › 专栏 › 技术社区 › manumoomoo

不同方式的mysql和

sum mysql

manumoomoo · 技术社区 · 14 年前

我有两张桌子

用户评级:

| id(int) | to_id(int) | value(int) | created_at(datetime)
|1        | 2          | 1          | 2009-03-01 00:00:00

编辑:我更改了用户\u rater \u id.history \u user \u raters.user \u id与user \u raters.id相关

历史记录用户评分:

| id(int) | user_rater_id(int) | value(int) | created_at(datetime)
| 1       | 1                  | 1          | 2009-03-02 00:00:00
| 2       | 1                  | 1          | 2009-03-02 00:00:00
| 3       | 1                  | -1         | 2009-03-02 00:00:00
| 4       | 1                  | 1          | 2009-03-03 00:00:00
| 5       | 1                  | -1         | 2009-03-03 00:00:00
| 6       | 1                  | -1         | 2009-03-03 00:00:00
| 7       | 1                  | -1         | 2009-03-03 00:00:00

我想计算历史用户评估器的值之和,因为它与用户评估器的目标ID相关。查询结果应为:

| year | month | day | total | down | up
| 2009 | 3     | 2   | 1     | 1    | 2
| 2009 | 3     | 3   | -2    | 3    | 1

我有一个接近的查询,但它没有正确地计算上下。总数是对的。有人能帮我写下这个查询或计算正确上下的新查询吗?

我当前的查询:

SELECT 
 YEAR(history.created_at) AS `year`,
 MONTH(history.created_at) AS `month`,
 DAY(history.created_at) AS `day`,
 SUM(history.value) as `total`,

 (SELECT 
  abs(SUM(historydown.value)) 
 FROM `user_raters` as raterdown 
  INNER JOIN `history_user_raters` AS historydown 
 WHERE raterdown.to_id = 2 
  AND historydown.value = -1 
  AND date(historydown.created_at) 
 GROUP BY history.created_at) as down,

 (SELECT SUM(historyup.value) 
 FROM `user_raters` as raterup 
  INNER JOIN `history_user_raters` AS historyup 
 WHERE raterup.to_id = 2 
  AND historyup.value = 1  
  AND date(history.created_at) 
 GROUP BY raterup.to_id) as up 

FROM `user_raters` 
 INNER JOIN history_user_raters AS history ON user_raters.id = history.user_rater_id
WHERE (user_raters.to_id = 2) 
GROUP BY DATE(history.created_at)

2 回复 | 直到 14 年前

mjv 14 年前

我可能看得太简单了(很抱歉,我现在不能用数据进行测试),但是我猜下面的技巧用两个case语句就可以满足需要了

SELECT 
  YEAR(history.created_at) AS year,
  MONTH(history.created_at) AS month,
  DAY(history.created_at) AS day,
  SUM(history.value) as total,
  SUM(CASE WHEN history.value < 0 THEN history.value ELSE 0 END) as down,
  SUM(CASE WHEN history.value > 0 THEN history.value ELSE 0 END) as up
FROM `user_raters` 
INNER JOIN `history_user_raters` AS history
  ON user_raters.id = history.user_rater_id
WHERE (user_raters.to_id = 1)  -- or some other condition...
GROUP BY DATE(history.created_at)

manumoomoo 14 年前

编辑:@omg小马删除了他的答案。这个回答现在没有意义,但我不会删除我的答案,因为我认为这很愚蠢。

OMG小马

您的查询运行,但不返回任何结果。我不得不稍微调整一下,以便在主查询WHERE子句中添加to_id

SELECT 
 YEAR( t.created_at ) AS `year` , 
 MONTH( t.created_at ) AS `month` , 
 DAY( t.created_at ) AS `day` , 
 SUM( t.value ) AS `total` , 
 MAX( COALESCE( x.sum_down, 0 ) ) AS down, 
 MAX( COALESCE( y.sum_up, 0 ) ) AS up
FROM history_user_raters AS t
JOIN user_raters AS ur ON ur.to_id = t.user_rater_id
LEFT JOIN (

 SELECT hur.user_rater_id, 
  SUM( hur.value ) AS sum_down
 FROM history_user_raters AS hur
 WHERE hur.value = -1
 GROUP BY hur.user_rater_id
) AS x ON x.user_rater_id = t.user_rater_id
LEFT JOIN (

 SELECT hur.user_rater_id, 
  SUM( hur.value ) AS sum_up
 FROM history_user_raters AS hur
 WHERE hur.value =1
 GROUP BY hur.user_rater_id
) AS y ON y.user_rater_id = t.user_rater_id
WHERE ur.to_id =1
GROUP BY YEAR( t.created_at ) , MONTH( t.created_at ) , DAY( t.created_at )