以下是测试脚本:
create table TEXT
(
TEXT_ID INTEGER,
CW_ID INTEGER,
TEXT_MARK INTEGER,
TEXT_VOTER INTEGER
)
;
CREATE TABLE tag (
tag_id int(3) NOT NULL auto_increment,
tag_name varchar(20) NOT NULL,
PRIMARY KEY (tag_id,tag_name)
) ;
CREATE TABLE texttagmap (
texttagmap_id int(3) NOT NULL auto_increment,
text_id int(5) default NULL,
tag_id int(3) default NULL,
PRIMARY KEY (texttagmap_id),
);
//text_id 和tag_id分别是外键.
insert into TEXT (TEXT_ID, CW_ID, TEXT_MARK, TEXT_VOTER)
values (34, 1, 66, 2);
insert into TEXT (TEXT_ID, CW_ID, TEXT_MARK, TEXT_VOTER)
values (35, 8, 77, 3);
insert into TEXT (TEXT_ID, CW_ID, TEXT_MARK, TEXT_VOTER)
values (36, 1, 88, 5);
insert into TEXT (TEXT_ID, CW_ID, TEXT_MARK, TEXT_VOTER)
values (37 1, 99, 7);
insert into TEXT (TEXT_ID, CW_ID, TEXT_MARK, TEXT_VOTER)
values (38, 8, 89, 4);
INSERT INTO tag VALUES (1,'PSP');
INSERT INTO tag VALUES (2,'NDS');
INSERT INTO tag VALUES (3,'PS2');
INSERT INTO tag VALUES (4,'PS3');
INSERT INTO tag VALUES (5,'Wii');
INSERT INTO tag VALUES (6,'XB360');
INSERT INTO tag VALUES (7,'文风华丽');
INSERT INTO tag VALUES (8,'文风淳朴');
INSERT INTO tag VALUES (9,'文风彪悍');
INSERT INTO tag VALUES (10,'系统');
INSERT INTO tag VALUES (11,'软件');
INSERT INTO tag VALUES (12,'杂谈');
INSERT INTO `texttagmap` VALUES (34,31,8);
INSERT INTO `texttagmap` VALUES (35,31,9);
INSERT INTO `texttagmap` VALUES (36,31,11);
INSERT INTO `texttagmap` VALUES (37,32,8);
INSERT INTO `texttagmap` VALUES (38,32,10);数据如下:
text:TEXT_ID CW_ID TEXT_MARK TEXT_VOTER
34 1 66 2
35 8 77 3
36 1 88 5
37 1 99 7
38 8 89 4 texttagmap:texttagmapid text_id tag_id
34 31 8
35 31 9
36 32 11
37 32 8
38 32 10我想求tag_name='psp','文风淳朴','系统'的所有文章;搜索出来的文章不要重复的.譬如说tag_id =8 tag_id = 9都是同一篇文章的tag,显示出来的只要一条结果.请各位前辈帮我看看要如何写查询语句啊,谢谢各位了!
create table TEXT
(
TEXT_ID INTEGER,
CW_ID INTEGER,
TEXT_MARK INTEGER,
TEXT_VOTER INTEGER
)
;
CREATE TABLE tag (
tag_id int(3) NOT NULL auto_increment,
tag_name varchar(20) NOT NULL,
PRIMARY KEY (tag_id,tag_name)
) ;
CREATE TABLE texttagmap (
texttagmap_id int(3) NOT NULL auto_increment,
text_id int(5) default NULL,
tag_id int(3) default NULL,
PRIMARY KEY (texttagmap_id),
);
//text_id 和tag_id分别是外键.
insert into TEXT (TEXT_ID, CW_ID, TEXT_MARK, TEXT_VOTER)
values (34, 1, 66, 2);
insert into TEXT (TEXT_ID, CW_ID, TEXT_MARK, TEXT_VOTER)
values (35, 8, 77, 3);
insert into TEXT (TEXT_ID, CW_ID, TEXT_MARK, TEXT_VOTER)
values (36, 1, 88, 5);
insert into TEXT (TEXT_ID, CW_ID, TEXT_MARK, TEXT_VOTER)
values (37 1, 99, 7);
insert into TEXT (TEXT_ID, CW_ID, TEXT_MARK, TEXT_VOTER)
values (38, 8, 89, 4);
INSERT INTO tag VALUES (1,'PSP');
INSERT INTO tag VALUES (2,'NDS');
INSERT INTO tag VALUES (3,'PS2');
INSERT INTO tag VALUES (4,'PS3');
INSERT INTO tag VALUES (5,'Wii');
INSERT INTO tag VALUES (6,'XB360');
INSERT INTO tag VALUES (7,'文风华丽');
INSERT INTO tag VALUES (8,'文风淳朴');
INSERT INTO tag VALUES (9,'文风彪悍');
INSERT INTO tag VALUES (10,'系统');
INSERT INTO tag VALUES (11,'软件');
INSERT INTO tag VALUES (12,'杂谈');
INSERT INTO `texttagmap` VALUES (34,31,8);
INSERT INTO `texttagmap` VALUES (35,31,9);
INSERT INTO `texttagmap` VALUES (36,31,11);
INSERT INTO `texttagmap` VALUES (37,32,8);
INSERT INTO `texttagmap` VALUES (38,32,10);数据如下:
text:TEXT_ID CW_ID TEXT_MARK TEXT_VOTER
34 1 66 2
35 8 77 3
36 1 88 5
37 1 99 7
38 8 89 4 texttagmap:texttagmapid text_id tag_id
34 31 8
35 31 9
36 32 11
37 32 8
38 32 10我想求tag_name='psp','文风淳朴','系统'的所有文章;搜索出来的文章不要重复的.譬如说tag_id =8 tag_id = 9都是同一篇文章的tag,显示出来的只要一条结果.请各位前辈帮我看看要如何写查询语句啊,谢谢各位了!
from texttagmap , tag,TEXT
where texttagmap.tag_id=tag.tag_id
and texttagmap.text_id=TEXT.text_id
and (tag.tag_name='psp'
or tag.tag_name='文风淳朴'
or tag.tag_name='系统')