Mysql left join query, comparing two different rows in the table, mysqljoin

Source: Internet
Author: User

Mysql left join query, comparing two different rows in the table, mysqljoin

How to query different data values of the same field in two tables

For example:

Field A in Table a contains 40000 data records.
Field a in Table B has 60000 data records, of which 40000 are the same as that in table.
How can we query 20000 different pieces of data?

 

-- Create Table table1, table2:

123456789 createtabletable1(id   int,namevarchar(10));   createtabletable2(id   int,score   int);   insertintotable1   select'1','lee';insertintotable1   select'2','zhang';insertintotable1   select'3','steve';insertintotable1   select'4','wang';   insertintotable2   select'1','90';   insertintotable2   select'2','100';   insertintotable2   select'3','70';

 

Such as table
-------------------------------------------------
Table1
-------------------------------------------------
Id name
1 lee
2 zhang

3 steve
4 wang

-------------------------------------------------

Table 2

-------------------------------------------------

Id score
1 90
2 100

3 70

-------------------------------------------------

 

(1) The result set of the left outer Join includes all rows in the left table specified in the left outer clause, not just the rows matched by the join column. If a row in the left table does not match a row in the right table, all the selection list columns in the right table in the row of the associated result set are null ).

(2) SQL statements

1 select from table1 t1 left join table2 t2 on t1.id = t2.id

 

------------- Result -------------
Id name id score
------------------------------
1 lee 1 90
2 zhang 2 100

3 steve 3 70
4 wang null
------------------------------

Note: all the clauses containing Table 1 return the corresponding fields of Table 2 based on the specified conditions. The non-conforming fields are displayed as null.

(3) obtain the difference value.

1 select from table1 t1 left join table2 t2 on t1.id = t2.id WHERE t2.id is null

 

------------- Result -------------
Id name id score

4 wang null
------------------------------

 

The following are the actual situations in the work:

# Filter out 0 Sales personnel (that is, the list of employees without sales records ).

# Sales personnel (User Role intermediate table)

1 select userid from bbscs_role_user where roleid = 'sales'

 

# ---> 11 records

 

# Statistical table (user sales record table)

1 select refid from bbscs_sales_income_stat where type = 4 and month '2012-02' and amount != 0

 

# ---> 4 records

Requirement: the record of the other 7 sales personnel is listed for the purpose.

 

######### This is the SQL statement model BEGIN ##########

1 select from b t2 left join a t1 on t1.a1 = t2.b1 WHERE t1.a1 is null

 

######### This is the SQL statement model END ############

Note: The left table is the table with more data (such as table B ). Left join query. The where condition is that a field (a1) in the table (table a) on the right is Null)

 

# Use the SQL return result as a temporary table for query

123 select from (select userid from bbscs_role_user where roleid = 'sales') t2 left join (select refid from bbscs_sales_income_stat where type = 4 and month '2012-02' and amount != 0) t1 on t2.userid = t1.refid WHERE t1.refid is null

 

# ---> 7 records

 

 

Test 1:

# SQL statement: mysql queries different values (mainly Difference values) in two tables. This statement still has problems.

12 select t1.Userid from bbscs_role_user t1 left join bbscs_sales_income_stat t2 on t1.userid = t2.refid and t1.roleid = 'sales' and t2.type = 4 and t2.month '2012-02' and t2.amount != 0 where t2.id is null;

# Table and table. The conditions and conditions are independent.

# ---> 18 Records

 

 

Test 2:

12 select t1.Userid from bbscs_role_user t1 left join bbscs_sales_income_stat t2 on t1.userid = t2.refid and t1.roleid = 'sales' and t2.type = 4 and t2.month '2012-02' and t2.amount != 0 and t2.id is null

# Difference between where or and

# ---> 22 records

 

 

### The more powerful temporary table query function puts the above query results as a whole.

# Associate with the user department intermediate table, which is displayed in the order of Department IDs.

 

123 select t4.userid fromselect from (select userid from bbscs_role_user where roleid = 'sales') t2 left join (select refid from bbscs_sales_income_stat where type = 4 and month '2012-02' and amount != 0) t1 on t2.userid = t1.refid WHERE t1.refid is null ) t3, bbscs_org_user t4 where t3.userid = t4.userid order by orgId

Https://www.zksfyz.com/detail/99/

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.