应用函数进步查询效力
create table STUDENT
(
STUNO NUMBER not null,
STUNAME VARCHAR2(1000),
SCORE NUMBER,
constraint PK_STUDENT primary key (STUNO)
);
-- Add comments to the columns
comment on column STUDENT.STUNO
is "学号";
comment on column STUDENT.STUNAME
is "姓名";
comment on column STUDENT.SCORE
is "成就";
insert into STUDENT (STUNO, STUNAME, SCORE)
values (1, "张三", 78);
insert into STUDENT (STUNO, STUNAME, SCORE)
values (2, "李四", 92);
insert into STUDENT (STUNO, STUNAME, SCORE)
values (3, "王五", 67);
insert into STUDENT (STUNO, STUNAME, SCORE)
values (4, "赵六", 88);
1.应用聚集(低效)
SELECT STUNAME, SCORE, "优"
FROM STUDENT
WHERE SCORE >= 90
UNION ALL
SELECT STUNAME, SCORE, "良"
FROM STUDENT
WHERE SCORE >= 80
AND SCORE < 90
UNION ALL
SELECT STUNAME, SCORE, "中"
FROM STUDENT
WHERE SCORE >= 60
AND SCORE < 80
UNION ALL
SELECT STUNAME, SCORE, "差" FROM STUDENT WHERE SCORE < 60;
SQL> SELECT STUNAME, SCORE, "优"
2 FROM STUDENT
3 WHERE SCORE >= 90
4 UNION ALL
5 SELECT STUNAME, SCORE, "良"
6 FROM STUDENT
7 WHERE SCORE >= 80
8 AND SCORE < 90
9 UNION ALL
10 SELECT STUNAME, SCORE, "中"
11 FROM STUDENT
12 WHERE SCORE >= 60
13 AND SCORE < 80
14 UNION ALL
15 SELECT STUNAME, SCORE, "差" FROM STUDENT WHERE SCORE < 60;
STUNAME SCORE "
-------------------- ---------- --
李四 92 优
赵六 88 良
张三 78 中
王五 67 中
2.应用decode函数(高效)
SELECT STUNAME,
SCORE,
DECODE(SIGN(SCORE - 90),
-1,
DECODE(SIGN(SCORE - 80),
-1,
DECODE(SIGN(SCORE - 60), -1, "差", "中"),
"良"),
"优")
FROM STUDENT;
SQL> SELECT STUNAME,
2 SCORE,
3 DECODE(SIGN(SCORE - 90),
4 -1,
5 DECODE(SIGN(SCORE - 80),
6 -1,
7 DECODE(SIGN(SCORE - 60), -1, "差", "中"),
8 "良"),
9 "优")
10 FROM STUDENT;
STUNAME SCORE DE
-------------------- ---------- --
张三 78 中
李四 92 优
王五 67 中
赵六 88 良<pre class="sql" name="code">DROP TABLE RESULT;
create table RESULT(rq VARCHAR2(10),SHENGFU VARCHAR2(2));
insert into result values("2005-05-09","胜");
insert into result values("2005-05-09","胜");
insert into result values("2005-05-09","负");
insert into result values("2005-05-09","负");
insert into result values("2005-05-10","胜");
insert into result values("2005-05-10","负");
insert into result values("2005-05-10","负");
COMMIT;
如果要生成以下成果, 该如何写sql语句?
胜 负
2005-05-09 2 2
2005-05-10 1 2
解答:
a，第一种方法
1.依据RQ进行group by 操作，即依据时光进行分组，
2.再求出同一时光胜和负的合计值
SELECT RQ,
SUM(CASE
WHEN SHENGFU = "胜" THEN
1
ELSE
0
END) 胜,
SUM(CASE
WHEN SHENGFU = "负" THEN
1
ELSE
0
END) 负
FROM RESULT
GROUP BY RQ;
SQL> SELECT RQ,
2 SUM(CASE
3 WHEN SHENGFU = "胜" THEN
4 1
5 ELSE
6 0
7 END) 胜,
8 SUM(CASE
9 WHEN SHENGFU = "负" THEN
10 1
11 ELSE
12 0
13 END) 负
14 FROM RESULT
15 GROUP BY RQ;
RQ 胜 负
---------- ---------- ----------
2005-05-10 1 2
2005-05-09 2 2
b.第二种方法
1.分离查询胜的次数及其时光，
2.再把两个成果集进行内衔接
SELECT N.RQ, N.胜, M.负
FROM (SELECT RQ, COUNT(*) AS 胜
FROM RESULT
WHERE SHENGFU = "胜"
GROUP BY RQ) N
INNER JOIN (SELECT RQ, COUNT(*) AS 负
FROM RESULT
WHERE SHENGFU = "负"
GROUP BY RQ) M ON N.RQ = M.RQ;
SQL> SELECT N.RQ, N.胜, M.负
2 FROM (SELECT RQ, COUNT(*) AS 胜
3 FROM RESULT
4 WHERE SHENGFU = "胜"
5 GROUP BY RQ) N
6 INNER JOIN (SELECT RQ, COUNT(*) AS 负
7 FROM RESULT
8 WHERE SHENGFU = "负"
9 GROUP BY RQ) M ON N.RQ = M.RQ;
RQ 胜 负
---------- ---------- ----------
2005-05-10 1 2
2005-05-09 2 2
c.第三种方法
1.分离查询胜的次数及其时光
2、通过等值条件进行衔接起来
SELECT N.RQ, N.胜, M.负
FROM (SELECT RQ, COUNT(*) AS 胜
FROM RESULT
WHERE SHENGFU = "胜"
GROUP BY RQ) N， (SELECT RQ, COUNT(*) AS 负
FROM RESULT
WHERE SHENGFU = "负"
GROUP BY RQ) M
WHERE N.RQ = M.RQ;
SQL> SELECT N.RQ, N.胜, M.负
2 FROM (SELECT RQ, COUNT(*) AS 胜
3 FROM RESULT
4 WHERE SHENGFU = "胜"
5 GROUP BY RQ) N， (SELECT RQ, COUNT(*) AS 负
6 FROM RESULT
7 WHERE SHENGFU = "负"
8 GROUP BY RQ) M
9 WHERE N.RQ = M.RQ;
RQ 胜 负
---------- ---------- ----------
2005-05-10 1 2
2005-05-09 2 2<pre class="sql" name="code">怎样把这样一个表儿
year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
查成这样一个成果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4
create table AMOUNT
(
YEAR VARCHAR2(4),
MONTH VARCHAR2(2),
AMOUNT NUMBER(15,2)
);
insert into amount (YEAR, MONTH, AMOUNT)
values ("1991", "1", 1.10);
insert into amount (YEAR, MONTH, AMOUNT)
values ("1991", "2", 1.20);
insert into amount (YEAR, MONTH, AMOUNT)
values ("1991", "3", 1.30);
insert into amount (YEAR, MONTH, AMOUNT)
values ("1991", "4", 1.40);
insert into amount (YEAR, MONTH, AMOUNT)
values ("1992", "1", 2.10);
insert into amount (YEAR, MONTH, AMOUNT)
values ("1992", "2", 2.20);
insert into amount (YEAR, MONTH, AMOUNT)
values ("1992", "3", 2.30);
insert into amount (YEAR, MONTH, AMOUNT)
values ("1992", "4", 2.40);
<pre><pre class="sql" name="code">思路:group by year统计年，再条件断定每月，统计所在月，所在月的合计值显示出来应用函数进步查询效力
create table STUDENT
(
STUNO NUMBER
阅读背景：
经典SQL
来源：互联网
分享到：
