MySQL Query in operation, query results in set order display
Copy Code code as follows:
SELECT * from test where ID into (3,1,5) Order by Find_in_set (ID, ' 3,1,5 ');
SELECT * from test where ID into (3,1,5) Order by Substring_index (' 3,1,2 ', id,1);
The occasional sight of ... Maybe someone would have noticed, but I didn't know that before.
Sql:select * FROM table where ID in (3,6,9,1,2,5,8,7);
This situation is taken out, in fact, id or press 1,2,3,4,5,6,7,8,9, sorted, but if we really want to press in the order in which to do? Can SQL be completed? Do you need to take it back and have a foreach? In fact, MySQL has this method
Sql:select * FROM table where ID in (3,6,9,1,2,5,8,7) Order by field (id,3,6,9,1,2,5,8,7);
The order in which they come out is in the order specified .... This, has been really never used, occasionally see, so on record. One is to make a note, the second is hope can give more people see
The handling of NULL value in the not in statement in MySQL
mysql> SELECT COUNT (name) from CVE WHERE name isn't in (' cve-1999-0001 ', ' cve-1999-0002 ');
+-------------+
| COUNT (name) |
+-------------+
| 17629 |
+-------------+
1 row in Set (0.02 sec)
mysql> SELECT COUNT (name) from CVE WHERE name isn't in (' cve-1999-0001 ', ' cve-1999-0002 ', NULL);
+-------------+
| COUNT (name) |
+-------------+
| 0 |
+-------------+
1 row in Set (0.01 sec)
When NULL is present in the subquery, the result must be 0. I looked up the manual, and I do have this statement. So, finally, we actually used this query:
SELECT COUNT (DISTINCT name)
From CVE
Where name isn't in (SELECT Cveid from Cve_sig where Cveid isn't NULL)
By the way, the simple use of MySQL's regular expression matching:
SELECT COUNT (Alarmid)
From Alarm
WHERE (CVE not rlike ' ^cve-[0-9]{4}-[0-9]{4}$ ' OR CVE is NULL)
Of course, Rlike can also write regexp, I personally tend to use rlike, because the spelling is close to like, you can see known righteousness.
Mysql-not in
Table:info primary KEY (ID, info_type_id)
IDs, info_type_id, programme_id, episode_id
3, 4, 382, 100034.
3, 8, 382, 100034.
4, 8, 382, 100034.
6, 8, 382, 100034.
7, 8, 382, 100034.
8, 8, 382, 100034.
9, 8, 382, 100034.
10, 8, 382, 100034.
11, 8, 382, 100034.
12, 8, 382, 100034.
13, 8, 382, 100034.
100001, 4, 382, 100034.
100002, 4, 382, 100034.
Exclude (id=3 && info_type_id=8) and (id=4 && info_type_id=8) These two records, that is, to find other records
Error:select * from info where episode_id=100034 and ID not in (3,4) and info_type_id not in (8);
Error Result:
IDs, info_type_id, programme_id, episode_id
100001, 4, 382, 100034.
100002, 4, 382, 100034.
Correct:select * from info where episode_id=100034 and (id<>3 or info_type_id<>8) and (Id<>4 or info_ty PE_ID<>8);
Correct result:
IDs, info_type_id, programme_id, episode_id
3, 4, 382, 100034.
6, 8, 382, 100034.
7, 8, 382, 100034.
8, 8, 382, 100034.
9, 8, 382, 100034.
10, 8, 382, 100034.
11, 8, 382, 100034.
12, 8, 382, 100034.
13, 8, 382, 100034.
100001, 4, 382, 100034.
100002, 4, 382, 100034.
Understanding: Id<>3 or Info_type_id<>8 excludes the id=3 && info_type_id=8 record, when there are more than one main key in the table, it is not easy to use key1 not in (...) and Key2 not in (...).
