sql经典50题

  1. 表（MYSQL）
  2. Student(sid,Sname,Sage,Ssex) 学生表 
  3. CREATE TABLE student (
  4.   sid varchar(10) NOT NULL,
  5.   sName varchar(20) DEFAULT NULL,
  6.   sAge datetime DEFAULT '1980-10-12 23:12:36',
  7.   sSex varchar(10) DEFAULT NULL,
  8.   PRIMARY KEY (sid)
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  10. Course(cid,Cname,tid) 课程表 
  11. CREATE TABLE course (
  12.   cid varchar(10) NOT NULL,
  13.   cName varchar(10) DEFAULT NULL,
  14.   tid int(20) DEFAULT NULL,
  15.   PRIMARY KEY (cid)
  16. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  17. SC(sid,cid,score) 成绩表 
  18. CREATE TABLE sc (
  19.   sid varchar(10) DEFAULT NULL,
  20.   cid varchar(10) DEFAULT NULL,
  21.   score int(10) DEFAULT NULL
  22. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  23. Teacher(tid,Tname) 教师表 
  24. CREATE TABLE taacher (
  25.   tid int(10) DEFAULT NULL,
  26.   tName varchar(10) DEFAULT NULL
  27. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  28. 数据：（MySQL）
  29. insert  into taacher(tid,tName) values (1,'李老师'),(2,'何以琛'),(3,'叶平');
  30. insert  into student(sid,sName,sAge,sSex) values ('1001','张三丰','1980-10-12 23:12:36','男'),('1002','张无极','1995-10-12 23:12:36','男'),('1003','李奎','1992-10-12 23:12:36','女'),('1004','李元宝','1980-10-12 23:12:36','女'),('1005','李世明','1981-10-12 23:12:36','男'),('1006','赵六','1986-10-12 23:12:36','男'),('1007','田七','1981-10-12 23:12:36','女');
  31. insert  into sc(sid,cid,score) values ('1','001',80),('1','002',60),('1','003',75),('2','001',85),('2','002',70),('3','004',100),('3','001',90),('3','002',55),('4','002',65),('4','003',60);
  32. insert  into course(cid,cName,tid) values ('001','企业管理',3),('002','马克思',3),('003','UML',2),('004','数据库',1),('005','英语',1);
  34. ORACLE(表+数据)
  35. CREATE TABLE student (
  36.   sid varchar2(10) NOT NULL,
  37.   sName varchar2(20) DEFAULT NULL,
  38.   sAge date ,
  39.   sSex varchar2(10) DEFAULT NULL,
  40.   PRIMARY KEY (sid)
  41. ) 
  43. CREATE TABLE course (
  44.   cid varchar2(10) NOT NULL,
  45.   cName varchar2(10) DEFAULT NULL,
  46.   tid number(20) DEFAULT NULL,
  47.   PRIMARY KEY (cid)
  48. ) 
  50. CREATE TABLE sc (
  51.   sid varchar2(10) DEFAULT NULL,
  52.   cid varchar2(10) DEFAULT NULL,
  53.   score number(10) DEFAULT NULL
  54. )
  57. CREATE TABLE teacher (
  58.   tid number(10) DEFAULT NULL,
  59.   tName varchar2(10) DEFAULT NULL
  60. )
  62. insert  into course(cid,cName,tid) values ('001','企业管理',3);
  63. insert  into course(cid,cName,tid) values ('002','马克思',3);
  64. insert  into course(cid,cName,tid) values ('004','数据库',1);
  65. insert  into course(cid,cName,tid) values ('005','英语',1);
  67. insert  into sc(sid,cid,score) values ('1001','001',80);
  68. insert  into sc(sid,cid,score) values ('1001','002',60);
  69. insert  into sc(sid,cid,score) values ('1001','003',70);
  70. insert  into sc(sid,cid,score) values ('1002','001',85);
  71. insert  into sc(sid,cid,score) values ('1002','002',70);
  72. insert  into sc(sid,cid,score) values ('1003','004',90);
  73. insert  into sc(sid,cid,score) values ('1003','001',90);
  74. insert  into sc(sid,cid,score) values ('1003','002',99);
  75. insert  into sc(sid,cid,score) values ('1004','002',65);
  76. insert  into sc(sid,cid,score) values ('1004','003',50);
  77. insert  into sc(sid,cid,score) values ('1005','005',80);
  78. insert  into sc(sid,cid,score) values ('1005','004',70);
  79. insert  into sc(sid,cid,score) values ('1003','003',10);
  80. insert  into sc(sid,cid,score) values ('1003','005',10);
  83. insert  into student(sid,sName,sAge,sSex) values ('1001','张三丰',to_date('1980-10-12 23:12:36','YYYY-MM-DD HH24:MI:SS'),'男');
  84. insert  into student(sid,sName,sAge,sSex) values ('1002','张无极',to_date('1995-10-12 23:12:36','YYYY-MM-DD HH24:MI:SS'),'男');
  85. insert  into student(sid,sName,sAge,sSex) values ('1003','李奎',to_date('1992-10-12 23:12:36','YYYY-MM-DD HH24:MI:SS'),'女');
  86. insert  into student(sid,sName,sAge,sSex) values ('1004','李元宝',to_date('1980-10-12 23:12:36','YYYY-MM-DD HH24:MI:SS'),'女');
  87. insert  into student(sid,sName,sAge,sSex) values ('1005','李世明',to_date('1981-10-12 23:12:36','YYYY-MM-DD HH24:MI:SS'),'男');
  88. insert  into student(sid,sName,sAge,sSex) values ('1006','赵六',to_date('1986-10-12 23:12:36','YYYY-MM-DD HH24:MI:SS'),'男');
  89. insert  into student(sid,sName,sAge,sSex) values ('1007','田七',to_date('1981-10-12 23:12:36','YYYY-MM-DD HH24:MI:SS'),'女');
  91. insert  into teacher(tid,tName) values (1,'李老师');
  92. insert  into teacher(tid,tName) values (2,'何以琛');
  93. insert  into teacher(tid,tName) values (3,'叶平');
  96. 问题： 
  97. 1.查询“001”课程比“002”课程成绩高的所有学生的学号; 
  98. select a.sid from (select sid,score from SC where cid='001') a,(select sid,score 
  99. from SC where cid='002') b 
  100. where a.score>b.score and a.sid=b.sid; 
  101. 2、查询平均成绩大于60分的同学的学号和平均成绩; 
  102. select sid,avg(score) 
  103. from sc 
  104. group by sid having avg(score) >60; 
  105. 3、查询所有同学的学号、姓名、选课数、总成绩; 
  106. select Student.sid,Student.Sname,count(SC.cid),sum(score) 
  107. from Student left Outer join SC on Student.sid=SC.sid 
  108. group by Student.sid,Sname 
  109. 4、查询姓“李”的老师的个数; 
  110. select count(distinct(Tname)) 
  111. from Teacher 
  112. where Tname like '李%'; 
  113. 5、查询没学过“叶平”老师课的同学的学号、姓名; 
  114. select Student.sid,Student.Sname 
  115. from Student 
  116. where sid not in (select distinct( SC.sid) from SC,Course,Teacher where SC.cid=Course.cid and Teacher.tid=Course.tid and Teacher.Tname='叶平'); 
  117. 6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名; 
  118. A:select Student.sid,Student.Sname from Student,SC where Student.sid=SC.sid and SC.cid='001'and exists( Select * from SC as SC_2 where SC_2.sid=SC.sid and SC_2.cid='002'); 
  119. B:SELECT     s.sid,s.sName 
  120. FROM     student s,    (SELECT sid,COUNT(cid) FROM  sc WHERE cid IN ('001','002') GROUP BY sid HAVING COUNT(cid)>=2) t WHERE s.sid = t.sid
  121. 7、查询学过“叶平”老师所教的所有课的同学的学号、姓名; 
  122. select sid,Sname 
  123. from Student 
  124. where sid in (select sid from SC ,Course ,Teacher where SC.cid=Course.cid and Teacher.tid=Course.tid and Teacher.Tname='叶平' group by sid having count(SC.cid)=(select count(cid) from Course,Teacher where Teacher.tid=Course.tid and Tname='叶平')); 
  125. 8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名; 
  126. 1>Select sid,Sname from (select Student.sid,Student.Sname,score ,(select score from SC SC_2 where SC_2.sid=Student.sid and SC_2.cid='002') score2 
  127. from Student,SC where Student.sid=SC.sid and cid='001') S_2 where score2 <score; 
  128. 2>SELECT s.sid,s.sName FROM student s, 
  129. (SELECT sid,score FROM sc WHERE cid = '001') sc_1,
  130. (SELECT sid,score FROM sc WHERE cid = '002') sc_2
  131. WHERE sc_1.sid = sc_2.sid AND s.sid = sc_2.sid AND sc_2.score < sc_1.score
  132. 9、查询所有课程成绩小于60分的同学的学号、姓名; 
  133. select sid,Sname 
  134. from Student 
  135. where sid not in (select Student.sid from Student,SC where S.sid=SC.sid and score>60); 
  136. 10、查询没有学全所有课的同学的学号、姓名; 
  137. 1>
  138. select Student.sid,Student.Sname 
  139. from Student,SC 
  140. where Student.sid=SC.sid group by Student.sid,Student.Sname having count(cid) <(select count(cid) from Course);
  141. 2>
  142. SELECT s.sid,s.sname FROM student s,
  143. (SELECT sid,COUNT(cid) FROM sc GROUP BY sid HAVING COUNT(cid) < (SELECT COUNT(cid) FROM course) )t
  144. WHERE s.sid  = t.sid 
  145. 11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名; 
  146. select sid,Sname from Student,SC where Student.sid=SC.sid and cid in (select cid from SC where sid='1001'); 
  147. 13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩; 
  148. UPDATE sc,(SELECT c.cid,AVG(score) avgs FROM sc,course c,teacher t WHERE sc.cid = c.cid AND 
  149. c.tid = t.tid AND t.tName = '叶平' GROUP BY c.cid)sc_2 SET sc.score = sc_2.avgs WHERE sc.cid = sc_2.cid
  151. 14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名; 
  152. select sid from SC where cid in (select cid from SC where sid='1002') 
  153. group by sid having count(*)=(select count(*) from SC where sid='1002'); 
  155. 15、删除学习“叶平”老师课的SC表记录; 
  156. DELETE  FROM sc  WHERE sc.cid IN (SELECT sc.cid FROM course c ,teacher t WHERE sc.cid = c.cid AND c.tid = t.tid AND t.tName = '叶平')
  158. 17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示： 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分 
  159. SELECT sid as 学生ID 
  160. ,(SELECT score FROM SC WHERE SC.sid=t.sid AND cid='004') AS 数据库 
  161. ,(SELECT score FROM SC WHERE SC.sid=t.sid AND cid='001') AS 企业管理 
  162. ,(SELECT score FROM SC WHERE SC.sid=t.sid AND cid='005') AS 英语 
  163. ,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩 
  164. FROM SC AS t 
  165. GROUP BY sid 
  166. ORDER BY avg(t.score) 
  167. 18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 
  168. select cid "课程ID",max(score) "最高分",min(score) "最低分" from sc group by cid
  170. 19、按各科平均成绩从低到高和及格率的百分数从高到低排序 
  171. oracle>
  172. SELECT t.cid AS 课程号,MAX(course.Cname)AS 课程名,nvl(AVG(score),0) AS 平均成绩 
  173. ,100 * SUM(CASE WHEN nvl(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数 
  174. FROM SC T,Course 
  175. WHERE t.cid=course.cid 
  176. GROUP BY t.cid 
  177. ORDER BY 100 * SUM(CASE WHEN nvl(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC 
  178. Mysql>
  179. SELECT t.cid AS 课程号,MAX(course.Cname)AS 课程名,IFNULL(AVG(score),0) AS 平均成绩 
  180. ,100 * SUM(CASE WHEN IFNULL(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数 
  181. FROM SC T,Course 
  182. WHERE t.cid=course.cid 
  183. GROUP BY t.cid 
  184. ORDER BY 100 * SUM(CASE WHEN IFNULL(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC 
  188. 20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（001），马克思（002），OO&UML （003），数据库（004） 
  189. SELECT SUM(CASE WHEN cid ='001' THEN score ELSE 0 END)/SUM(CASE cid WHEN '001' THEN 1 ELSE 0 END) AS 企业管理平均分 
  190. ,100 * SUM(CASE WHEN cid = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN cid = '001' THEN 1 ELSE 0 END) AS 企业管理及格百分数 
  191. ,SUM(CASE WHEN cid = '002' THEN score ELSE 0 END)/SUM(CASE cid WHEN '002' THEN 1 ELSE 0 END) AS 马克思平均分 
  192. ,100 * SUM(CASE WHEN cid = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN cid = '002' THEN 1 ELSE 0 END) AS 马克思及格百分数 
  193. ,SUM(CASE WHEN cid = '003' THEN score ELSE 0 END)/SUM(CASE cid WHEN '003' THEN 1 ELSE 0 END) AS UML平均分 
  194. ,100 * SUM(CASE WHEN cid = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN cid = '003' THEN 1 ELSE 0 END) AS UML及格百分数 
  195. ,SUM(CASE WHEN cid = '004' THEN score ELSE 0 END)/SUM(CASE cid WHEN '004' THEN 1 ELSE 0 END) AS 数据库平均分 
  196. ,100 * SUM(CASE WHEN cid = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN cid = '004' THEN 1 ELSE 0 END) AS 数据库及格百分数 
  197. FROM SC 
  198. 21、查询不同老师所教不同课程平均分从高到低显示 要求显示：教师ID,教师姓名，课程ID,课程名称，平均成绩
  199. SELECT MAX(t.tid) "教师ID",MAX(t.tName) "教师姓名",c.cid "课程ID", MAX(c.cName) "课程名称" ,AVG(sc.score) "平均成绩"
  200. FROM sc,course c,teacher t WHERE sc.cid = c.cid AND c.tid = t.tid GROUP BY c.tid,c.cid
  201. ORDER BY AVG(sc.score) DESC
  202. 23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60] 
  203. SELECT SC.cid as 课程ID, Cname as 课程名称 
  204. ,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 - 85] 
  205. ,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 - 70] 
  206. ,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 - 60] 
  207. ,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -] 
  208. FROM SC,Course 
  209. where SC.cid=Course.cid 
  210. GROUP BY SC.cid,Cname; 
  212. 26、查询每门课程被选修的学生数 
  213. select cid,count(sid) from sc group by cid; 
  214. 27、查询出只选修了一门课程的全部学生的学号和姓名 
  215. select SC.sid,Student.Sname,count(cid) AS 选课数 
  216. from SC ,Student 
  217. where SC.sid=Student.sid group by SC.sid ,Student.Sname having count(cid)=1; 
  218. 28、查询男生、女生人数 
  219. Select count(Ssex) as 男生人数 from Student group by Ssex having Ssex='男'; 
  220. Select count(Ssex) as 女生人数 from Student group by Ssex having Ssex='女'; 
  221. 29、查询姓“张”的学生名单 
  222. SELECT Sname FROM Student WHERE Sname like '张%'; 
  223. 30、查询同名同性学生名单，并统计同名人数 
  224. SELECT sName,sSex ,COUNT(*) FROM student GROUP BY sName,sSex HAVING COUNT(*) > 1
  225. 31、1981年出生的学生名单(注：Student表中Sage列的类型是datetime) 
  226. Mysql>
  227. select Sname, CONVERT(char (11),DATEPART(year,Sage)) as age 
  228. from student 
  229. where CONVERT(char(11),DATEPART(year,Sage))='1981'; 
  230. Oracle>
  231. select * from student where substr(to_char(sage,'yyyy-MM-dd'),1,4)= '1981'
  232. 32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列 
  233. Select cid,Avg(score) from SC group by cid order by Avg(score),cid DESC ; 
  234. 33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩 
  235. select Sname,SC.sid ,avg(score) 
  236. from Student,SC 
  237. where Student.sid=SC.sid group by SC.sid,Sname having avg(score)>85; 
  238. 34、查询课程名称为“数据库”，且分数低于60的学生姓名和分数 
  239. Select Sname,isnull(score,0) 
  240. from Student,SC,Course 
  241. where SC.sid=Student.sid and SC.cid=Course.cid and Course.Cname='数据库'and score <60; 
  242. 35、查询所有学生的选课情况; 
  243. SELECT SC.sid,SC.cid,Sname,Cname 
  244. FROM SC,Student,Course 
  245. where SC.sid=Student.sid and SC.cid=Course.cid ; 
  246. 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数; 
  247. SELECT distinct student.sid,student.Sname,SC.cid,SC.score 
  248. FROM student,Sc 
  249. WHERE SC.score>=70 AND SC.sid=student.sid; 
  250. 37、查询不及格的课程，并按课程号从大到小排列 
  251. select cid from sc where scor e <60 order by cid ; 
  252. 38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名; 
  253. select SC.sid,Student.Sname from SC,Student where SC.sid=Student.sid and Score>80 and cid='003'; 
  254. 39、求选了课程的学生人数 
  255. select count(*) from sc; 
  256. 40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩 
  257. select Student.Sname,score 
  258. from Student,SC,Course C,Teacher 
  259. where Student.sid=SC.sid and SC.cid=C.cid and C.tid=Teacher.tid and Teacher.Tname='叶平' and SC.score=(select max(score)from SC where cid=C.cid ); 
  260. 41、查询各个课程及相应的选修人数 
  261. select count(*) from sc group by cid; 
  262. 42、查询不同课程成绩相同的学生的学号、课程号、学生成绩 
  263. select distinct A.sid,B.score from SC A ,SC B where A.Score=B.Score and A.cid <>B.cid ; 
  264. 43、查询每门功课成绩最好的前两名 
  265. SELECT * 
  266. FROM sc t1
  267. WHERE (
  268.   SELECT COUNT(*)
  269.   FROM sc t2
  270.   WHERE t1.cid=t2.cid
  271.   AND t2.score>=t1.score
  272. ) <=2 ORDER BY t1.cid
  274. 44、统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列 
  275. SELECT  cid "课程号",COUNT(*) "选修人数" FROM sc GROUP BY cid HAVING COUNT(*) >10  ORDER BY COUNT(*) DESC,cid 
  278. 45、检索至少选修两门课程的学生学号 
  279. select sid 
  280. from sc 
  281. group by sid 
  282. having count(*) > = 2 
  283. 46、查询全部学生都选修的课程的课程号和课程名 
  284. SELECT s.sName,c.cName, COUNT(*) FROM student s,course c, sc WHERE s.sid = sc.sid AND sc.cid = c.cid GROUP BY sc.cid HAVING COUNT(*) = (SELECT COUNT(*) FROM student)
  285. 47、查询没学过“叶平”老师讲授的任一门课程的学生姓名 
  286. SELECT DISTINCT Sname FROM Student WHERE sid NOT IN (SELECT sid FROM Course,Teacher,SC WHERE Course.tid=Teacher.tid AND SC.cid=course.cid AND Tname='叶平'); 
  287. 48、查询两门以上不及格课程的同学的学号及其平均成绩 
  288. select sid,avg(ifnull(score,0)) from SC where sid in (select sid from SC where score <60 group by sid having count(*)>2)group by sid; 
  289. 49、检索“004”课程分数小于60，按分数降序排列的同学学号 
  290. select sid from SC where cid='004'and score <60 order by score desc; 
  291. 50、删除“1002”同学的“001”课程的成绩 
  292. delete from Sc where sid='1002' and cid='001'; 
  295. 行转列
  296. CREATE TABLE stu_score (
  297.   grade_id varchar(10) DEFAULT NULL,
  298.   subject_name varchar(10) DEFAULT NULL,
  299.   max_score int(10) DEFAULT NULL
  300. )
  302. insert  into `stu_score`(`grade_id`,`subject_name`,`max_score`) values('1','语文',98);
  303. insert  into `stu_score`(`grade_id`,`subject_name`,`max_score`) values('2','数学',95);
  304. insert  into `stu_score`(`grade_id`,`subject_name`,`max_score`) values('2','政治',87);
  305. insert  into `stu_score`(`grade_id`,`subject_name`,`max_score`) values('5','语文',97);
  306. insert  into `stu_score`(`grade_id`,`subject_name`,`max_score`) values('5','数学',100);
  307. insert  into `stu_score`(`grade_id`,`subject_name`,`max_score`) values('5','政治',92);
  309. 步骤：
  310. 1.查询出要求的结果格式
  311. SELECT
  312.  CASE grade_id WHEN 1 THEN '一年级' 
  313.         WHEN 2 THEN '二年级'
  314.         WHEN 5 THEN '五年级'
  315.  END AS '年级',
  316.      CASE subject_name WHEN '语文' THEN max_score END AS '语文',
  317.      CASE subject_name WHEN '数学' THEN max_score END AS '数学',
  318.      CASE subject_name WHEN '政治' THEN max_score END AS '政治'
  319. FROM stu_score
  320. 2,去除null
  321. SELECT
  322.  CASE grade_id WHEN 1 THEN '一年级' 
  323.         WHEN 2 THEN '二年级'
  324.         WHEN 5 THEN '五年级'
  325.  END AS '年级',
  326.      IFNULL(CASE subject_name WHEN '语文' THEN max_score END,0) AS '语文',
  327.      IFNULL(CASE subject_name WHEN '数学' THEN max_score END,0) AS '数学',
  328.      IFNULL(CASE subject_name WHEN '政治' THEN max_score END,0) AS '政治'
  329. FROM stu_score
  331. 3.合并结果。
  332. SELECT
  333.  CASE grade_id WHEN 1 THEN '一年级' 
  334.         WHEN 2 THEN '二年级'
  335.         WHEN 5 THEN '五年级'
  336.  END AS '年级',
  337.  MAX(IFNULL(CASE subject_name WHEN '语文' THEN max_score END,0)) AS '语文',
  338.  MAX(IFNULL(CASE subject_name WHEN '数学' THEN max_score END,0)) AS '数学',
  339.  MAX(IFNULL(CASE subject_name WHEN '政治' THEN max_score END,0)) AS '政治'
  340. FROM stu_score
  341. GROUP BY grade_id
  343. 参考：http://blog.csdn.net/jayxujia123/article/details/21739125