日期:2014-05-17 浏览次数:20650 次
--user 表
CREATE TABLE tb_user
(
userid INT IDENTITY NOT NULL PRIMARY KEY,
username NVARCHAR(100) --张三,李四
)
--兴趣爱好表
CREATE TABLE tb_savour
(
savourid INT IDENTITY NOT NULL PRIMARY KEY,
savourname NVARCHAR(100) --羽毛球、兵乓球 、足球、篮球、健身、自行车、
)
--关系表
CREATE TABLE tb_userSavour
(
id INT IDENTITY NOT NULL PRIMARY KEY,
userid INT NOT NULL,
savourid INT NOT NULL
)
--保存某人兴趣爱好
INSERT INTO tb_userSavour(userid, savourid) VALUES(1, 2)
INSERT INTO tb_userSavour(userid, savourid) VALUES(1, 3)
INSERT INTO tb_userSavour(userid, savourid) VALUES(1, 4)
--查询某人兴趣爱好
SELECT a.userid,a.username,c.savourname
FROM dbo.tb_user a
LEFT JOIN dbo.tb_userSavour b
ON a.userid = b.userid
LEFT JOIN dbo.tb_savour c
ON b.savourid = c.savourid
WHERE a.username = '张三'