sql 差值_4道常见的SQL试题，你能答对多少？-CSDN博客

题目解析 | 爱数据助教

内容来源 | 爱数据学院9月SQL月考题

现有某地区某天的选举投票数据，ge数据表为投票记录，Candidates数据表候选人信息，根据已有数据用SQL知识解答下列问题:

第一题

随机抽选2名给Trump投票的投票人以及联系方式(结果包含name以及phone_number)。

解析

本题主要考察的是“随机”的概念，用到rand函数，并根据rand进行排序。括号中不需要填写任何内容，否则就不是“随机”了。

select name, phone_number
from ge
where vote = 'RP1'
order by rand
limit 2;

第二题

查询出投票时间截止到09:21:18时，超过5票的候选人信息以及票数。

解析

本题主要考察的是表关联、where与having的区别、以及聚合函数与group by的使用，当然也可以用子查询的方式，以下方法较为简洁。需要注意的是，在不用子查询的情况下，在where中不能筛选同一级select后面的count条件，这里涉及到SQL语句执行顺序，后面具体再单独讲，也可以自己去了解一下~

select C.Candidate, C.gender, C.party, count(ge.ssn)
from ge
join
Candidates C
on ge.vote = C.candidateId
where ge.voting_time <= '09:21:18'
group by C.Candidate
having count(ge.ssn)>5;

第三题

查询出投票时间截止到09:21:18时，连续获得3票及3票以上的候选人姓名以及获得投票的id，name，voting_time，连续获得投票的次数。

解析

这道题是考察“连续性”问题，是面试中经常考的一类题，主要是考察子查询、窗口函数的熟练使用，具体可以分为以下三步:

第一步:先用id减去根据vote分组按照id排序之后的序号，获得一个差值。如果同一个vote的差值相等，则说明相等的部分是连续的，代码实现与结果如下:

select id
,name
,voting_time
,vote
,id - row_number over(partition by vote order by id,voting_time) as
D_value
from ge
where ge.voting_time <= '09:21:18'
order by id

第二步:计算连续的次数，这里用窗口函数count(1) over(...)来进行计算，根据差值与同一vote进行分组计算，这里没有在over里进行排序，是因为后面需要查询出这连续的一组数据。具体代码以及结果如下:

select D.id
,D.name
,D.voting_tim
,D.vote
,count(1) over(partition by D.D_value,D.vote) as times
from(
select id
,name
,voting_time
,vote
,id - row_number over(partition by vote order by
id,voting_time) as D_value
from ge
where ge.voting_time <= '09:21:18'
-- order by id
)D
order by id;

第三步:查询出最后结果:根据题目要求，筛选出3票及3票以上的结果。

select c.Candidate, t.id, t.name, t.voting_time, t.times
from
(select D.id,D.name,D.voting_time,D.vote,count(1) over(partition by D.D_value,D.vote) as times
from(
select id
,name
,voting_time
,vote
,id - row_number over(partition by vote order by id,voting_time) as D_value
from ge
where ge.voting_time <= '09:21:18'
-- order by id
) D) t
-- order by id
left join
Candidates c
on t.vote = c.candidateId
where times >= 3;