mysql 多条记录选择一套_2020-11-09-Mysql(练习题第一套)

遗留问题 select语句中的别名问题 group by 中的别名问题

# sql

-表 FACT_DAY_SC

-字段 DEPT_ID varchar(20) 电厂

TJSJ_DATE DATE 日期

FSL number 日发电量

create table FACT_DAY_SC(

DEPT_ID varchar(20),

TJSJ_DATE DATE,

FSL LONG

);

insert into FACT_DAY_SC values ('10001','2020-12-01',113);

insert into FACT_DAY_SC values ('10001','2019-02-01',1233);

insert into FACT_DAY_SC values ('10001','2020-03-01',1453);

insert into FACT_DAY_SC values ('10002','2020-04-01',113);

insert into FACT_DAY_SC values ('10001','2020-05-01',563);

insert into FACT_DAY_SC values ('10002','2020-06-01',4);

insert into FACT_DAY_SC values ('10001','2020-07-01',567);

insert into FACT_DAY_SC values ('10002','2020-08-01',4567);

insert into FACT_DAY_SC values ('10001','2020-09-01',1232);

insert into FACT_DAY_SC values ('10001','2020-10-01',456);

insert into FACT_DAY_SC values ('10001','2020-07-23',678);

insert into FACT_DAY_SC values ('10003','2020-09-2',1247);

insert into FACT_DAY_SC values ('10001','2020-01-2',579);

insert into FACT_DAY_SC values ('10003','2019-01-3',1257);

insert into FACT_DAY_SC values ('10001','2019-01-11',1269);

insert into FACT_DAY_SC values ('10003','2020-01-4',4793);

insert into FACT_DAY_SC values ('10003','2019-01-5',47234);

insert into FACT_DAY_SC values ('10003','2020-04-04',4793);

insert into FACT_DAY_SC values ('10003','2018-01-19',4456783);

insert into FACT_DAY_SC values ('10003','2020-12-01',47123);

1.写出SQL计算出每个单位,每个月的最高发电量

//问题:分析是否按照年来分类

// select substr(cast('2020-01-09' as Date ) , 1,7 ); substr() 函数能截取日期

//substr(string string, int a, int b);函数的理解

//string 需要截取的字符串

a 截取字符串的开始位置(注：当a等于0或1时，都是从第一位开始截取)

b 要截取的字符串的长度

select

DEPT_ID,

substr(DEPT_ID,,6,2) as result ,

max(FSL)

from FACT_DAY_SC

group by DEPT_ID , substr(TJSJ_DATE,6,2) ;

2.求取10001电厂,每日的最高发电量(显示到目前为只每日最高发电量)

//用聚合函数还是使用窗口函数

//好像不行???? 发现问题??? 我用的是long比较的是字典顺序?????

select

*,

max(FSL) over(ORDER BY TJSJ_DATE) as num

from

FACT_DAY_SC

where DEPT_ID ='10001';

//这个是比较的是前后日期最大的日电量

select

DEPT_ID,

TJSJ_DATE,

if(result > FSL , result , FSL) as '日最大用量'

from

(

select

DEPT_ID,

TJSJ_DATE,

FSL,

lead(FSL,1) over( order by FSL ) as result

from

(

select

DEPT_ID,

TJSJ_DATE,

FSL

from FACT_DAY_SC

where DEPT_ID = '10001'

order by TJSJ_DATE

) t1

) t2

3.基于2查询每次打破日最大用电量的日期和发电量

select

TJSJ_DATE,

FSL

from

(

select

DEPT_ID,

TJSJ_DATE,

FSL,

max(FSL) over(order by FSL ) as max_result

from

FACT_DAY_SC

where DEPT_ID = '10001'

) t1

where max_result = t1.FSL

4.查询日期2019-01-11日发电量topN 的电厂

//知识点 序列函数考虑并序

select

DEPT_ID

dense_rank() over(order by FSL )

from FACT_DAY_SC

where TJSJ_DATE = '2019-01-11'

5.计算本年有多少天的SQL?

//知识点回顾SQL中的日期函数 注意使用 cast(' 2020-09-09' as DATE)

datediff(date ,date)+1 比较两个日期之间相差多少天

date_add(date,number) 日期加number

date_sub(date ,number) 日期减number

select datediff('2020-12-31','2020-01-01') +1

6.计算 2019-01月 及 电厂10001 中天数超过平均发电量的天数

//知识点 使用切割函数 substr() 分组

//发现不允许这样用使用子查询

select

count(FSL > avg(FSL))

from

FACT_DAY_SC

where DEPT_ID = '1001' and substr(TJSJ_DATE ,1,7) = '2020-09'

// substr和子查询

select

count(FSL >(select

avg(FSL)

from

FACT_DAY_SC

where DEPT_ID = '10001'

group by DEPT_ID) )

from

FACT_DAY_SC

where DEPT_ID = '10001' and substr(TJSJ_DATE ,1,7) = '2020-09'

image.png

8.指标分析

create table TEST(

DEP_CODE varchar(10),

ITEM_CODE varchar(10),

ITEM_VALUE int

);

insert into TEST values ('单位一','A',100);

insert into TEST values ('单位二','A',200);

insert into TEST values ('单位一','B',300);

insert into TEST values ('单位二','B',300);

insert into TEST values ('单位一','C',350);

insert into TEST values ('单位二','C',300);

select

DEP_CODE,

ITEM_CODE,

ITEM_VALUE

from

TEST

UNION ALL

select

DEP_CODE,

'D' as ITEM_CODE,

sum(if(ITEM_CODE = 'C',-ITEM_VALUE,ITEM_VALUE)) as ITEM_VALUE

from

TEST

group by DEP_CODE