MySQL多个Left Join输出错误

本文介绍了MySQL多个Left Join输出错误的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 您好，我是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）来自 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 / 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 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 100The 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 cleft join invoiceclients as cname on cname.IdClient = c.id_clientleft join payments as p on p.id_client = c.id_clientleft join calls as cdr on cdr.id_client = c.id_clientwhere 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_clientwhere c.id_reseller='10' group by c.id_client order by total_duration desc limit 100 这篇关于MySQL多个Left Join输出错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！