如何在mysql中使用左外部联接以获得我想要的结果? [英] How to use left outer join in mysql to get my desirable results?

问题描述

我制作了 sql小提琴.标识为100和118的用户应具有assigned_scopes，assigned_qa，failed_qa和assigned_canvass的0记录.但是它不会显示该数据.它只显示ID为210的用户的记录.我真正需要的是显示所有用户，如果没有用户，则在每列中显示0.我该怎么办?

此后我一直尝试的是此小提琴.它可以根据我的要求工作，但是查询优化存在问题；它的执行时间不是我想要的.当我使用第二个小提琴查询时，由于数据量大，花了超过33秒的时间来加载页面(一点都不好).即使有大量数据，第一个小提琴中的查询也会在2秒内执行.如何(希望)保持快速运行而更正第一个查询以给出第二个查询的结果?

解决方案

读取left join on的定义.它返回inner join on所做的行以及左表中扩展了null s的不匹配行.

重新进行第一个链接查询:如果要获取user中的所有记录，则该记录必须是left join中最左边的表.您不得通过要求在where中使用非null值的测试来删除可能具有null值的行-但是，为什么呢?-即为什么要做?以下内容仅根据第二个链接查询返回角色为15的用户的行；您没有解释为什么15在第一次测试中.

select u.user_id,
    ut.name as team_name,
    /* ... */
    count(case when a.status = 2 AND a.qc_id = 0 and o.class_id= 3 then 1 else null end) 
    AS assigned_canvass
from am_user u
left join user_team ut
on u.user_team_id = ut.user_team_id
left join am_ts_assignment a
on u.user_id = a.tech_id
left join am_ts_order o
on o.assignment_id = a.assignment_id
where u.user_role_id = 15
group by u.user_id
order by u.user_id asc

逐步组成查询，并在添加联接&时进行测试.列.

(这是我在您的上一个帖子中给出的查询，本质上是您的第一个链接查询，只是它为每个am_user而不是每个am_ts_assignment返回一行，每个帖子都返回一行.where可能是一个and与am_user一起出现在第一个left join中；这两个都可以.

I have made an sql fiddle. Users with id 100 and 118 should have 0 records for assigned_scopes, assigned_qa, failed_qa and assigned_canvass. But it does not display that data; it just shows the record for the user with id 210. What I really need is to display all the users, with 0 in each column if they have nothing. How can I do this?

What I have tried since is in this fiddle. It works according to my requirements but there is problem with query optimization; its execution time is not what I want. It took more than 33 seconds to load the page (which is not good at all) when I used the second fiddle query because of the huge data. The query in the first fiddle executed in 2 seconds even with huge data. How can correct the first query to give the results of the second query while (hopefully) staying fast?

解决方案

Read a definition of left join on. It returns rows that inner join on does plus unmatched rows in the left table extended by nulls.

Re your 1st link query: If you want all the records from user then it must be the leftmost table in your left joins. You must not remove rows that might have null values by tests requiring non-null values in where--but, why would you?--ie, why do you? The following returns rows only for users with role 15, per the 2nd link query; you don't explain why 15 tested for in the 1st.

select u.user_id,
    ut.name as team_name,
    /* ... */
    count(case when a.status = 2 AND a.qc_id = 0 and o.class_id= 3 then 1 else null end) 
    AS assigned_canvass
from am_user u
left join user_team ut
on u.user_team_id = ut.user_team_id
left join am_ts_assignment a
on u.user_id = a.tech_id
left join am_ts_order o
on o.assignment_id = a.assignment_id
where u.user_role_id = 15
group by u.user_id
order by u.user_id asc

Compose your queries incrementally and test as you add joins & columns.

(This is the same query I gave on your last post re essentially your 1st link query except it returns a row for every am_user instead of every am_ts_assignment, per each post. The where might have been an and in the first left join with am_user; either will do here.)

这篇关于如何在mysql中使用左外部联接以获得我想要的结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！