Oracle高级查询_技术笔记

斯巴达汉子 2022-02-01

create table pm_ci
(ci_id varchar2(20),
stu_ids varchar2(100));

insert into pm_ci values('1','1,2,3,4');
insert into pm_ci values('2','1,4');

create table pm_stu
(stu_id varchar2(20),
stu_name varchar2(20));

insert into pm_stu values('1','张三');
insert into pm_stu values('2','李四');
insert into pm_stu values('3','王五');
insert into pm_stu values('4','赵六');

col stu_name for a20;

select c.ci_id,wm_concat(s.stu_name) stu_name
from pm_ci c,pm_stu s
where instr(c.stu_ids,s.stu_id)>0
group by c.ci_id;

0赞 · 0采集
慕姐0296475 2021-10-25

代码一：

1
2
3
4

select c.ci_id, wm_concat(s.stu_name) stu_ids
from pm_ci c, pm_stu s
where instr(c.stu_ids, s.stu_id) > 0
group by c.ci_id;

代码实现结果如下：

发现和视频结果不一样，没有按序号顺序输入，可能是因为wm_concat（）没有排序功能？这点不清楚，请大佬指教！！
利用listagg（）代替wm_concat（）就可以输出正确结果了。
代码二：

1
2
3
4

select c.ci_id, listagg(s.stu_name, ',') within group (order by s.stu_id) stu_ids
from pm_ci c, pm_stu s
where instr(c.stu_ids, s.stu_id) > 0
group by c.ci_id;
--------------------------------------------------------------------------
select cstu.ci_id, wm_concat(stu.stu_name) stu_name
from (select c.ci_id, stu.stu_name
from PM_CI c, PM_STU stu
where instr(c.stu_ids, stu.stu_id) != 0) cstu
group by cstu.ci_id

结果如下：

0赞 · 0采集
慕设计4302654 2021-10-13

练习题：
要求查询一下的结果：

1、先创建表及初始化数据
①创建及初始化学生表PM_STU
create table PM_STU(STU_ID VARCHAR2(20) NOT NULL,
STU_NAME VARCHAR2(20));
INSERT INTO PM_STU VALUES(1,'张三');
INSERT INTO PM_STU VALUES(2,'李四');
INSERT INTO PM_STU VALUES(3,'王五');
INSERT INTO PM_STU VALUES(4,'赵六');
②创建及初始化选课表PM_CI
先创建一个PM_CI1来转换成PM_CI
CREATE TABLE PM_CI1(CI_ID VARCHAR2(20) NOT NULL,
STU_IDS   VARCHAR2(20));
INSERT INTO PM_CI1 VALUES(1,1);
INSERT INTO PM_CI1 VALUES(1,2);
INSERT INTO PM_CI1 VALUES(1,3);
INSERT INTO PM_CI1 VALUES(1,4);
INSERT INTO PM_CI1 VALUES(2,1);
INSERT INTO PM_CI1 VALUES(2,4);
创建PM_CI，并插入数据
CREATE TABLE PM_CI(CI_ID VARCHAR2(20) NOT NULL,
STU_IDS   VARCHAR2(20));

INSERT INTO PM_CI SELECT CI_ID,WM_CONCAT(STU_IDS) FROM PM_CI1 GROUP BY CI_ID;

---先做出两张表的笛卡尔积，再根据有效的条件instr(a.STU_IDS,b.STU_ID) !=0，过滤得到每个科目对应的学生
select a.CI_ID ,b.STU_NAME
        from PM_CI a,PM_STU b
        where instr(a.STU_IDS,b.STU_ID) !=0;

---使用WM_CONCAT（）函数，列转行
select CI_ID, WM_CONCAT(STU_NAME) SUT_NAME
from (
        select a.CI_ID ,b.STU_NAME
        from PM_CI a,PM_STU b
        where instr(a.STU_IDS,b.STU_ID) !=0
         )
group by CI_ID;
【笔记本scott账户创建pm_cil对应截图上的pm_ci】

0赞 · 0采集
慕九州州 2021-05-10

instr（ a，b）：如果字符串b在字符串a的里面，则返回的是b在a中的位置，即返回值大于0；
wm_concat(cols):对学生姓名用逗号进行拼接

0赞 · 0采集
风铃在想什么 2021-05-04

select wm_concat(s.stu_name) "stu_name"
from pm_stu s,pm_ci c
where instr(c.stu_ids,s.stu_id)>0
group by c.ci_id;

0赞 · 0采集
qq_羽悦_03291187 2021-02-19

多表查询，取别名

截图
0赞 · 0采集
_阡陌花开 2021-01-07

qqq

0赞 · 0采集
宇智波带自行车 2020-10-13

SELECT
c.CI_ID,
c.STU_IDS,
wm_concat ( CASE WHEN instr( c.STU_IDS, s.STU_ID ) > 0 THEN s.stu_name ELSE NULL END ) stu_names
FROM
PM_CI c,
PM_STU s
GROUP BY
c.CI_ID,
c.STU_IDS;

1赞 · 2采集
qq_羽悦_03291187 2020-09-28

多表查询，instr(a,b)

0赞 · 0采集
qq_慕标5384168 2020-03-04

参考编写代码

0赞 · 0采集
慕瓜0579600 2020-02-29

declare
cursor cplayer is select height from player;
pheight player.height%type;

counta number:=0;
countb number:=0;
countc number:=0;
countd number:=0;

begin
open cplayer;

loop
fetch cplayer into pheight;
exit when cplayer%notfound;

if pheight>=2.00 and pheight<=2.50 then counta = counta+1
elsif pheight >=1.90 and pheight<=1.99 then countb = countb+1
elsif pheight >=1.80 and pheight<=1.89 then countc = countc+1
else countd = countd+1
end if;

end loop;
close cplayer;
dbms.output.put_line('A类人数'||counta)
dbms.output.put_line('B类人数'||countb)
dbms.output.put_line('C类人数'||countc)
dbms.output.put_line('D类人数'||countd)
end;
/

select 'a',count(height)
from player
where height between 2.00 and 2.50
union all
select 'b',count(height)
from player
where height between 1.90 and 1.99
union all
select 'c',count(height)
from player
where height between 1.80 and 1.89
union all
select 'd',count(height)
from player
where height between 1.60 and 1.79

select sum(decode(height, between 2.00 and 2.50,1,0) A,
sum(decode(height, between 1.90 and 1.99,1,0) B,
sum(decode(height, between 1.80 and 1.89,1,0) C,
sum(decode(height, between 1.60 and 1.79,1,0) D
from player

select
(select count(*) from player where height between 2.00 and 2.50) A,
(select count(*) from player where height between 1.90 and 1.99) B,
(select count(*) from player where height between 1.80 and 1.89) C,
(select count(*) from player where height between 1.60 and 1.79) D
from dual;

0赞 · 0采集
慕粉6666888 2019-05-15

--#案例三：按部门统计员工人数(已知员工入职年份）
select count(*) from emp;
select HIREDATE from emp;
select count(*) Total,
sum(decode(to_char(hiredate,'YYYY'),'1981',1,0)) "1981",
sum(decode(to_char(hiredate,'YYYY'),'1980',1,0)) "1980",
sum(decode(to_char(hiredate,'YYYY'),'1982',1,0)) "1982",
sum(decode(to_char(hiredate,'YYYY'),'1987',1,0)) "1987"
from emp;--函数方式
-------------------------
select
(select count(*) from emp) Total,
(select count(*) from emp where to_char(hiredate,'yyyy')='1980') "1980",
(select count(*) from emp where to_char(hiredate,'yyyy')='1981') "1981",
(select count(*) from emp where to_char(hiredate,'yyyy')='1982') "1982",
(select count(*) from emp where to_char(hiredate,'yyyy')='1987') "1987"
from dual;--子查询方式

----------
--创建表
desc pn_ci;
desc PM_STU;
create table pn_ci
(CI_ID varchar2(20) NOT NULL,
STU_IDS varchar2(100));--选课表
-------------------
create table pm_stu
(STU_ID varchar2(20) NOT NULL,
STU_NAME varchar2(20));--学生表
------------------
INSERT into pn_ci values('1','1,2,3,4');
INSERT into pn_ci values('2','14');
select * from pn_ci;
---------------------
INSERT into pm_stu values('1','张三');
INSERT into pm_stu values('2','李四');
INSERT into pm_stu values('3','王五');
INSERT into pm_stu values('4','赵六');
select * from pm_stu;

0赞 · 0采集
慕粉6666888 2019-05-15

--#案例三：按部门统计员工人数(已知员工入职年份）
select count(*) from emp;
select HIREDATE from emp;
select count(*) Total,
sum(decode(to_char(hiredate,'YYYY'),'1981',1,0)) "1981",
sum(decode(to_char(hiredate,'YYYY'),'1980',1,0)) "1980",
sum(decode(to_char(hiredate,'YYYY'),'1982',1,0)) "1982",
sum(decode(to_char(hiredate,'YYYY'),'1987',1,0)) "1987"
from emp;--函数方式
-------------------------
select
(select count(*) from emp) Total,
(select count(*) from emp where to_char(hiredate,'yyyy')='1980') "1980",
(select count(*) from emp where to_char(hiredate,'yyyy')='1981') "1981",
(select count(*) from emp where to_char(hiredate,'yyyy')='1982') "1982",
(select count(*) from emp where to_char(hiredate,'yyyy')='1987') "1987"
from dual;--子查询方式

----------
--创建表
desc pn_ci;
desc PM_STU;
create table pn_ci
(CI_ID varchar2(20) NOT NULL,
STU_IDS varchar2(100));--选课表
-------------------
create table pm_stu
(STU_ID varchar2(20) NOT NULL,
STU_NAME varchar2(20));--学生表
------------------
INSERT into pn_ci values('1','1,2,3,4');
INSERT into pn_ci values('2','14');
select * from pn_ci;
---------------------
INSERT into pm_stu values('1','张三');
INSERT into pm_stu values('2','李四');
INSERT into pm_stu values('3','王五');
INSERT into pm_stu values('4','赵六');
select * from pm_stu;

0赞 · 0采集
陈晨辰_ 2019-04-29

使用列转行listagg函数

0赞 · 0采集

MOMO______ 2019-04-07

select t1.cid, concat(t1.sname)
from(select k.ci_id cid,s.stu_name sname
    from pm_ci k,pm_stu s
    where instr(k.stu_id,s.stu_id)<>0) t1
group by t1.cid;

0赞 · 0采集

Amousy 2019-03-31
提示
1. 需要进行两个表的连接查询，为两个表都取别名
2. 使用instr（a，b）函数，该函数的含义为：如果字符串b在字符串a里面，则返回的是b在a中的位置，即返回值大于0
3. 需要用到分组查询
4. 使用wm_concat（cols）函数对学生姓名用逗号拼接。
0赞 · 0采集
慕神5423383 2019-03-24

如何用mysql来解决这个最后一个的联系

截图
0赞 · 0采集
精慕门1437568 2018-11-01

select cid,wm_concat(name) from ci,pm where instr(ci.sid,pm.sid)>0 group by cid;

0赞 · 0采集

小乌or小鸟 2018-04-04

不知道查询的结果是不是保证顺序的，比如'1,2,3,4'的结果应该是'张三,李四,王五,赵六'，而如果是'1,3,2,4',那么结果应该是'张三,王五,李四,赵六'，下列查询语句应该可以解决，不知还有没有更简便的语句

SELECT B.CI_ID, MAX(B.WM) STU_NAME
  FROM (SELECT A.CI_ID,
               WM_CONCAT(A.STU_NAME) OVER(PARTITION BY A.CI_ID ORDER BY A.CI_ID, A.P) WM
          FROM (SELECT PC.CI_ID, PS.STU_NAME, INSTR(PC.STU_IDS, PS.STU_ID) P
                  FROM PM_CI PC, PM_STU PS
                 WHERE INSTR(PC.STU_IDS, PS.STU_ID) > 0
                 ORDER BY CI_ID) A) B
 GROUP BY B.CI_ID;

2赞 · 0采集

seac 2018-03-06

create table pm_ci (ci_id varchar2(20) not null, stu_ids varchar2(100)); insert into pm_ci values('1','1,2,3,4'); insert into pm_ci values('2','1,4'); create table pm_stu (stu_id varchar2(20) not null, stu_name varchar2(20)); insert into pm_stu values('1','张三'); insert into pm_stu values('2','李四'); insert into pm_stu values('3','王五'); insert into pm_stu values('4','赵六'); select c.ci_id,wm_concat(s.stu_name) stu_name from pm_ci c,pm_stu s where instr(c.stu_ids,s.stu_id)>0 group by c.ci_id;

2赞 · 0采集
weibo_酱油sean_0 2017-12-22

SELECT CI_ID, MAX(TO_CHAR(STU_NAME)) STU_NAME FROM ( SELECT PC.CI_ID CI_ID,WM_CONCAT(PS.STU_NAME) OVER(PARTITION BY PC.CI_ID ORDER BY PS.STU_ID) STU_NAME FROM PM_CI PC,PM_STU PS WHERE INSTR(PC.STU_IDS,PS.STU_ID)>0) GROUP BY CI_ID ORDER BY CI_ID;

0赞 · 0采集
杨少宾 2017-11-03

select a.ci_id, wm_concat(b.stu_name) as stu_name from pn_ci a,pm_stu b where instr(a.stu_ids,b.stu_id)>0 group by a.ci_id

2赞 · 0采集
慕瓜3944751 2017-10-15

深入浅出

0赞 · 0采集
慕的地5165382 2017-09-11

select ci_id,wm_concat(stu_name) from pm_ci p,pm_stu where instr(stu_ids,stu_id )>0 group by ci_id; ---------------------------------------- instr(a,b) b不在a里返回0，在返回位置数>0

0赞 · 0采集
慕姐6008753 2017-09-04

create table pm_ci (ci_id varchar2(20), stu_ids varchar2(100)); insert into pm_ci values('1','1,2,3,4'); insert into pm_ci values('2','1,4'); create table pm_stu (stu_id varchar2(20), stu_name varchar2(20)); insert into pm_stu values('1','张三'); insert into pm_stu values('2','李四'); insert into pm_stu values('3','王五'); insert into pm_stu values('4','赵六'); col stu_name for a20; select c.ci_id,wm_concat(s.stu_name) stu_name from pm_ci c,pm_stu s where instr(c.stu_ids,s.stu_id)>0 group by c.ci_id;

1赞 · 1采集
jazia 2017-08-28

select c.ci_id,wm_concat(s.stu_name) from pm_ci c,pm_stu s where instr(c.stu_ids,s.stu_id)>0 group by ci_id

0赞 · 0采集
披着风的设计狮 2017-06-16

select ci_id,wm_concat(stu_name) namelist from (select ci_id,stu_name from pm_ci c,pm_stu s where instr(c.stu_ids,s.stu_id) > 0) group by ci_id;

1赞 · 0采集
KingStar1 2017-06-05

略空

截图
0赞 · 1采集
KingStar1 2017-05-22

nvl(a,0)滤空函数处理空值问题！！！

0赞 · 0采集
KingStar1 2017-05-22

111

截图
0赞 · 0采集

数据加载中...