如何使用引用表中的不同值返回 SUM 值

Question

我在架构中有以下结构：

 -------------------------------     ----------------------------                                        
 |           m_user            |     |          person          |       
 -------------------------------     ----------------------------                                   
 |  UUID  |  ID   |  PLATFORM  |     |  ID   |  NAME  |  EMAIL  |            
 | 456789 | 22222 |     TG     |     | 22222 | JOSEPH | [e-mail] |          
 | 987654 | 22222 |     MS     |     | 85858 | MARKUS | [e-mail] |          
 | 948576 | 85858 |     TG     |     | 36363 | ANDREA | [e-mail] |              
 -------------------------------     ----------------------------                                        
 -------------------------------------------------
 |                    plan                       |
 -------------------------------------------------
 |     ID     |   HOURS  |    DATE    |  CLIENT  |
 |    22222   |    72    | 2017-12-05 | CLIENT11 |
 |    22222   |    88    | 2017-12-25 | CLIENT11 |
 |    85858   |    48    | 2017-12-05 | CLIENT12 |
 -------------------------------------------------

我需要返回表中存在的每个用户计划的HOURS m_user SUM。 m_user每个平台只允许一个ID：同一个用户可以在两个平台上，但拥有适用于两个平台的唯一 ID。

当结果显示重复的SUM值时，会出现此问题，因为 ID 在表中出现两次m_user。这是查询：

SELECT  ps.id
        ps.name,
        ps.email,
        SUM(pl.hours) AS hours
FROM  schema.person AS ps
JOIN  schema.m_user AS usr ON ps.id = usr.id
JOIN  schema.plan   AS pl  ON usr.id = pl.id      -- Here is the problem, I think
WHERE  pl.client = 'CLIENT11'     AND
       pl.date BETWEEN '2017-12-01' AND '2017-12-31'
GROUP BY id, name, email;

我尝试使用DISTINCT和DISTINCT ON (usr.id)但给出的结果是相同的。

这是我得到的结果：

--------------------------------------
|   ID  |  NAME  |  EMAIL  |  HOURS  |
--------------------------------------
| 22222 | JOSEPH | [e-mail] |   320   |     -- <- 320 instead of 160
|  ...  |  ....  |   ....  |   ...   |
--------------------------------------

我是SQL的新手，所以我认为这是一个简单的错误，我现在无法弄清楚，我也尝试使用OVER (PARTITION BY usr.id)和LIMIT 1但是我再次为出现22222的每一行得到320。是否需要使用 CTE 来执行此查询？我希望你能帮助我，谢谢(我目前正在使用PostgreSQL，但我认为这个问题通常适用于SQL，所以我设置了SQL标签(。

Answer 1

删除了m_user上的联接。 对表使用子查询m_user查找用户列表。

SELECT  ps.id,
        ps.name,
        ps.email,
        SUM(pl.hours) AS hours
FROM   schema.person AS ps
JOIN   schema.plan   AS pl  ON ps.id = pl.id    
WHERE  pl.client = 'CLIENT11'     AND
       pl.date BETWEEN '2017-12-01' AND '2017-12-31'
AND    ps.id IN ( SELECT usr.id FROM  schema.m_user AS usr ) 
GROUP BY ps.id, ps.name, ps.email;

sqlfiddle： sqlfiddle.com/#!17/5996e/1

Answer 2

您始终可以将其表述为：

SELECT ps.id, ps.name, ps.email, SUM(pl.hours) AS hours
FROM schema.person ps JOIN
     (SELECT usr.*, ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) as seqnum
      FROM schema.m_user usr 
     ) usr
     ON ps.id = usr.id JOIN 
     schema.plan pl
     ON usr.id = pl.id AND seqnum = 1
WHERE  pl.client = 'CLIENT11' AND
       pl.date BETWEEN '2017-12-01' AND '2017-12-31'
GROUP BY id, name, email;

这将为join选择一行。