分享
三行代码  ›  专栏  ›  技术社区  ›  Bruno Oyama

PostgreSQL |合并查询结果

  •  1
  • Bruno Oyama  · 技术社区  · 1 周前

    我这里有个“简单”的情况,但我自己解决不了。因此,我需要将第一个查询的一个结果与第二个查询的结果结合起来,得到一个“最终结果”。

    SELECT player, COUNT(shot) shots FROM table1 GROUP BY player;
    
    +---------+-------+
    | player  | shots |
    +---------+-------+
    | player1 | 10    |
    +---------+-------+
    | player2 | 10    |
    +---------+-------+
    

    SELECT player, COUNT(hit) hits FROM table2 GROUP BY player;
    
    +---------+-------+
    | player  | hits  |
    +---------+-------+
    | player1 | 10    |
    +---------+-------+
    | player2 | 5     |
    +---------+-------+
    

    我需要的是计算准确度(命中率/命中率*100),显示结果。

    +---------+-------+------+-----+
    | player  | shots | hits | acc |
    +---------+-------+------+-----+
    | player1 | 10    | 10   | 100 |
    +---------+-------+------+-----+
    | player2 | 10    | 5    | 50  |
    +---------+-------+------+-----+
    
    1 回复  |  直到 1 周前
        1
  •  1
  •   Gordon Linoff    1 周前

    你可以用 join

    SELECT player, s.shots, h.hits
    FROM (SELECT player, COUNT(shot) as shots
          FROM table1
          GROUP BY player
         ) s JOIN
         (SELECT player, COUNT(hit) as hits
          FROM table2
          GROUP BY player
         ) h
         USING (player);
    

    你真的打算吗 COUNT() SUM() 会更合适。

    此外,这只返回两个表中的玩家。如果你想让任何一张桌子上都有球员,使用 FULL JOIN .