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

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

For example:

Field a in table A has 40,000 pieces of data
Field a in table B has 60,000 40,000 pieces of data are the same as table A. How can we query the 20,000 different pieces of data?

--Create tables table1, table2:

create   table   table1(id   int,name   varchar(10));   
create   table   table2(id   int,score   int);   
insert   into   table1   select   '1','lee';
insert   into   table1   select   '2','zhang';
insert   into   table1   select   '3','steve';
insert   into   table1   select   '4','wang';   
insert   into   table2   select   '1','90';   
insert   into   table2   select   '2','100';   
insert   into   table2   select   '3','70';

Such as table

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

table1
------ ---------------------------------------------
id name
1 lee
2 zhang

3 steve

4 wang

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

table2

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

id score

1 90
2 100

3 70

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

(1) The result set of the left outer join includes the left specified in the left outer clause All rows of the table, not just the rows matched by the join column. If a row in the left table has no matching row in the right table, all select list columns of the right table will be null in the associated result set row.

(2)sql statement

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

3 steve 3 70------------- Result------------- id name id score
-------------------------- ----
1 lee 1 90
2 zhang 2 100

4 wang null null

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

Note: Contains all clauses of table1, returns the corresponding fields of table2 according to the specified conditions, and displays null if they do not meet the requirements

(3) Then get the difference

* 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 null

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

The following is the actual situation encountered at work:

##Filter out 0 salespeople (that is, a list of employee information without sales records).

#Salesperson (user role intermediate table)

1

userid from bbscs_role_user where roleid = 'sales'

---> 11 records

# Statistics table (user sales record table)

1

# ---> 4 records

The requirements are: the records of the other 7 sales staff are listed for the purpose.

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

#This is the SQL statement model BEGINisnull

#1select * from b t2 left join a t1 on t1.a1 = t2.b1 WHERE

##This is the SQL statement model END##Query the SQL return result as a temporary tablet2.userid = t1.refid WHERE

123select * from (select userid from
where roleid = 'sales') t2 left join (select refid from bbscs_sales_income_stat
type = 4 and month = '2012-02' and amount != 0) t1

###t1.refid ###### is### ###null#####################

# --->7 records

Test 1:

##SQL statement, Mysql queries different values ​​(mainly differences) in two tables. There is still a problem with this statement query.

##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 ;

#Tables and tables, conditions and conditions are independent.

# --->18 records

Test 2:

##where or and The difference

# --->22 records

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

## is associated with the user department intermediate table and is displayed sorted by department ID.

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

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

###

The above is the detailed content of How to query different data values ​​of the same field in two tables. For more information, please follow other related articles on the PHP Chinese website!