sql - 与多个 ID 的聚合/关联

Question

我有 2 个表 usr 和 rslt，如下所示。

我想得到如下输出。

我试过下面的查询来获得总赢和输

SELECT    tab1.id
       , tab1.wins
       , tab2.loos
FROM
        (SELECT u1.ID
             , COUNT(r1.WINNER) wins
         FROM   rslt r1
             , usr u1
         WHERE  (u1.ID = r1.WINNER)
         GROUP BY u1.ID) tab1
       ,
        (SELECT u2.ID
             , COUNT(r2.Looser) loos
         FROM   rslt r2
             , usr u2
         WHERE  (u2.ID = r2.Looser)
         GROUP BY u2.ID) tab2
WHERE tab1.ID = tab2.id;

Answer 1

这可以使用相关查询来完成，如果有很多 usr，性能可能会很差，因为每个查询都是针对 usr 表中的每一行运行的，但是，您应该明白这一点。

   select 
      id, name, 
      (select count(*) c
       from rslt
       where
         (rslt.sender = usr.id or rslt.receiver = usr.id) and
         rslt.winner = usr.id) wins, 
      (select count(*) c
       from rslt
       where
         (rslt.sender = usr.id or rslt.receiver = usr.id)) played, 
      (select count(*) c
       from rslt
       where
         (rslt.sender = usr.id or rslt.receiver = usr.id) and
         rslt.looser = usr.id) lost, 
      (select count(*) c
       from rslt
       where
         (rslt.sender = usr.id or rslt.receiver = usr.id) and
         rslt.tie <> 'T') tie
    from usr;

我运行查询时的结果:

下面的表创建DML，如果有人想摆弄

create table usr(id number,name varchar(10));

create table rslt (id number,sender number, receiver number,winner number,looser number, tie varchar(1));

insert into usr values(1,'Prince');

insert into  usr values(2,'Subham');

insert into usr values(3,'3');

insert into rslt values(1,1,2,2,1,'T');

insert into rslt values(2,2,3,3,2,'T');