时间戳

首页 > 解决方案 > 左连接重复并计算所有行

mysql - 左连接重复并计算所有行

问题描述

我正在尝试以不同的结果执行下面的查询，但没有成功。

SELECT a.Nome
      ,c.Saldo
      ,COUNT(d.Apostador_ID) AS Depositos
      ,COUNT(s.Apostador_ID) AS Saques
FROM Apostadores a
LEFT JOIN Contas c ON a.ID = c.Apostador_ID
LEFT JOIN Deposito d ON a.ID = d.Apostador_ID
LEFT JOIN Saque s ON a.ID = s.Apostador_ID
GROUP BY a.Nome, c.Saldo

它正在返回：

Nome    Saldo   Depositos   Saques  
Marcos  4857      180        180    
Paulo   2868      180        180    
Mariana 8348      180        180    
Marcela -3512     180        180    
Mario   -2367     180        180    
Maria   -3359     180        180

但是每个人的结果应该是 27。

谢谢！！

编辑 1：

INSERT INTO Apostadores
    (`ID`, `Nome`, `CPF`, `Email`)
VALUES
    (NULL, 'Marcos', '12345678901', 'marcos@email.com'),
    (NULL, 'Paulo', '12345678902', 'paulo@email.com'),
    (NULL, 'Mariana', '12345678903', 'mariana@email.com'),
    (NULL, 'Marcela', '12345678904', 'marcela@email.com'),
    (NULL, 'Mario', '12345678905', 'mario@email.com'),
    (NULL, 'Maria', '12345678906', 'maria@email.com');


INSERT INTO Contas
    (`ID`, `Apostador_ID`, `Saldo`)
VALUES
    (NULL, '1', '700.00'),
    (NULL, '2', '1000.00'),
    (NULL, '3', '5000.00'),
    (NULL, '4', '250.00'),
    (NULL, '5', '365.00'),
    (NULL, '6', '391.82');

INSERT INTO Deposito
    (`ID`, `Apostador_ID`, `Valor_Depositado`)
VALUES
    (NULL, '1', '700'),
    (NULL, '2', '900'),
    (NULL, '3', '580'),
    (NULL, '4', '260'),
    (NULL, '5', '360'),
    (NULL, '6', '154'),
    (NULL, '1', '1000'),
    (NULL, '2', '1346'),
    (NULL, '3', '2005'),
    (NULL, '4', '360'),
    (NULL, '5', '392'),
    (NULL, '6', '182'),
    (NULL, '1', '362'),
    (NULL, '2', '182'),
    (NULL, '3', '270'),
    (NULL, '4', '390'),
    (NULL, '5', '160'),
    (NULL, '6', '380'),
    (NULL, '1', '290'),
    (NULL, '2', '370'),
    (NULL, '3', '193'),
    (NULL, '4', '256'),
    (NULL, '5', '509'),
    (NULL, '6', '536'),
    (NULL, '1', '500'),
    (NULL, '2', '190'),
    (NULL, '3', '160'),
    (NULL, '4', '380'),
    (NULL, '5', '190'),
    (NULL, '6', '170');


INSERT INTO Saque
    (`ID`, `Apostador_ID`, `Valor_Sacado`)
VALUES
    (NULL, '1', '100'),
    (NULL, '2', '200'),
    (NULL, '3', '100'),
    (NULL, '4', '50'),
    (NULL, '5', '55'),
    (NULL, '6', '70'),
    (NULL, '1', '80'),
    (NULL, '2', '90'),
    (NULL, '3', '65'),
    (NULL, '4', '55'),
    (NULL, '5', '45'),
    (NULL, '6', '60'),
    (NULL, '1', '35'),
    (NULL, '2', '61'),
    (NULL, '3', '99'),
    (NULL, '4', '66'),
    (NULL, '5', '55'),
    (NULL, '6', '33'),
    (NULL, '1', '88'),
    (NULL, '2', '15'),
    (NULL, '3', '20'),
    (NULL, '4', '29'),
    (NULL, '5', '36'),
    (NULL, '6', '58');

这是上面的示例。我添加了 4 个表，Apostadores、Depositos、Saque 和 Contas。我试过 DISTINCT 没有成功。

编辑2：如果我尝试下面的查询，我可以获得所有行的正确值，但是如果我按照下面的查询再进行一次连接，它会带来错误的值

SELECT a.Nome
      ,c.Saldo
      ,COUNT(d.ID)
FROM Apostadores a
LEFT JOIN Contas c ON a.ID = c.Apostador_ID
LEFT JOIN Depositos d ON a.ID = d.Apostador_ID
GROUP BY a.Nome, c.Saldo

这是带来错误值的查询。

SELECT a.Nome
      ,c.Saldo
      ,COUNT(d.ID)
      ,COUNT(s.ID)
FROM Apostadores a
LEFT JOIN Contas c ON a.ID = c.Apostador_ID
LEFT JOIN Depositos d ON a.ID = d.Apostador_ID
LEFT JOIN Saques s ON a.ID = s.Apostador_ID
GROUP BY a.Nome, c.Saldo

标签： mysqlsql

解决方案

我在下面的查询中更改了一些我不知道是什么的东西，现在它正在工作！！

谢谢您的帮助！！

SELECT a.Nome
      ,c.Saldo
      ,COUNT(DISTINCT d.ID) AS Depositos
      ,COUNT(DISTINCT s.ID) AS Saques
      ,SUM(d.Valor_Depositado) AS Valor_Depositado
      ,SUM(s.Valor_Sacado) AS Valor_Sacado
      ,COUNT(DISTINCT t.ID) AS Transferencias_Efetuadas
      ,SUM(t.Valor_Transferido) AS Valor_Enviado_Transferencia
      ,COUNT(DISTINCT ap.ID) AS Quantidade_Apostas
      ,SUM(ap.Valor_Apostado) AS Valor_Apostado
FROM Apostadores a
LEFT JOIN Contas c ON a.ID = c.Apostador_ID
LEFT JOIN Depositos d ON a.ID = d.Apostador_ID
LEFT JOIN Saques s ON a.ID = s.Apostador_ID
LEFT JOIN Transferencias t ON a.ID = t.Origem_Apostador_ID
LEFT JOIN Apostas ap ON a.ID = ap.Apostador_ID
GROUP BY a.Nome, c.Saldo

推荐阅读