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

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 from( 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 ) t3, bbscs_org_user t4 where t3.userid = t4.userid order by orgId

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