有趣的电影
1、题目:
某城市开了一家新的电影院,吸引了很多人过来看电影。该电影院特别注意用户体验,专门有个 LED显示板做电影推荐,上面公布着影评和相关电影描述。
作为该电影院的信息部主管,您需要编写一个 SQL查询,找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数 的影片,结果请按等级 rating 排列。
例如,下表 cinema:
+---------+-----------+--------------+-----------+
| id | movie | description | rating |
+---------+-----------+--------------+-----------+
| 1 | War | great 3D | 8.9 |
| 2 | Science | fiction | 8.5 |
| 3 | irish | boring | 6.2 |
| 4 | Ice song | Fantacy | 8.6 |
| 5 | House card| Interesting| 9.1 |
+---------+-----------+--------------+-----------+
对于上面的例子,则正确的输出是为:
+---------+-----------+--------------+-----------+
| id | movie | description | rating |
+---------+-----------+--------------+-----------+
| 5 | House card| Interesting| 9.1 |
| 1 | War | great 3D | 8.9 |
+---------+-----------+--------------+-----------+
2、解题步骤:
(1) 创建表:
CREATE TABLE `cinema` (
`Id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Id',
`movie` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '电影',
`description` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '说明',
`rating` double DEFAULT 0 COMMENT '收视率',
PRIMARY KEY (`Id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '电影表' ROW_FORMAT = Dynamic;
(2) 插入数据:
INSERT INTO `cinema` VALUES (1, 'War', 'great 3D', 8.9);
INSERT INTO `cinema` VALUES (2, 'Science', 'fiction', 8.5);
INSERT INTO `cinema` VALUES (3, 'irish', 'boring', 6.2);
INSERT INTO `cinema` VALUES (4, 'Ice song', 'Fantacy', 8.6);
INSERT INTO `cinema` VALUES (5, 'House card', 'Interesting', 9.1);
(3) 查询SQL:
# 按位与
SELECT * FROM cinema WHERE description != "boring" AND Id&1 ORDER BY rating DESC;
# Id先除以2然后乘以2,如果与原来的不相等就是奇数,否则为偶数
SELECT * FROM cinema WHERE description != "boring" AND Id!=(Id>>1)<<1 ORDER BY rating DESC;
# 正则匹配最后一位,奇数[13579],偶数[02468]
SELECT * FROM cinema WHERE description != "boring" AND Id REGEXP '[13579]' ORDER BY rating DESC;
# Id计算,Id%2=1为奇数,Id%2=0为偶数
SELECT * FROM cinema WHERE description != "boring" AND Id%2=1 ORDER BY rating DESC;
# mod(Id, 2)=1为奇数,mod(Id, 2)=0位偶数
SELECT * FROM cinema WHERE description != "boring" AND MOD(Id, 2)=1 ORDER BY rating DESC;
# -1的奇次方和偶次方
SELECT * FROM cinema WHERE description != "boring" AND POWER(-1,Id)=-1 ORDER BY rating DESC;
(4) 结果: