文章表分类是一篇文章可分属于多个分类的,现在用REGEXP去获取2个类别的文章。SELECT * FROM article WHERE category_id REGEXP '3|5' ORDER BY id DESC;有什么办法得知结果行是属于哪个分类的?
就是说得出的行数是匹配上面的3还是5的,然后可根据这个去获得类别名字。
-- 查询结果:
id category_id title posted_date
8 1,3,6 title8 2012/5/27 17:21:20
6 2,5,8 title6 2012/2/27 17:17:28
4 2,3,7 title4 2012/1/22 21:07:35
1 1,3,6 title1 2012/1/20 21:07:35
-- 期待结果:
id category_id category_name title posted_date
8 1,3,6 分类3 title8 2012/5/27 17:21:20
6 2,5,8 分类5 title6 2012/2/27 17:17:28
4 2,3,7 分类3 title4 2012/1/22 21:07:35
1 1,3,6 分类3 title1 2012/1/20 21:07:35
article:-- ----------------------------
-- Table structure for `article`
-- ----------------------------
DROP TABLE IF EXISTS `article`;
CREATE TABLE `article` (
`id` int(8) NOT NULL AUTO_INCREMENT,
`category_id` varchar(32) NOT NULL,
`title` varchar(50) NOT NULL,
`posted_date` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of article
-- ----------------------------
INSERT INTO `article` VALUES ('1', '1,3,6', 'title1', '2012-01-20 21:07:35');
INSERT INTO `article` VALUES ('2', '1,4,6', 'title2', '2012-01-21 21:07:35');
INSERT INTO `article` VALUES ('3', '1,4,8', 'title3', '2012-01-21 22:07:35');
INSERT INTO `article` VALUES ('4', '2,3,7', 'title4', '2012-01-22 21:07:35');
INSERT INTO `article` VALUES ('5', '1,4,9', 'title5', '2012-01-23 21:07:35');
INSERT INTO `article` VALUES ('6', '2,5,8', 'title6', '2012-02-27 17:17:28');
INSERT INTO `article` VALUES ('7', '1,4,8', 'title7', '2012-02-27 17:19:58');
INSERT INTO `article` VALUES ('8', '1,3,6', 'title8', '2012-05-27 17:21:20');
INSERT INTO `article` VALUES ('9', '2,4,6', 'title9', '2012-05-28 17:22:35');
INSERT INTO `article` VALUES ('10', '2,6,9', 'title10', '2012-05-29 17:23:04');
category-- ----------------------------
-- Table structure for `category`
-- ----------------------------
DROP TABLE IF EXISTS `category`;
CREATE TABLE `category` (
`category_id` int(8) NOT NULL AUTO_INCREMENT,
`category_name` varchar(16) NOT NULL,
PRIMARY KEY (`category_id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of category
-- ----------------------------
INSERT INTO `category` VALUES ('1', '分类1');
INSERT INTO `category` VALUES ('2', '分类2');
INSERT INTO `category` VALUES ('3', '分类3');
INSERT INTO `category` VALUES ('4', '分类4');
INSERT INTO `category` VALUES ('5', '分类5');
INSERT INTO `category` VALUES ('6', '分类6');
INSERT INTO `category` VALUES ('7', '分类7');
INSERT INTO `category` VALUES ('8', '分类8');
INSERT INTO `category` VALUES ('9', '分类9');
如果是 3,5 那你的REGEXP '3|5' OR准备如何显示?
+----+-------------+---------+---------------------+
| id | category_id | title | posted_date |
+----+-------------+---------+---------------------+
| 1 | 1,3,6 | title1 | 2012-01-20 21:07:35 |
| 2 | 1,4,6 | title2 | 2012-01-21 21:07:35 |
| 3 | 1,4,8 | title3 | 2012-01-21 22:07:35 |
| 4 | 2,3,7 | title4 | 2012-01-22 21:07:35 |
| 5 | 1,4,9 | title5 | 2012-01-23 21:07:35 |
| 6 | 2,5,8 | title6 | 2012-02-27 17:17:28 |
| 7 | 1,4,8 | title7 | 2012-02-27 17:19:58 |
| 8 | 1,3,6 | title8 | 2012-05-27 17:21:20 |
| 9 | 2,4,6 | title9 | 2012-05-28 17:22:35 |
| 10 | 2,6,9 | title10 | 2012-05-29 17:23:04 |
+----+-------------+---------+---------------------+
10 rows in set (0.00 sec)mysql> select * from category;
+-------------+---------------+
| category_id | category_name |
+-------------+---------------+
| 1 | 分类1 |
| 2 | 分类2 |
| 3 | 分类3 |
| 4 | 分类4 |
| 5 | 分类5 |
| 6 | 分类6 |
| 7 | 分类7 |
| 8 | 分类8 |
| 9 | 分类9 |
+-------------+---------------+
9 rows in set (0.00 sec)mysql> select a.id,a.category_id,b.category_name,a.title ,a.posted_date
-> from article a, (SELECT * FROM category WHERE category_id REGEXP '3|5') b
-> where find_in_set(b.category_id,a.category_id)
-> group by a.id
-> ORDER BY id DESC;
+----+-------------+---------------+--------+---------------------+
| id | category_id | category_name | title | posted_date |
+----+-------------+---------------+--------+---------------------+
| 8 | 1,3,6 | 分类3 | title8 | 2012-05-27 17:21:20 |
| 6 | 2,5,8 | 分类5 | title6 | 2012-02-27 17:17:28 |
| 4 | 2,3,7 | 分类3 | title4 | 2012-01-22 21:07:35 |
| 1 | 1,3,6 | 分类3 | title1 | 2012-01-20 21:07:35 |
+----+-------------+---------------+--------+---------------------+
4 rows in set (0.06 sec)mysql>
实在是强!group by a.id 这句的作用是什么?a表的id是自增id,不会有重复的,这句是否可去掉?
SELECT * FROM article WHERE category_id NOT REGEXP '^3|5' ORDER BY id DESC LIMIT 12;