# 最新面试题集锦（1）-紫光数智电力SQL分析

#### 1.1

``````select
DEPT_ID,
year,
month,
FDL (
select
DEPT_ID,
year,
month,
FDL,
row_number() over(partition by DEPT_ID, year, month order by FDL desc) as rows
from (
select
DEPT_ID,
year(TJSJ_DATR) as year,
month(TJSJ_DATR) as month,
FDL
from
FACT_DAY_SC
)t
)where rows = 1;``````

#### 1.2

``````select
DEPT_ID
TJSJ_DATR
(
select
DEPT_ID,
TJSJ_DATR,
FDL
from
(
select
DEPT_ID,
TJSJ_DATR,
FDL
from
FACT_DAY_SC
where DEPT_ID = 1000000
order by TJSJ_DATR
)t
)t1``````

#### 1.3

``````select
DEPT_ID,
TJSJ_DATR,
FDL
from
(
select
DEPT_ID,
TJSJ_DATR,
FDL
from
(
select
DEPT_ID,
TJSJ_DATR,
FDL
from
FACT_DAY_SC
where DEPT_ID = 1000000
order by TJSJ_DATR
)t

#### 1.4

`````` select
dept_id,
tjsj_date,
fdl,
from  fact_day_sc
where tjsj_date='2011-8-1'
order by fdl desc
limit 3``````

#### 1.5

`` select date_diff(2020-12-31,2020-01-01)+1;``

#### 1.6

``````with tmp as (
select
dept_id,
tjsj_date,
fdl
from fact_day_sc
where dept_id='10000000' and month(tjsj_date)=8
)
select
count(if a.fdl>b.avgfdl,1,null) as days
from
(select fdl from tmp ) a
join
(select  avg(fdl) as avgfdl from tmp ) b``````

#### 1.7

``````存储过程调用
begin
xproc(参数)
end

select   函数(参数)  … from ….``````

1.8 1.9 1.10 都是存储过程的语法

DOIT.EDU 资料来自网络收集整理，如有侵权请告知

(1)

### 相关推荐 