mysql - 如何计算任意数量的 COUNTS 的总和

Question

我想知道是否可以在一个 SQL 查询中将每张照片的分数与其分数类型因子的乘积求和？

示例:

t = total : my aim, the total score for each photo
c_foo = count score type foo = the amount of scores for each photo with the score type name = foo 
c_bar = count score type bar = the amount of scores for each photo with the score type name = bar
m_foo = foo factor
m_bar = bar factor

不同分数类型的数量是任意的。

t = c_foo * m_foo + c_bar * m_bar + … + c_last * m_last

我最初的想法是使用以下表结构:

• http://twitpic.com/840ctk/full

到目前为止我有以下查询:

SELECT p.id, st.name, st.factor, COUNT(*) AS count
FROM s2p_photo p
LEFT JOIN s2p_score s 
LEFT JOIN s2p_score_type st 
GROUP BY p.id, st.name
ORDER BY p.id ASC 

我收到了照片的名称、系数和总和，但我无法进行数学计算。

• http://twitpic.com/840ctk/full

我不知道UNION在这里是否可行。

我的表结构还好吗？你们有人知道吗？

PS:抱歉，我无法发布图片；请在浏览器中手动打开它们:(

Answer 1

不确定我是否理解你的意思，但这就是你要找的吗？

SELECT
  id,
  SUM(factor*count) AS totalscore
FROM (
    SELECT
      p.id AS id,
      st.factor AS factor,
      COUNT(*) AS count 
    FROM
      s2p_photo p 
      LEFT JOIN s2p_score s 
      LEFT JOIN s2p_score_type st 
    GROUP BY p.id, st.name
    ORDER BY p.id ASC
) AS baseview
GROUP BY id