左连接复制并计数所有行

t1qtbnec 于 2021-06-15 发布在 Mysql

关注(0)|答案(3)|浏览(287)

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

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个表，Apostadors，depositos，saque和contas。我试过了，但没有成功。
编辑2：如果我尝试下面的查询，我可以为所有行获得正确的值，但是如果我像下面的查询那样多做一个join，它会带来错误的值

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

sql mysql

来源：https://stackoverflow.com/questions/53789928/left-join-duplicating-and-count-all-rows

3条答案

按热度按时间

neskvpey1#

我在下面的查询中更改了一些我不知道的内容，它现在正在工作！！
谢谢你的帮助！！

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

赞(0）回复(0）举报 2021-06-16

whlutmcx2#

要获得相同的结果，需要为saldo属性包含sum aggregate函数

SELECT a.Nome
      ,SUM(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

赞(0）回复(0）举报 2021-06-16

m2xkgtsf3#

问题是，你是沿着不同的维度连接。而且， count() 只算不算- NULL 价值观。
通常，最好的解决方案是在你之前进行聚合 JOIN . 不过，一个简单的解决办法是 COUNT(DISTINCT) :

SELECT a.Nome, c.Saldo
       COUNT(DISTINCT d.id) AS Depositos
       COUNT(DISTINCT s.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

赞(0）回复(0）举报 2021-06-15

我来回答

左连接复制并计数所有行

3条答案

相关问题

热门标签

最新问答