sql - 按出现在多行上的 id 求和成本

Question

解决方案

我通过简单地执行以下操作解决了它。

SELECT table_size, sum(cost) as total_cost, sum(num_players) as num_players
FROM
(
  SELECT table_size, cost, sum(tp.uid) as num_players
  FROM tournament as t
  LEFT JOIN takes_part AS tp ON tp.tid = t.tid
  LEFT JOIN users as u on u.uid = tp.tid
  JOIN attributes as a on a.aid = t.attrId
  GROUP BY t.tid
) as res
GROUP BY table_size

我不确定它会工作，我必须在我的真实 sql 中使用其他聚合函数，但它似乎工作正常。如果我想进行其他类型的计算，将来可能会出现问题，例如对所有锦标赛进行 COUNT(DISTINCT tp.uid)。尽管如此，在这种情况下，这并不是那么重要，所以我现在很满意。谢谢大家的帮助。

更新!!!这是一个解释问题的 fiddle : http://www.sqlfiddle.com/#!2/e03ff/7

我想得到:

table_size  |  cost
-------------------------------
5           |  110
8           |   80

旧帖

我确信有一个我只是没有看到的简单解决方案，但我似乎无法在任何地方找到解决方案。我想做的是:

我需要计算系统中每场锦标赛的“成本”。由于其他原因，我不得不加入许多其他表，使相同的成本出现在多行上，如下所示:

id | name | cost | (hidden_id)
-----------------------------
0  | Abc  | 100  | 1
1  | ASD  | 100  | 1
2  | Das  | 100  | 1
3  | Ads  |  50  | 2
4  | Ads  |  50  | 2
5  | Fsd  |   0  | 3
6  | Ads  |   0  | 3
7  | Dsa  |   0  | 3

上表中的成本链接到 SQL 中不需要选择的 id 值(这取决于用户在运行时决定的内容)。我想得到的是 100+50+0 = 150 的总和。当然，如果我只使用 SUM(cost)，我会得到不同的答案。我尝试使用 SUM(cost)/COUNT(*)*COUNT(tourney_ids) 但这只会在某些情况下给出正确的结果。一个(非常)简单的查询形式如下所示:

SELECT SUM(cost) as tot_cost -- This will not work as it sums all rows where the sum appears.
FROM t
JOIN ta ON t.attr_id = ta.toaid
JOIN tr ON tr.toid = t.toid  -- This row will cause multiple rows with same cost
GROUP BY *selected by user*  -- This row enables the user to group by several attributes, such as weekday, hour or ids of different kinds.

更新。一个更正确的 SQL 查询，也许:

SELECT
*some way to sum cost*
FROM tournament AS t
JOIN attribute AS ta ON t.attr_id = ta.toaid
JOIN registration AS tr ON tr.tourneyId = t.tourneyId
INNER JOIN pokerstuff as ga ON ta.game_attr_id = ga.gameId
LEFT JOIN people AS p ON p.userId = tr.userId
LEFT JOIN parttaking AS jlt ON (jlt.tourneyId = t.tourneyId AND tr.userId = jlt.userId)
LEFT JOIN (
    SELECT t.tourneyId,
    ta.a - (ta.b) - sum(c)*ta.cost AS cost
    FROM tournament as t
    JOIN attribute as ta ON (t.attr_id = ta.toaid)
    JOIN registration tr ON (tr.tourneyId = t.tourneyId)
    GROUP BY t.tourneyId, ta.b, ta.a
) as o on t.tourneyId = o.tourneyId
AND whereConditions
GROUP BY groupBySql

表格说明

• 锦标赛(tourneyId、名称、attributeId)
• 属性(attributeId, ..., gameid)
• 注册(userId，tourneyId，...)
• pokerstuff(gameid,...)
• 人(用户 ID，...)
• 参与(userId，tourneyId，...)

假设我们有以下内容(成本实际上是在子查询中计算的，但由于它与锦标赛相关，因此我将在此处将其视为一个属性):

比赛:

tourneyId | name         | cost
1         | MyTournament |  50
2         | MyTournament |  80

和

userId | tourneyId
1      | 1
2      | 1
3      | 1
4      | 1
1      | 2
4      | 2

问题很简单。我需要能够在不多次计算锦标赛的情况下获得锦标赛成本的总和。总和(以及所有其他聚合)将由用户动态分组。

一个大问题是，我尝试过的许多解决方案(例如 SUM OVER...)都要求我按某些属性进行分组，而我做不到。 group by 子句必须完全由用户决定。成本总和应该对任何分组依据属性求和，唯一的问题当然是出现总和的多行。

你们中的任何人对可以做什么有任何好的提示吗？

Answer 1

尝试以下操作:

select *selected by user*, sum(case rownum when 1 then a.cost end)
from
(
    select
        *selected by user*, cost,
        row_number() over (partition by t.tid) as rownum
    FROM t
    JOIN ta ON t.attr_id = ta.toaid
    JOIN tr ON tr.toid = t.toid
) a
group by *selected by user*

row_number 用于对具有相同锦标赛行的每一行进行编号。在对成本求和时，我们只考虑 rownum 为 1 的那些行。就成本而言，所有其他行都是这一行的副本。

---

就 fiddle 而言:

select table_size, sum(case rownum when 1 then a.cost end)
from
(
  SELECT
      table_size, cost,
      row_number() over (partition by t.tid) as rownum
  FROM tournament as t
  LEFT JOIN takes_part AS tp ON tp.tid = t.tid
  LEFT JOIN users as u on u.uid = tp.tid
  JOIN attributes as a on a.aid = t.attrId
) a
group by table_size