有一个人员信息表user_info(user_id,name,dept_id,amt)、部门信息表dept_info(dept_id,dept_desc),人员功夫表user_kongfu(user_id,kf_id,main_kf_flag)和功夫信息代码表kf_info(kf_id,kf_desc)表中的数据分别如下:
人员信息表:
用户ID | 姓名 | 部门ID | 个人资产(金额) |
1001 | 令狐冲 | 1 | 70 |
1002 | 任我行 | 2 | 0 |
1003 | 东方不败 | 3 | 528 |
1004 | 风清阳 | 1 | 10 |
1005 | 左冷禅 | 4 | 230 |
1006 | 冲虚 | 3 | 50 |
1007 | 岳不群 | 1 | 158 |
1008 | 天门道人 | 5 | -10 |
部门信息表:
部门ID | 部门名称 |
1 | 华山 |
2 | 黑木崖 |
3 | 武当 |
4 | 嵩山 |
5 | 泰山 |
人员功夫表(1是主功夫):
用户ID | 功夫ID | 主功夫标志 |
1001 | 1 | 1 |
1001 | 2 | 0 |
1001 | 4 | 0 |
1002 | 4 | 1 |
1003 | 3 | 1 |
1004 | 1 | 1 |
1005 | 5 | 1 |
1006 | 7 | 1 |
1007 | 2 | 0 |
1007 | 3 | 1 |
1008 | 6 | 0 |
功夫信息表:
功夫ID | 功夫描述 |
1 | 孤独九剑 |
2 | 紫霞神功 |
3 | 辟邪剑法 |
4 | 吸星大法 |
5 | 寒冰掌 |
6 | 三脚猫 |
7 | 太极剑法 |
其中,五岳剑派为华山、嵩山和泰山。
问:请给出SQL统计有主功夫没有次功夫的人员名单以及部门名称、功夫描述和个人资产。
注意审题!!!
请给出SQL统计有主功夫没有次功夫的人员名单以及部门名称、功夫描述和个人资产
使用sql语句的子查询方法,用not in 剔除含有次功夫的id就行了
这才是正确答案!!!(文末附上SQL创建代码,直接运行即可)
SELECT user_name,dept_desc,kf_desc,amt FROM
user_info INNER JOIN dept_info ON user_info.dept_id=dept_info.dept_id
INNER JOIN user_kongfu ON user_info.user_id=user_kongfu.user_id
INNER JOIN kf_info ON user_kongfu.main_kf_flag=kf_info.kf_id
WHERE user_kongfu.user_id
NOT IN (SELECT user_id FROM user_kongfu WHERE user_kongfu.main_kf_flag=0)
附上创建SQL的语句
CREATE DATABASE test3 CHARACTER SET UTF8;
CREATE TABLE user_info(user_id INT PRIMARY KEY,user_name VARCHAR(10),dept_id INT,amt DOUBLE);
CREATE TABLE dept_info(dept_id INT PRIMARY KEY,dept_desc VARCHAR(10));
CREATE TABLE user_kongfu(user_id INT,kf_id INT,main_kf_flag INT);
CREATE TABLE kf_info(kf_id INT,kf_desc VARCHAR(10));
INSERT INTO user_info(user_id,user_name,dept_id,amt) VALUES (1001,'令狐冲',1,70);
INSERT INTO user_info(user_id,user_name,dept_id,amt) VALUES (1002,'任我行',2,0);
INSERT INTO user_info(user_id,user_name,dept_id,amt) VALUES (1003,'东方不败',2,528);
INSERT INTO user_info(user_id,user_name,dept_id,amt) VALUES (1004,'风清阳',1,10);
INSERT INTO user_info(user_id,user_name,dept_id,amt) VALUES (1005,'左冷禅',4,230);
INSERT INTO user_info(user_id,user_name,dept_id,amt) VALUES (1006,'冲虚',3,50);
INSERT INTO user_info(user_id,user_name,dept_id,amt) VALUES (1007,'岳不群',1,158);
INSERT INTO user_info(user_id,user_name,dept_id,amt) VALUES (1008,'天门道人',5,-10);
INSERT INTO dept_info(dept_id,dept_desc) VALUES (1,'华山');
INSERT INTO dept_info(dept_id,dept_desc) VALUES (2,'黑木崖');
INSERT INTO dept_info(dept_id,dept_desc) VALUES (3,'武当');
INSERT INTO dept_info(dept_id,dept_desc) VALUES (4,'嵩山');
INSERT INTO dept_info(dept_id,dept_desc) VALUES (5,'泰山');
INSERT INTO user_kongfu(user_id,kf_id,main_kf_flag) VALUES (1001,1,1);
INSERT INTO user_kongfu(user_id,kf_id,main_kf_flag) VALUES (1001,2,0);
INSERT INTO user_kongfu(user_id,kf_id,main_kf_flag) VALUES (1001,4,0);
INSERT INTO user_kongfu(user_id,kf_id,main_kf_flag) VALUES (1002,4,1);
INSERT INTO user_kongfu(user_id,kf_id,main_kf_flag) VALUES (1003,3,1);
INSERT INTO user_kongfu(user_id,kf_id,main_kf_flag) VALUES (1004,1,1);
INSERT INTO user_kongfu(user_id,kf_id,main_kf_flag) VALUES (1005,5,1);
INSERT INTO user_kongfu(user_id,kf_id,main_kf_flag) VALUES (1006,7,1);
INSERT INTO user_kongfu(user_id,kf_id,main_kf_flag) VALUES (1007,2,0);
INSERT INTO user_kongfu(user_id,kf_id,main_kf_flag) VALUES (1007,3,1);
INSERT INTO user_kongfu(user_id,kf_id,main_kf_flag) VALUES (1008,6,0);
INSERT INTO kf_info(kf_id,kf_desc) VALUES (1,'独孤九剑');
INSERT INTO kf_info(kf_id,kf_desc) VALUES (2,'紫霞神功');
INSERT INTO kf_info(kf_id,kf_desc) VALUES (3,'辟邪剑法');
INSERT INTO kf_info(kf_id,kf_desc) VALUES (4,'吸星大法');
INSERT INTO kf_info(kf_id,kf_desc) VALUES (5,'寒冰掌');
INSERT INTO kf_info(kf_id,kf_desc) VALUES (6,'三角猫');
INSERT INTO kf_info(kf_id,kf_desc) VALUES (7,'太极剑法');