mysql - 在没有子查询的情况下在mysql中获取比率

Question

我想从下表运行一个查询，得到从执行办公室支付的金额与应付总额的比率:

 CREATE TABLE  offices (
   name VARCHAR(255),
   ID VARCHAR(255),
  level VARCHAR(255)
 );



 INSERT INTO offices (name,ID,level)
   VALUES 
  ('chairman', 'ad1', 'admin'),
  ('MD', 'ad2', 'admin'),
  ('CEO', 'ad3', 'admin'),
  ('president', 'ex1', 'exec' ),
  ('VP', 'ex2', 'exec'),
  ('GM', 'ex3', 'exec'),
  ('HOD', 'ex4', 'exec');

CREATE TABLE  cheques (
  payee VARCHAR(255) ,
 officeID VARCHAR(255),
 amount INT(),
 status VARCHAR(255) 
 );


INSERT INTO cheques
 VALUES ('john', 'ad2', '100',  'paid'),
 ('john',   'ad3',  '50',   'owed'),
 ('john',   'ex3',  '80',   'paid'),
 ('john',   'ex4',  '150',  'owed'),
 ('john',   'ex1',  '35',   'paid'),
('mary',    'ad1',  '200',  'paid'),
('mary',    'ad3',  '90',   'owed'),
('mary',    'ex2',  '110',  'paid'),
('mary',    'ex4',  '40',   'owed'),
('mary',    'ex1',  '60',   'paid'),
('jane',    'ad1',  '75',   'paid'),
('jane',    'ad3',  '120',  'paid');

预期结果如下:

data.frame(
 payee=c("john","mary"),
 totalpaid=c(115,170),
 status=c("paid", "paid"),
 totalsdue = c(415, 500),
 ratio=(0.28, 0.34)
   )

我已经能够从以下执行办公室获得 totalpaid

  SELECT c.payee, SUM(c.amount) as totalpaid, c.status
   FROM cheques c
   JOIN offices o
   ON c.officeID = o.ID
  WHERE 
   o.level LIKE '%ex%'
 AND 
   c.status LIKE '%paid%'
 GROUP BY payee 

总计如下

   SELECT  c.payee, SUM(c.amount) as totalsdue
     FROM cheques c
      GROUP BY payee

我没有成功的尝试:

  SELECT  
   c.payee, SUM(c.amount) as totalsdue
      totalsdue/totalpaid as ratio
      FROM cheques c
      WHERE c.payee IN
        (SELECT c.payee, SUM(c.amount) as totalpaid, c.status
           FROM cheques c
           JOIN offices o
           ON c.officeID = o.ID
           WHERE 
             o.level LIKE '%ex%'
           AND 
             c.status LIKE '%paid%'
          GROUP BY payee )
      GROUP BY payee

如何通过单个查询获得预期结果。

Answer 1

对于结果，您可以在 JOIN 中使用子查询

  SELECT t1.payee, t1.totalsdue, t1.totalsdue/t2.totalpaid 
  from  (
      SELECT  c.payee, SUM(c.amount) as totalsdue
      FROM cheques c
      GROUP BY payee
  ) t1
  INNER JOIN  (
    SELECT c.payee, SUM(c.amount) as totalpaid, c.status
    FROM cheques c
    JOIN offices o  ON c.officeID = o.ID
    WHERE o.level LIKE '%ex%'
    AND   c.status LIKE '%paid%'
    GROUP BY payee 
 ) t2 on t1.payee = t2.payee 

为了更好的表现..
检查您是否真的需要 like 和 wildchar 匹配，或者您可以使用精确匹配来生成查询

并确保您在连接中可能涉及的列和位置上有适当的索引

表检查列(officeID，payee)

或者如果列status允许完全匹配列(officeID, status, payee)