(1) Implement result set a-result set B: -- use not
Exists. Union is available for merging.
Exists, not
Exists: used to determine whether or not result set a exists in result set B!
=========== Result set:
Select
D. device_id, D. device_name, A. app_id, A. app_name
From
T_device_baseinfo D, t_app_spce_r_info da, t_app_info
Where D. spec_code = da. spec_code and DA. app_id = A. app_id and
D. device_id = 01;
+ ----------- + ------------- + -------- + ---------------- +
|
Device_id | device_name | app_id | app_name |
+ ----------- + ------------- + -------- + ---------------- +
|
01 | coship01 | 01 | maliao |
| 01 |
Coship01 | 02 | maliao |
| 01 | coship01 |
03 | red alert |
| 01 | coship01 | 05 |
Cod |
| 01 | coship01 | 08 | flashplayer |
|
01 | coship01 | 09 | flashplayer6.0 |
+ ----------- + ------------- + -------- + ---------------- +
6 rows in SET (0.00 Sec)
=========== Result set B:
Mysql> select * From t_device_app_r_info
Where device_id = 01;
+ ---- + ----------- + -------- + ------ + ------------ +
|
Id | device_id | app_id | OPR | setup_date |
+ ---- + ----------- + -------- + ------ + ------------ +
|
64 | 01 | 01 | 2 | null |
| 65 | 01 |
03 | 2 | null |
+ ---- + ----------- + -------- + ------ + ------------ +
2 rows in SET (0.00 Sec)
=========== Result set a-result set B:
Select
D. device_id, D. device_name, A. app_id, A. app_name
From
T_device_baseinfo D, t_app_spce_r_info da, t_app_info
Where
D. spec_code = da. spec_code and DA. app_id = A. app_id and D. device_id = 01
And
Not exists (select * From t_device_app_r_info TT where
TT. device_id = D. device_id and A. app_id = TT. app_id );
+ ----------- + ------------- + -------- + ---------------- +
|
Device_id | device_name | app_id | app_name |
+ ----------- + ------------- + -------- + ---------------- +
|
01 | coship01 | 02 | maliao |
| 01 |
Coship01 | 05 | cod |
| 01 | coship01 |
08 | flashplayer |
| 01 | coship01 | 09 |
Flashplayer6.0 |
+ ----------- + ------------- + -------- + ---------------- +
4 rows in SET (0.00 Sec)
To obtain results that only exist in the result set, you only need to change not exists to exists.
(2) Use all for subquery-MySQL database
The All keyword must be followed by a comparison operator.
All means "compare all values in the column returned by the subquery. If the comparison result is true, return
True ." For example:
Select S1 from T1 where S1> All (select S1 from T2 );
Assume that Table 1 contains
The row contains (10 ).
If table T2 contains (-5, 0, + 5), the expression is true because 10 is greater than all three values in Table T2.
If table T2 contains
(100, null,-), the expression is false, because the value of 12 in Table T2 is greater than 10.
If table T2 contains (0, null, 1), the expression is
Unknown.
Not in is the alias of <> All. Therefore, the following two statements are the same:
Select S1 from T1 where S1
<> All (select S1 from T2 );
Select S1 from T1 where S1 not in
(Select S1 from T2 );
(3) use any for subquery-MySQL database
The any keyword must be followed by a comparison operator (I .e. >,<,= ). The "any" keyword means "any value in the column returned by the subquery, for example:
Select S1 from T1 where S1> Any (select S1 from T2 );
Assume that table T1 contains
A row contains (10 ). If table T2 contains (, 14, 7), the expression is true because one value in Table T2 is 7 and the value is less than 10. If table T2 contains (20, 10), or
If table T2 is empty, the expression is false. If table T2 contains (null, null, null), the expression is unknown.
The word in is the alias of "= any. Therefore, the two statements are the same:
Select S1 from T1 where S1 = any (select S1 from T2 );
Select S1
From T1 where S1 in (select S1 from T2 );
(4) MySQL comparison operator: <>=<=<>. It should also be available! =.
But it is not used for multi-condition queries, and the query results will be incorrect. For example:
Select D. device_id, D. device_name, A. app_id, A. app_name, A. app_memo
From t_device_baseinfo D,
T_app_spce_r_info Da,
T_App_Info,
T_Device_App_R_Info tt
WHERE d. Spec_Code = da. Spec_Code
AND
Da. App_ID = a. App_ID
And d. Device_ID = 01
And d. Device_ID! = Tt. Device_ID and
A. App_ID! = Tt. App_ID (here );
This article is from "Changes we need!
"
Blog, please be sure to keep this source http://shenzhenchufa.blog.51cto.com/730213/269152
