本文共 1480 字，大约阅读时间需要 4 分钟。
逛stackoverflow的时候偶然看到一些MySQL查询的小技巧
来源： 例一：CREATE TABLE MovieList( ID INT, MovieName VARCHAR(25), CONSTRAINT ml_pk PRIMARY KEY (ID), CONSTRAINT ml_uq UNIQUE (MovieName));INSERT INTO MovieList VALUES (1, 'American Pie');INSERT INTO MovieList VALUES (2, 'The Notebook');INSERT INTO MovieList VALUES (3, 'Discovery Channel: Africa');INSERT INTO MovieList VALUES (4, 'Mr. Bean');INSERT INTO MovieList VALUES (5, 'Expendables 2');CREATE TABLE CategoryList( MovieID INT, CategoryName VARCHAR(25), CONSTRAINT cl_uq UNIQUE(MovieID, CategoryName), CONSTRAINT cl_fk FOREIGN KEY (MovieID) REFERENCES MovieList(ID));INSERT INTO CategoryList VALUES (1, 'Comedy');INSERT INTO CategoryList VALUES (1, 'Romance');INSERT INTO CategoryList VALUES (2, 'Romance');INSERT INTO CategoryList VALUES (2, 'Drama');INSERT INTO CategoryList VALUES (3, 'Documentary');INSERT INTO CategoryList VALUES (4, 'Comedy');INSERT INTO CategoryList VALUES (5, 'Comedy');INSERT INTO CategoryList VALUES (5, 'Action');
对于这样一个表，找到CategoryName同时为Romance和Comedy的电影
错误语句：
SELECT DISTINCT a.MovieNameFROM MovieList a INNER JOIN CategoryList b ON a.ID = b.MovieIDWHERE b.CategoryName = 'Comedy' AND b.CategoryName = 'Romance'
这样会返回零记录，因为每一条记录只有一个CategoryName，第一个条件返回true，第二个条件必然返回false，这样and连接起来会返回false。
另一个错误语句:SELECT DISTINCT a.MovieNameFROM MovieList a INNER JOIN CategoryList b ON a.ID = b.MovieIDWHERE b.CategoryName IN ('Comedy','Romance') 转载地址：http://ggzsi.baihongyu.com/