SQL查询及50题

1.查询出时间范围2022/11/6 7:30~2022/11/11 19:30，生产线2001，件号655392800C，所有产品的客户条码信息

select id_stocpf,operat_nscomp,datemodif,SUBSTRING(heuremodif,1,6) AS TIME
from (select id_stocpf from t_stocpf where et_refcmp='655392800C' and datemodif between '20221106' and '20221111' and STOCPF_LINE='2001')s,t_operat
where s.id_stocpf=t_operat.et_stocpf
and operat_nscomp like 'P109%'

SUBSTRING(s,n,len)

SUBSTRING(heuremodif,1,6)，表示从heuremodif字符串返回一个长度为6的字符串，起始位置为1。

//按日期，时间排序

select id_stocpf,operat_nscomp,datemodif,LEFT(heuremodif,6) AS TIME1
from (select id_stocpf from t_stocpf where et_refcmp='655392800C' and datemodif between '20221104' and '20221112' and STOCPF_LINE='2001')s,t_operat
where s.id_stocpf=t_operat.et_stocpf
and operat_nscomp like 'P109%' order by datemodif,time1

LEFT(s,len),SUBSTRING(heuremodif,1,6)函数,在SQL Server与Postgres中相同

3.查询某IP PLC的地址引用情况

select * from t_cfport where et_prod='6008' and et_typeport='PLC' and et_par in('ADDR','IP','PLCNUM','SIZEDB','USAGE') and et_port_num in
(
select et_port_num from t_cfport where et_prod='6008' and et_typeport='PLC' and et_param_value='192.168.10.70'
)

4.查询'20221125','20221126'两天所有件号的条码和操作时间

select op.et_stocpf,op.operat_nscomp,op.datemodif,LEFT(op.heuremodif,6) AS TIME1,et_refcmp from (select * from t_operat where operat_nscomp<>'' and t_operat.datemodif in('20221125','20221126')) op,t_stocpf where op.et_stocpf=t_stocpf.id_stocpf

 或者

select op.et_stocpf,op.operat_nscomp,op.datemodif,LEFT(op.heuremodif,6) AS TIME1,et_refcmp from (select * from t_operat where operat_nscomp<>'' and t_operat.datemodif in('20221125','20221126')) op left join t_stocpf on op.et_stocpf=t_stocpf.id_stocpf

 

5.LAG,LEAD函数增加对记录的操作

select et_stocpf as pf_id,operat_nscomp as nscomp,datemodif,heuremodif AS TIME,lag(heuremodif,1) over (ORDER BY heuremodif) AS BEFORE from t_operat where et_stocpf='200103308142' order by heuremodif

 字段相减

5.1

select et_stocpf,operat_nscomp,datemodif,cast(heuremodif AS bigint) as time,
cast(lag(heuremodif,1) over (ORDER BY heuremodif) AS bigint) as before,cast(heuremodif AS bigint)-cast(lag(heuremodif,1) over (ORDER BY heuremodif) AS bigint)
from t_operat where et_stocpf='500200839502' order by heuremodif;

5.2

select id_internal,cname,datemodif,timemodif,quantity,quantity-lag(quantity,1) over (ORDER BY quantity) as useHour from t_waterfee

 

6.根据内部号，查产品的routing,多表联合查询，以id号'200103251275'为例

select t_operat.et_stocpf,t_operat.operat_nscomp,t_operat.nommodif,t_operat.datemodif,LEFT(t_operat.heuremodif,6) AS timeHms,t_operat.operat_comp_no_passage,et_refcmp,t_modeop.et_prod_poste,modeop_libelle,et_refakf_comp,qualpf_libelle,CNTNER_NUMCONT
from t_operat,t_stocpf,t_simo,t_modeop,t_nomen,t_qualpf,t_cntner,t_packpf
where t_operat.et_stocpf='200103251275'
and t_stocpf.id_stocpf=t_operat.et_stocpf
and t_operat.et_simo=t_simo.id_simo
and t_simo.et_modeop=t_modeop.id_modeop
and t_simo.et_nomen=t_nomen.id_nomen
and t_stocpf.et_qualpf=t_qualpf.id_qualpf
and t_packpf.et_stocpf=t_operat.et_stocpf
and t_packpf.et_cntner=t_cntner.id_cntner
order by datemodif,timeHms

7.

SQL经典50题

第3题：查询在SC表存在成绩的学生信息

答案：

select distinct student.* from student,sc where student.sid=sc.sid

 如果select distinct * from student,sc where student.sid=sc.sid 则为

第7题：

查询没有学全所有课程的同学的信息

考点：分组查询Group by+Having过滤分组

WHERE 搜索条件在进行分组操作之前应用；而 HAVING 搜索条件在进行分组操作之后应用。HAVING 语法与 WHERE 语法类似，但 HAVING 可以包含聚合函数。HAVING 子句可以引用选择列表中显示的任意项。

WHERE、GROUP BY 和 HAVING 子句的正确顺序对编写高效的查询代码会有所帮助：
WHERE 子句用来筛选 FROM 子句中指定的操作所产生的行。
GROUP BY 子句用来分组 WHERE 子句的输出。
HAVING 子句用来从分组的结果中筛选行。
对于可以在分组操作之前或之后应用的任何搜索条件，在 WHERE 子句中指定它们会更有效。这样可以减少必须分组的行数。应当在 HAVING 子句中指定的搜索条件只是那些必须在执行分组操作之后应用的搜索条件。
当同时含有where子句、group by 子句 、having子句及聚集函数时，执行顺序如下：
--执行where子句查找符合条件的数据；
--使用group by 子句对数据进行分组；对group by 子句形成的组运行聚集函数计算每一组的值；最后用having 子句去掉不符合条件的组。
简而言之，一句话，where 语句里不能使用聚合函数，所以用having。

答案：

select * from student where student.sid not in

(
select sid from sc group by sid having count(cid)=(select count(cid) from course)
)

8.第8题：统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

select course.cid,course.cname,
sum(case when sc.score<=100 and sc.score>85 then 1 else 0 end) as "[100-85]",
sum(case when sc.score<=100 and sc.score>85 then 1 else 0 end)/cast(count(sc.sid) as real)as "[100-85]%",
sum(case when sc.score<=85 and sc.score>70 then 1 else 0 end) as "[85-70]",
sum(case when sc.score<=85 and sc.score>70 then 1 else 0 end)/cast(count(sc.sid) as real) as "[85-70]%",
sum(case when sc.score<=70 and sc.score>60 then 1 else 0 end) as "[70-60]",
sum(case when sc.score<=70 and sc.score>60 then 1 else 0 end)/cast(count(sc.sid) as real) as "[70-60]%",
sum(case when sc.score<=60 and sc.score>0 then 1 else 0 end) as "[60-0]",
sum(case when sc.score<=60 and sc.score>0 then 1 else 0 end)/cast(count(sc.sid) as real) as "[60-0]%"
from course inner join sc on sc.cid=course.cid group by course.cid,course.cname;

9.第18题

查询各科成绩前三名的记录

select b.sid,student.sname,b.cid,ranking from (select *,ROW_NUMBER() OVER(Partition by cid order by score desc) as ranking from sc)b
inner join student on student.sid=b.sid where b.ranking in(1,2,3) order by cid

10.查询每门课程选修的学生id

原题目为:查询每门课程选修的学生数，语句为:select cid,count(sid) from sc group by cid

现要求返回结果为:

01:01,02,03,04,05,06

02:01,02,03,04,05,07

03:01,02,03,04,06,07

 

select d.cid,array_agg(d.sid) from (select cid,sid from sc where cid='01')d group by d.cid
union all
select e.cid,array_agg(e.sid) from (select cid,sid from sc where cid='02')e group by e.cid
union all
select f.cid,array_agg(f.sid) from (select cid,sid from sc where cid='03')f group by f.cid

 

select cid,array_agg(distinct d.sid) from (select cid,sid from sc)d group by cid

11.select 1 from 的作用

select 1 from mytable 与 select anycol(目的表集合中的任意一行）from mytable、select * from mytable 作用上来说是没有差别的，都是查看是否有记录;

一般用来当做判断子查询是否成功（即是否有满足条件的时候使用），常用于exists,子查询中。