MySQL多个Left Join输出错误 [英] MySQL multiple Left Join gets wrong output

问题描述

您好，我是MySQL的初学者。我试图从voip电话卡数据库中查询一些我需要加入多个表的数据。它给出了正确的输出，直到有两个左连接表，但是当我添加第三个表时，总持续时间列给出了错误的输出。



我的第一个查询看起来像这样，输出就在这个screencap中（ http://i.stack.imgur.com/M8JN6.png）：

 选择 c.login，cname.Name，cname.LastName，DATE_FORMAT（Creation_Date，' ％d-％m-％y '） as  regdate，DATE_FORMAT（（选择 max（call_start） from 调用其中​​ calls.id_client = c.id_client），' ％d-％m-％y'） as  lastcall，c.account_state，sum（cdr.duration） /  60 ） as  total_duration 来自 clientsshared  as  c 
  left   join  invoiceclients  as  cname  on  cname.IdClient = c.id_client 
  left   join 调用 as  cdr  on  cdr.id_client = c.id_client 
 其中 c.id_reseller = '  10'  group   by  c.id_client  order   by  total_duration  desc  limit  100  

新查询i这样的输出错误，请检查screencap（ http://i.stack.imgur.com/usce8.png ）：

 选择 c.login，cname.Name，cname.LastName，DATE_FORMAT（Creation_Date，' ％m-％d- ％y'） as  regdate，
（选择 max（data）< span class =code-keyword>来自 payment 其中 payments.id_client = c.id_client） as  lastpayment，
（选择 max（call_start）来自来电 where  calls.id_client = c.id_client） as  lastcall，
 c.account_state，sum（cdr.duration / < span class =code-digit> 60 ） as  total_duration 来自 clientsshared  as  c 
 
  left   join  invoiceclients  as  cname  on  cname.IdClient = c.id_client 
  left   join  payments  as  p  on  p.id_client = c.id_client 
  left   join 调用作为 cdr   cdr.id_client = c.id_client 
 其中 c.id_reseller = '  10'  group   by  c.id_client  order   by  total_duration  desc  limit  100  

解决方案

您将客户端所有呼叫的总持续时间乘以客户端的总付款次数。



查询实际上并没有使用左连接到付款表，因此您可以将其删除：

 选择 
 c.login，
 cname.Name，
 cname.LastName，
 DATE_FORMAT（Creation_Date，'  ％m-％d-％y'） as  regdate，
（ 选择最大（数据）来自付款其中 payments.id_client = c.id_client） as  lastpayment，
（选择 max（call_sta rt）来自调用其中 calls.id_client = c.id_client） as  lastcall，
 c.account_state，
 sum（cdr.duration /  60 ） as  total_duration 
 来自 
 clientsshared  as  c 
  left   join  invoiceclients  as  cname < span class =code-keyword> on  cname.IdClient = c.id_client 
  left  加入调用作为 cdr   cdr.id_client = c.id_client 
 其中 
 c.id_reseller = '  10' 
  group   by  
 c.id_client 
  order   by  
 total_duration  desc  
 limit  100  

Hello, Im a beginner in MySQL. Im trying to query out some data from a voip calling card database for which I needed to join multiple table. It was giving correct output till there was two left join table, but when I added third table then the "total duration" column is giving wrong output.

My First query looked like this and the output was in this screencap ( http://i.stack.imgur.com/M8JN6.png ):

select c.login,cname.Name,cname.LastName,DATE_FORMAT(Creation_Date,'%d-%m-%y')as regdate,DATE_FORMAT((Select max(call_start) from calls where calls.id_client = c.id_client),'%d-%m-%y') as lastcall, c.account_state,sum(cdr.duration / 60) as total_duration from clientsshared as c
    left join invoiceclients as cname on cname.IdClient = c.id_client
    left join calls as cdr on cdr.id_client = c.id_client
    where c.id_reseller='10' group by c.id_client order by total_duration desc limit 100

The new query is like this which gives wrong output, please check the screencap ( http://i.stack.imgur.com/usce8.png ):

select c.login,cname.Name,cname.LastName,DATE_FORMAT(Creation_Date,'%m-%d-%y')as regdate, 
	(Select max(data) from payments where payments.id_client = c.id_client) as lastpayment,
	(Select max(call_start) from calls where calls.id_client = c.id_client) as lastcall, 
	c.account_state,sum(cdr.duration / 60) as total_duration from clientsshared as c

	left join invoiceclients as cname on cname.IdClient = c.id_client
	left join payments as p on p.id_client = c.id_client
	left join calls as cdr on cdr.id_client = c.id_client
	where c.id_reseller='10' group by c.id_client order by total_duration desc limit 100

解决方案

You're multiplying the total duration for all calls for the client by the total number of payments the client has made.

The query doesn't actually use the left join to the payments table, so you can just remove it:

select 
    c.login,
    cname.Name,
    cname.LastName,
    DATE_FORMAT(Creation_Date,'%m-%d-%y')as regdate, 
    (Select max(data) from payments where payments.id_client = c.id_client) as lastpayment,
    (Select max(call_start) from calls where calls.id_client = c.id_client) as lastcall, 
    c.account_state,
    sum(cdr.duration / 60) as total_duration 
from 
    clientsshared as c
    left join invoiceclients as cname on cname.IdClient = c.id_client
    left join calls as cdr on cdr.id_client = c.id_client
where 
    c.id_reseller='10' 
group by 
    c.id_client 
order by 
    total_duration desc 
limit 100

这篇关于MySQL多个Left Join输出错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！