文章目录:
不废话,直接进入主题:
一、关于表内容如下:
日期 结果
2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负
如果要生成下列结果, 该如何通过sql语句实现?
胜 负
2005-05-09 2 2
2005-05-10 1 2
1、建表语句:
drop table recreationclub_result
create table recreationclub_result(
INT PRIMARY KEY,
dt_col datetime NOT NULL COMMENT '日期时间',
result VARCHAR(4) NOT NULL COMMENT '结果'
);
insert into recreationclub_result(dt_col,result)VALUES('2005-05-09','胜')
insert into recreationclub_result(dt_col,result)VALUES('2005-05-09','胜')
insert into recreationclub_result(dt_col,result)VALUES('2005-05-09','负')
insert into recreationclub_result(dt_col,result)VALUES('2005-05-09','负')
insert into recreationclub_result(dt_col,result)VALUES('2005-05-10','胜')
insert into recreationclub_result(dt_col,result)VALUES('2005-05-10','负')
insert into recreationclub_result(dt_col,result)VALUES('2005-05-10','负')
SELECT * from recreationclub_result
2、实现:
select
dt_col
,sum(case when result = '胜' then 1 else 0 end) 胜
,sum(case when result = '负' then 1 else 0 end) 负
from recreationclub_result
group by dt_col
order by dt_col;
二、查询出每门课都大于80 分的学生姓名:
name kecheng fenshu
张三 语文 81
张三 数学 75
李四 语文 76
李四 数学 90
王五 语文 81
王五 数学 100
王五 英语 90
建表语句:
create table Student(
name VARCHAR(10) NOT NULL COMMENT '姓名',
kecheng VARCHAR(10) NOT NULL COMMENT '课程',
score int NOT NULL COMMENT '成绩'
);
insert into Student(name,kecheng,score)VALUES('张三','语文',81)
insert into Student(name,kecheng,score)VALUES('张三','数学',75)
insert into Student(name,kecheng,score)VALUES('李四','语文',765)
insert into Student(name,kecheng,score)VALUES('李四','数学',90)
insert into Student(name,kecheng,score)VALUES('王五','语文',81)
insert into Student(name,kecheng,score)VALUES('王五','数学',100)
insert into Student(name,kecheng,score)VALUES('王五','英语',90)
实现:
-- 实现:
SELECT DISTINCT A.name FROM Student A WHERE A.name not in(SELECT Distinct S.name FROM Student S WHERE S.score <80);
-- 实现2:
SELECT DISTINCT A.name From Student A where not exists (SELECT 1 From Student S Where S.score <80 AND S.name =A.name);
总表查
SELECT * from Student
三、请用SQL 语句实现
从TestDB 数据表中查询出所有月份的发生额都比101 科目相应月份的发生额高的科目。请注意:TestDB 中有很多科目,都有1 -12 月份的发生额。
AccID :科目代码,Occmonth :发生额月份,DebitOccur :发生额。
实现:
select max(AccID),Occmonth,DebitOccur from TestDB where to_date(Occmonth,'yyyy-mm-dd') as 日期 >='2021-01-01'
and to_date(Occmonth,'yyyy-mm-dd') as 日期 <='2021-01-12' and AccID >='101' group by Occmonth,DebitOccur
下面题目是设计到行列转换的问题:
四.根据表一数据,如何查询以下结果:
表一:
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
建表语句:
drop table YearData
SELECT * from YearData
create table YearData(
year int NOT NULL COMMENT '年份',
month VARCHAR(10) NOT NULL COMMENT '月份 ',
amount VARCHAR(10) NOT NULL COMMENT '账户'
);
insert into YearData(year,month,amount)VALUES(1991,'m1','1.1');
insert into YearData(year,month,amount)VALUES(1991,'m2','1.2');
insert into YearData(year,month,amount)VALUES(1991,'m3','1.3');
insert into YearData(year,month,amount)VALUES(1991,'m4','1.4');
insert into YearData(year,month,amount)VALUES(1992,'m1','2.1');
insert into YearData(year,month,amount)VALUES(1992,'m2','2.2');
insert into YearData(year,month,amount)VALUES(1992,'m3','2.3');
insert into YearData(year,month,amount)VALUES(1992,'m4','3.4');
实现:
SELECT
year,
max(CASE WHEN MONTH='m1' THEN amount ELSE 0 END ) AS m1,
max(CASE WHEN MONTH='m2' THEN amount ELSE 0 END) AS m2,
max(CASE WHEN MONTH='m3' THEN amount ELSE 0 END ) AS m3,
max(CASE WHEN MONTH='m4' THEN amount ELSE 0 END ) AS m4
FROM YearData
GROUP BY year
注意点:
下面截图是面试官给的真题,我在此基础上做了修改数据,因为我经过几次尝试,数据有问题。所以在月份的基础字段上增加了m字母,用来判断的唯一字段。本人此题目花费时间较长琢磨。
面试原题:
五、根据以下表1,表2提供表输出统计结果
表1:用户放号信息表,放号周期(202001-202105)
CREATE TABLE TJ_CARD_BUSINESS_DTL1 (
MONTH_ID VARCHAR(10), --放号月份
node_type varchar(50), --渠道名称
bill_id varchar(50), --放号用户手机号
IS_BLQ smallint --是否保留期
)
数据举例:
放号月份,例:'202112'
渠道名称,例:'电子渠道','加盟厅','社会加盟店','手机卖场','直供商'
放号用户手机号, 例:11位手机号
是否保留期,例如:’是’或者’否’
表2:用户出账费用,(周期:202001-202105)
CREATE TABLE TJ_ USR_ACC _DTL (
stat_month VARCHAR(10), --账单月份
bill_id varchar(50), --放号用户手机号
TOTAL_FEE DECIMAL(30, 2) --出账费用
)
账单月份,例:'202202'
放号用户手机号,例:11位手机号
出账费用,例:88.00,138.00等
没来的及做此题。