返回顶部

收藏

mysql 在where语句中使用XOR

更多
/*
mysql> Drop table Books;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE Books
    -> (
    ->    ID SMALLINT NOT NULL PRIMARY KEY,
    ->    Name VARCHAR(40) NOT NULL,
    ->    Category VARCHAR(15),
    ->    InStock SMALLINT NOT NULL,
    ->    OnOrder SMALLINT NOT NULL
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO Books
    -> VALUES (101, 'On', 'Nonfiction', 13, 11),
    ->        (102, 'News', 'Fiction', 15, 21),
    ->        (103, 'Hello', 'Nonfiction', 21, 32),
    ->        (104, 'Poet', 'Nonfiction', 35, 13),
    ->        (105, 'Dunces', 'Fiction', 5, 35),
    ->        (106, 'One', 'Fiction', 28, 14),
    ->        (107, 'From', NULL, 46, 31);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from Books;
+-----+--------+------------+---------+---------+
| ID  | Name   | Category   | InStock | OnOrder |
+-----+--------+------------+---------+---------+
| 101 | On     | Nonfiction |      13 |      11 |
| 102 | News   | Fiction    |      15 |      21 |
| 103 | Hello  | Nonfiction |      21 |      32 |
| 104 | Poet   | Nonfiction |      35 |      13 |
| 105 | Dunces | Fiction    |       5 |      35 |
| 106 | One    | Fiction    |      28 |      14 |
| 107 | From   | NULL       |      46 |      31 |
+-----+--------+------------+---------+---------+
7 rows in set (0.00 sec)

mysql> SELECT Name, Category, InStock, OnOrder
    -> FROM Books
    -> WHERE Category='Fiction' XOR InStock IS NULL
    -> ORDER BY Name;
+--------+----------+---------+---------+
| Name   | Category | InStock | OnOrder |
+--------+----------+---------+---------+
| Dunces | Fiction  |       5 |      35 |
| News   | Fiction  |      15 |      21 |
| One    | Fiction  |      28 |      14 |
+--------+----------+---------+---------+
3 rows in set (0.00 sec)

*/
Drop table Books;

CREATE TABLE Books
(
   ID SMALLINT NOT NULL PRIMARY KEY,
   Name VARCHAR(40) NOT NULL,
   Category VARCHAR(15),
   InStock SMALLINT NOT NULL,
   OnOrder SMALLINT NOT NULL
);

INSERT INTO Books
VALUES (101, 'On', 'Nonfiction', 13, 11),
       (102, 'News', 'Fiction', 15, 21),
       (103, 'Hello', 'Nonfiction', 21, 32),
       (104, 'Poet', 'Nonfiction', 35, 13),
       (105, 'Dunces', 'Fiction', 5, 35),
       (106, 'One', 'Fiction', 28, 14),
       (107, 'From', NULL, 46, 31);

select * from Books;

SELECT Name, Category, InStock, OnOrder
FROM Books
WHERE Category='Fiction' XOR InStock IS NULL
ORDER BY Name;

标签:SQL,MySQL

收藏

0人收藏

支持

0

反对

0