新手用WHERE字句进行条件分支,高手用SELECT字句进行条件分支sql
--男性人口 SELECT pref_name, SUM(population) FROM PopTbl2 WHERE sex='1' GROUP BY pref_name --女性人口 SELECT pref_name SUM(population) FROM PopTbl2 WHERE sex='2' GROUP BY pref_name
SELECT pref_name, --男性人口 SUM(CASE WHEN sex='1' THEN population ELSE 0 END) AS cnt_m, --女性人口 SUM(CASE WHEN sex='2' THEN population ELSE 0 END)AS cnt_f, FROM PopTbl2 GROUP BY pref_name;
新手用HAVING子句进行条件分支,高手用SELECT子句进行条件分支函数
--选择只加入一个社团的学生 SELECT std_id,MAX(club_id)AS main_club FROM StudentClub GROUP BY std_id HAVING COUNT(*)=1; --选择加入了多个社团的学生 SELECT std_id,club_id AS main_club FROM StudentClub WHERE main_club_flg='Y';
SELECT std_id, CASE WHERE COUNT(*)=1 --只加入一个社团的学生 THEN MAX(club_id) ELSE MAX(CASE WHEN main_club_flag='Y' THEN club_id ELSE NULL END) END AS main_club FROM StudentClub GROUP BY std_id
删除重复行code
DELETE FROM Products P1 WHERE EXISTS(SELECT * FROM Products P2 WHERE P1.name =P2.name AND P1.price=P2.price AND P1.rowid <P2.rowid);
查找局部不一致的列server
--用于查询价格相等但商品名称不一样的记录的SQL语句 SELECT DISTINCT P1。name,P1.price FROM Products P1,Products P2 WHERE P1.price =p2.price AND P1.name<>P2.name
排序排序
--排序,使用窗口函数 SELECT name,price RANK() OVER (ORDER BY price DESC) AS rank_1, DENSE_RANK() OVER(ORDER BY price DESC)AS rank_2 FROM Products
true和false两个值,第三个值unknown递归
寻找缺失的编号io
--若是有查询结果,说明存在缺失的编号 SELECT '存在缺失的编号' AS gap FROM SeqTbl HAVING COUNT(*)<>MAX(seq)
求众数入门
SELECT income,COUNT(*)AS cnt FROM Graduates GROUP BY income HAVING COUNT(*) >=ALL(SELECT COUNT(*) FROM Graduates GROUP BY income)
求中位数class
--求中位数SQL语句:在HAVING子句中使用非等值自链接 SELECT AVG(DISTINCT income) FROM (SELECT T1.income FROM Graduates T1,Graduates T2 GROUP BY T1.income) --S1的条件 HAVING SUM(CASE WHEN T2.income >=T1.income THEN 1 ELSE 0 END)>=COUNT(*)/2 AND SUM(CASE WHERE T2.income<=T1.income THEN 1 ELSE 0 END)>=COUNT(*)/2) TEP
查询不包含NULL的集合基础
--在对包含NULL的列使用时,COUNT(*)和COUNT(列名)的查询结果是不一样的 SELECT COUNT(*),COUNT(col_1) FROM NUllTbl
用外链接进行行列转换(行→列)
--水平展开求交叉表 SELECT C0,name, CASE WHEN C1.name IS NOT NULL THEN 'O' ELSE NULL END AS "SQL入门" CASE WHEN C2.name IS NOT NULL THEN 'O' ELSE NULL END AS "UNIX基础" CASE WHEN C3.name IS NOT NULL THEN 'O' ELSE NULL END AS "Java中级" FROM (SELECT DISTINCT name FROM Courses)CO --这里的Co是侧栏 LEFT OUTER JOIN (SELECT name FROM Courses WHERE course ='SQL入门')C1 ON CO.name =C1.name LEFT OUTER JOIN (SELECT name FROM Courses WHERE course='UNIX基础')C2 ON C0.name=C2.name LEFT OUTEER JOIN (SELECT name FROM Courses WHERE course='Java中级')C3 ON C0.name=C3.name
用外链接进行行列转换(列→行)
--列数据转换成行数据;使用UNION ALL SELECT employee,child_1 AS child FROM Personnel UNION ALL SELECT employee,child_2 AS child FROM Personnel UNION ALL SELECT employee,child_3 AS child FROM Personnel
用外链接求差集:A-B
SELECT A.id AS id,A.name AS A_name FROM Class_A A LEFT OUTER JOIN CLass_B B ON A.id =B.id WHERE B.name IS NULL
用全外链接求异或集
SELECT COALESCE(A.id,B.id)AS id, COALESCE(A.name,B.name)AS name FROM Class_A A FULL OUTER OUTER JOIN Class_B B ON A.id=B.id WHERE A.name IS NULL OR B.name IS NULL
和上一年比较结果
--求出的是增加了仍是减小了,亦或者是维持现状,使用关联子查询 SELECT S1.year,S1.sale, CASE WHEN sale= (SELECT sale FROM Sales S2 WHERE S2.year =S1.year-1)THEN '→'--持平 WHEN sale> (SELECT sale FROM Sales S2 WHERE S2.year =S1.year-1)THEN '↑'--增加 WHEN sale< (SELECT sale FROM Sales S2 WHERE S2.year =S1.year-1)THEN '↓'--减小 ELSE '-' END AS var FROM Sales S1 ORDER BY year;
和过去最临近的年份营业额相同的年份,同时使用自链接
SELECT S1.year AS year, S1.year AS year, FROM Sale2 S1,Sales2 S2 WHERE S1.sale =S2.sale AND S2.year =(SELECT MAX(year) FROM Sales2 S3 WHERE S1.year>S3.year) ORDER BY year;
移动累计值
--求累计值;使用肥罗已满型递归集合 SELECT prc_data, A1.prc_amt, (SELECT SUM(prc_amt) FROM Accounts A2 WHERE A1.prc_date >=A2.prc_date)AS onhand_amt FROM Accounts A1 ORDER BY prc_date;
查询重叠的时间区间
--求重叠的住宿期间 SELECT reserver,start_date,end_date FROM Reservations R1 WHERE EXISTS (SELECT * FROM Reservations R2 WHERE R1.reserver<> R2.reserver --与本身之外的客人进行比较 AND (R1.start_date BETWEEN R2.start_date AND R2.end_date --本身的入住日期在他人的住宿期间 OR R1.end_date BETWEEN R2.start_date AND R2.end_date)); --本身的离店日期在他人的住宿期间内