SQL 查询 : SUM on three columns with criteria

Question

我有一个包含这些列的表格:

idx | amount | usercol1 | usercol2 | usercol3 | percentage1 | percentage2 | percentage3

数据通常是这样的:

0   | 1500   | 1        | null     | null     | 100         | null        | null
1   | 3000   | 2        | 3        | null     | 50          | 50          | null

我想对每个用户的金额做一个 SUM()。

示例:

• user1= 1500*100/100 (数量*usercol1/100)
• user2= 3000*50/100 (金额*usercol1/100)
• user3= 3000*50/100 (金额*usercol2/100)

我试过 UNION 无济于事(没有对 SUM 求和)。

有办法吗？问题是它应该按用户名分组(我在 exampletable.usercol1=usernames.idx 上使用 LEFT OUTER JOIN 用户名获得)。

我知道这是非标准的，使用另一个表的关系会更好。但是我不允许更改表结构。

非常非常感谢! :=)

特此举个给出错误结果的例子(貌似只给出了中间查询的结果)

( 
SELECT SUM(projects.amount * (projects.percentage1/100)) as totalproj, 
entities.idx as idx, 
COUNT(projects.idx) as numproj, 
entities.name 
 FROM projects 
 INNER JOIN entities ON projects.usercol1=entities.idx 
 WHERE projects.usercol1=entities.idx 
GROUP BY name ORDER BY totalproj DESC 
) 
UNION ALL
( 
SELECT SUM(projects.amount * (projects.percentage2/100)) as totalproj, 
entities.idx as idx, 
COUNT(projects.idx) as numproj, 
entities.name 
 FROM projects 
 INNER JOIN entities ON projects.usercol2=entities.idx 
 WHERE projects.usercol2=entities.idx 
GROUP BY name ORDER BY totalproj DESC 
) 
UNION ALL
( 
SELECT SUM(projects.amount * (projects.percentage3/100)) as totalproj, 
entities.idx as idx, 
COUNT(projects.idx) as numproj, 
entities.name 
 FROM projects 
 INNER JOIN entities ON projects.usercol3=entities.idx 
 WHERE projects.usercol3=entities.idx 
GROUP BY name ORDER BY totalproj DESC 
)
ORDER BY totalproj DESC 
LIMIT 10

Answer 1

您可以使用派生表模拟第一个范式表，然后加入该表。

SELECT SUM(P.amount * (P.percentage/100)) as totalproj, 
       entities.idx as idx, 
       COUNT(P.idx) as numproj, 
       entities.name 
FROM 
(
SELECT idx, amount, usercol1 AS usercol, percentage1 AS percentage
FROM projects
UNION ALL
SELECT idx, amount, usercol2 AS usercol, percentage2 AS percentage
FROM projects
UNION ALL
SELECT idx, amount, usercol3 AS usercol, percentage3 AS percentage
FROM projects
) P
 INNER JOIN entities ON P.usercol=entities.idx 
 WHERE P.usercol=entities.idx 
 GROUP BY name 
 ORDER BY totalproj DESC