626. 換座位

https://leetcode.cn/problems/exchange-seats/

嗯，今天又看了看這個題目，發(fā)現(xiàn)用例已經(jīng)修復(fù)了。

# mysql && oracle
select floor((id - 1) / 2) * 2 + row_number() over(partition by floor((id - 1) / 2) order by id desc) id ,student
from seat
# mssql
select (id - 1) / 2 * 2 + row_number() over(partition by (id - 1) / 2 order by id desc) id ,student
from seat
# mssql && mysql
# 這個是抄評論里的內(nèi)容，可惜不直到怎么調(diào)整的能夠支持 oracle
select rank() over (order by (id - 1) ^ 1) id, student
from Seat

1280. 學(xué)生們參加各科測試的次數(shù)

https://leetcode.cn/problems/students-and-examinations/

額。。。統(tǒng)計所有人，所有科目，各進行了幾次考核。。。那么人員和科目只能最大化交叉查詢了，然后再去關(guān)聯(lián)考試次數(shù)。

其實指令是一拖三的，把空值處理換成各自的函數(shù)即可：

oracle ： nvl
mysql ： ifnull
mssql ： isnull

# oracle
select a.*,nvl(cnt,0) attended_exams 
from (
    select * from students,subjects
) a
left join (
    select student_id,subject_name,count(0) cnt
    from examinations
    group by student_id,subject_name
) b on a.student_id=b.student_id and a.subject_name=b.subject_name
order by a.student_id,a.subject_name

CSDN 文盲老顧的博客，https://blog.csdn.net/superwfei

1321. 餐館營業(yè)額變化增長

https://leetcode.cn/problems/restaurant-growth/

額。。。。根據(jù)日期進行關(guān)聯(lián)，還得限定輸出的日期范圍，難度不高，就是寫起來挺麻煩，這就已經(jīng)接近日常工作的樣子了。

主要的是 mysql 和 mssql 的 datediff 的用法，差異不小哦，需要仔細查詢各自的語法和差計算方式，哪個日期在前，哪個日子在后。

# mysql
select a.visited_on,sum(c.amount) amount,round(sum(c.amount) / 7,2) average_amount
from (
    select distinct visited_on 
    from customer c
    where exists(
        select 1 
        from customer
        where datediff(c.visited_on,visited_on)=6
    )
) a
inner join customer c on datediff(a.visited_on,c.visited_on)<=6 and datediff(a.visited_on,c.visited_on) >= 0
group by a.visited_on
order by a.visited_on
# mssql
select a.visited_on,sum(c.amount) amount,convert(decimal(16,2),sum(c.amount) * 1.0 / 7) average_amount
from (
    select distinct visited_on 
    from customer c
    where exists(
        select 1 
        from customer
        where datediff(d,visited_on,c.visited_on)=6
    )
) a
inner join customer c on datediff(d,c.visited_on,a.visited_on)<=6 and datediff(d,c.visited_on,a.visited_on) >= 0
group by a.visited_on
order by a.visited_on
# oracle
select to_char(a.visited_on,'YYYY-mm-DD') visited_on,sum(c.amount) amount,round(sum(c.amount) * 1.0 / 7,2) average_amount
from (
    select distinct visited_on 
    from customer c
    where exists(
        select 1 
        from customer
        where c.visited_on - visited_on = 6
    )
) a
inner join customer c on c.visited_on between a.visited_on - 6 and a.visited_on
group by a.visited_on
order by a.visited_on

1327. 列出指定時間段內(nèi)所有的下單產(chǎn)品

https://leetcode.cn/problems/list-the-products-ordered-in-a-period/

嗯，日期范圍，可以用 between 來描述，這樣就可以一拖三了。至于不少于100的銷售量，用having聚合篩選一下就好。

select p.product_name,sum(o.unit) unit 
from products p
inner join orders o on p.product_id=o.product_id
where o.order_date between '2020-2-1' and '2020-2-29'
group by p.product_name
having(sum(o.unit)>=100)

1341. 電影評分

https://leetcode.cn/problems/movie-rating/

額。完全兩個不同的查詢條件，聚合方式，然后得到兩個結(jié)果集，放到一起輸出。。。這個考的內(nèi)容，不具有通用性。。。。算了，隨便寫寫吧。

嗯，下邊的內(nèi)容，并不是某數(shù)據(jù)只能使用這一種方式，而是，在不同的數(shù)據(jù)里，用不同的方式進行實現(xiàn)了，小伙伴們也可以用任意數(shù)據(jù)庫按照特定方式去實現(xiàn)查詢結(jié)果。

# mysql
select * 
from (
    select name results
    from movierating m
    inner join users u on m.user_id=u.user_id
    group by name
    order by count(0) desc,name
    limit 0,1
) a
union all
select * 
from (
    select title results
    from movierating r
    inner join movies m on r.movie_id=m.movie_id
    where created_at between '2020-2-1' and '2020-2-29'
    group by title
    order by avg(rating) desc,title
    limit 0,1
) b
# oracle
select * 
from (
    select u.name results
    from movierating m
    inner join users u on m.user_id=u.user_id
    group by name
    order by count(0) desc,name
) a
where rownum=1
union all
select * 
from (
    select m.title results
    from movierating r
    inner join movies m on r.movie_id=m.movie_id
    where r.created_at between '2020-2-1' and '2020-2-29'
    group by title
    order by avg(r.rating) desc,title
) b
where rownum=1
# 一拖三，除了 mssql， * 1.0 都可以省略
select a.name results 
from (
    select a.*,row_number() over(partition by tp order by cnt desc,name) nid 
    from (
        select u.name,sum(1) over(partition by u.user_id order by r.created_at) cnt,'1' tp
        from users u
        inner join MovieRating r on u.user_id=r.user_id
        union all
        select m.title,avg(rating * 1.0),'2' tp 
        from Movies m
        inner join MovieRating r on m.movie_id=r.movie_id
        where created_at >= '2020-2-1' and created_at < '2020-3-1'
        group by title
    ) a
) a
where a.nid=1

1378. 使用唯一標(biāo)識碼替換員工ID

https://leetcode.cn/problems/replace-employee-id-with-the-unique-identifier/

啊哦，又考回到 left join 了，完全沒什么其他想法啊。

select u.unique_id,e.name 
from Employees e
left join EmployeeUNI u on e.id=u.id

1393. 股票的資本損益

https://leetcode.cn/problems/capital-gainloss/

嗯？這個題目居然被定為中等難度？沒看出來啊，不還是 group 和 sum 的問題嗎？哦，難道一個 case when 就算一個難度了？

# 一拖三
select a.stock_name,sum(p) capital_gain_loss 
from (
    select s.*,(case when operation = 'Buy' then -price else price end) p
    from stocks s
) a
group by a.stock_name

1407. 排名靠前的旅行者

https://leetcode.cn/problems/top-travellers/submissions/

額。。。還是 left join 一個聚合結(jié)果。。。

# oracle
select a.name,nvl(b.d,0) as travelled_distance 
from users a 
left join (
    select user_id,sum(distance) d
    from rides
    group by user_id
) b on a.id=b.user_id 
order by nvl(b.d,0) desc,a.name
# mssql
select name,isnull(d,0) travelled_distance
from users a
left join (
    select user_id,sum(distance) d
    from rides
    group by user_id
) b on a.id=b.user_id
order by d desc,name
# mysql
select name,ifnull(sum(distance),0) travelled_distance
from users a
left join rides r on r.user_id=a.id
group by a.id
order by travelled_distance desc,name

1484. 按日期分組銷售產(chǎn)品

https://leetcode.cn/problems/group-sold-products-by-the-date/

哦，終于用到 group_concat 或 for xml 了，可惜老顧對 oracle 不熟悉，不知道怎么在 oracle 里搞出這個結(jié)果

# mysql
select sell_date,count(distinct product) num_sold,group_concat(distinct product order by product) products
from Activities
group by sell_date
order by sell_date
# mssql
select distinct sell_date,len(products) - len(replace(products,',','')) + 1 num_sold,products 
from Activities a
cross apply (
    select stuff((
        select distinct ',' + product
        from Activities 
        where sell_date=a.sell_date
        order by ',' + product
        for xml path('')
    ),1,1,'') products
) b
order by sell_date

1517. 查找擁有有效郵箱的用戶

https://leetcode.cn/problems/find-users-with-valid-e-mails/

額。。。。這個題目，有官方正則支持的，很容易，而 mssql 因為沒有官方正則支持。。。只能驗字符串了。。。。還好 tsql 的 like 也有類似的正則用法，雖然并不是完整的正則。

# mysql
select * 
from Users
where mail regexp '^[a-zA-Z][a-zA-Z0-9_\.-]*@leetcode[\.]com$'
# oracle
select * 
from Users
where regexp_like(mail,'^[a-zA-Z][a-zA-Z0-9_\.-]*@leetcode[\.]com$')
# mssql
select *
from users 
where mail like '[a-zA-Z]%@leetcode.com'
and substring(mail,0,len(mail) - 12) not like '%[^a-zA-Z0-9_.-]%'

1527. 患某種疾病的患者

https://leetcode.cn/problems/patients-with-a-condition/

嗯嗯。這個題目，還是正則比 like 好用，尤其是 like 需要 or 的時候，效率太低了。

# mssql
select * 
from patients
where ',' + replace(conditions,' ',',') like '%,diab1%'
# mysql
select * 
from Patients
where conditions regexp '(?<![a-z0-9A-Z])DIAB1'
# oracle
select * 
from Patients
where regexp_like(conditions,'(^| )DIAB1')

小結(jié)

額，這次10個題目，算是考的比較全面了，尤其是分組字符串合并，是一個很常用的內(nèi)容，然后，就是正則這種匹配，也算是很常用的內(nèi)容了。

但是，在沒有正則的情況下，對于確定的字符串內(nèi)容，其實我們還是有變動的辦法的，嗯，這次有兩個題目就是用 like 進行變通得到正確結(jié)果的。

所以說，一定要了解清楚，到底每個指令，都有哪些功能，能進行怎樣的操作。

所有華麗的招式，都是有基礎(chǔ)的動作構(gòu)成的，只有了解了所有的基礎(chǔ)內(nèi)容，才能無招勝有招的完成所有需求。

文章來源地址http://www.zghlxwxcb.cn/news/detail-497960.html

到了這里，關(guān)于leetcode數(shù)據(jù)庫題第六彈的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！