代码之家 › 专栏 › 技术社区 › NVG

MySql与Sum连接

outer-join join mysql sql

NVG · 技术社区 · 14 年前

我有一个名为RESULTS的表,其结构如下:

结果,获胜者,类型

以及一个名为TICKETS的表,其结构如下:

我试着做一些连接,一些求和,但我不能得到我想要的。

SELECT *,COALESCE(SUM(tickets.bet),0) AS totalbets,
      COALESCE(SUM(tickets.sum_won),0) AS totalwins 
FROM `results` NATURAL JOIN `tickets` 
WHERE tickets.status<>0 
GROUP BY resultid

请给我一些建议。

RESULT WINNER TOTALBETS TOTALWINS
1       2        431       222
2       3         0         0
3       1         23        0
4       1         324       111

2 回复 | 直到 14 年前

OMG Ponies 14 年前

用途:

   SELECT r.*,
          COALESCE(x.totalbet, 0) AS totalbet,
          COALESCE(x.totalwins, 0) AS totalwins
     FROM RESULTS r
LEFT JOIN (SELECT t.resultid,
                  SUM(t.bet) AS totalbet,
                  SUM(t.sum_won) AS totalwins
             FROM TICKETS t
            WHERE t.status != 0
         GROUP BY t.resultid) x ON x.resultid = r.resultid

我不喜欢自然连接语法,更喜欢明确地说明如何将表连接/链接在一起。

eumiro 14 年前

SELECT *, COALESCE(SUM(tickets.bet),0) AS totalbets,
      COALESCE(SUM(tickets.sum_won),0) AS totalwins 
FROM `results` NATURAL JOIN `tickets` 
WHERE tickets.status<>0 
GROUP BY resultid