我有一张桌子，上面有这些列：
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无济于事（未对总和求和）。
有没有办法做到这一点 ？问题在于它应该按用户名分组（我通过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
最佳答案
您可以使用派生表来模拟第一个范式表，然后将其联接到该表上。
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
关于sql - SQL查询:具有条件的三列上的SUM，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/3711016/