SQL——两个结果相除

Question

我下面有两个查询，它们都来自同一个“玩家”表。我想将查询 1 除以查询 2 以获得相关百分比。我对更详细的 SQL 查询以及在论坛上发帖还比较陌生……但是如果您对如何结合此查询以获得相关的百分比结果有任何建议，请告诉我。

1

Select
  sysdate,sum(Count(init_dtime))
From Player p
Where
  Trunc(Init_Dtime) > Trunc(Sysdate) - 7 
  And Trunc(Create_Dtime) >= To_Date('2012-mar-01','yyyy-mon-dd')
  and trunc(create_dtime) < to_date('2015-sep-9','yyyy-mon-dd')
Group By Trunc(Init_Dtime)
Order By Trunc(Init_Dtime) Asc

2

Select
  Sum(Count(Create_Dtime))
From Player P
where 
  Trunc(Create_Dtime) >= To_Date('2012-mar-01','yyyy-mon-dd')
  And Trunc(Create_Dtime) < To_Date('2015-sep-9','yyyy-mon-dd')
Group By Trunc(create_Dtime)
Order By Trunc(create_Dtime) Asc

Answer 1

你可以直接说

select sysdate,
       count((init_dtime)) / sum((Create_Dtime)) * 100 as percentage
  from Player p
 where Trunc(Init_Dtime) > Trunc(Sysdate) - 7 
   and Trunc(Create_Dtime) >= To_Date('2012-mar-01','yyyy-mon-dd')
   and trunc(create_dtime) < to_date('2015-sep-9','yyyy-mon-dd')
   order by percentage asc

SQL 中的 group by 是不需要的，因为您实际上并没有按某些内容进行分组。例如，当您需要按玩家划分的百分比时，group by 非常有用。然后你会说group byplayer_id并且在select中会有player_id:

select player_id, count(…)
  from …
 where …
group by player_id

编辑:如果 where 子句不同:

select sysdate, 
       (
           (select count((init_dtime))
             from player p
            where trunc(Init_Dtime) > trunc(Sysdate) - 7 
              and Trunc(Create_Dtime) >= To_Date('2012-mar-01','yyyy-mon-dd')
              and trunc(create_dtime) < to_date('2015-sep-9','yyyy-mon-dd'))
            / 
           (select count((Create_Dtime))
              from player P
             where trunc(Create_Dtime) >= To_Date('2012-mar-01','yyyy-mon-dd')
               and trunc(Create_Dtime) < To_Date('2015-sep-9','yyyy-mon-dd'))
       ) * 100 as percentage
from dual