数据库练习

实现如下(4 应为3):

7、删除表中序号ID更大的重复项

思路：表的自连接。查询出email相等且id大的，在删除

delete a from Person a ,Person b
where a.Email = b.Email and a.Id > b.Id   

8、

Trips 表中存所有出租车的行程信息。每段行程有唯一键 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。Status 是枚举类型，枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。

Users 表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止，Role 则是一个表示（‘client’, ‘driver’, ‘partner’）的枚举类型。

Trips表

 Users表

写一段 SQL 语句查出 2013年10月1日 至 2013年10月3日 期间非禁止用户的取消率。基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。

 分析：1)、2)可以实现结果，但存在一定的问题，未考虑Driver_Id = Users_Id且被禁止的情况，因题目刚好Driver_Id均未被禁止，所以可以实现结果，1）2）还待需改进。1）改进可以在where后加and Driver_Id <>

1）case when 

select t.Request_at Day ,    
round(sum(case when t.Status <> ‘completed‘ then 1 else 0 end)/count(t.Request_at),2) as `Cancellation Rate` from 
(
select * from Trips where Client_Id <> (
    select Users_Id from Users where Banned = ‘Yes‘ and Role = ‘client‘  
    
)
)t 
where t.Request_at between "2013-10-01" and "2013-10-03" 
group by t.Request_at  

2) 左连接

select a.Request_at Day,
    round(sum(case when a.Status <> ‘completed‘ then 1 else 0 end)/count(a.Request_at),2) as `Cancellation Rate` from 
    Trips a left join Users b
    on a.Client_Id = b.Users_Id 
    where  b.Banned ="No" and a.Request_at between "2013-10-01"  and "2013-10-03"
    group by a.Request_at

3) if

select a.Request_at Day, 
    round(sum( if(a.Status = ‘completed‘,0,1) ) / count(a.Status),2) as `Cancellation Rate`
    from Trips a join Users b on
    a.Client_Id = b.Users_Id and b.Banned = ‘No‘
    join Users c on
    a.Driver_Id = c.Users_Id and c.Banned = ‘No‘
    where a.Request_at between "2013-10-01"  and "2013-10-03"
    group by a.Request_at

4) 方法2改进 左连接

select a.request_at as Day, 
	round( 
		sum( 
				if (a.Status = ‘completed‘,0,1)
			) 
			/ 
			count(a.Status),
			2
		) as `Cancellation Rate`
from trips a left join 
(
	select users_id from Users 
	where banned = ‘Yes‘
) b on (a.Client_Id  = b.users_id)
left join (
	select users_id from Users 
	where banned = ‘Yes‘
) c on (a.Driver_Id = c.users_id)
where b.users_id is null and c.users_id is null
and a.Request_at  between ‘2013-10-01‘ and ‘2013-10-03‘
group by a.Request_at;

8、座位表

小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。其中纵列的 id 是连续递增的，小美想改变相邻俩学生的座位。学生总数奇数，最后一位不换位置。偶数需要换。

seat表：

 实现效果如下：

思路：操作id，偶数id-1，奇数id+1，奇数id最大时，id不变

1）case when 

select
	(case 
		when id % 2 = 0 then id - 1
		when id = (select max(id) from seat) then id 
		else id +1 end) id  , student 
	from seat order by id ;

2) if

select 
	if (mod(id,2)=0,id-1,if(id =(select max(id) from seat),id,id+1)) id ,student
	from seat 
	order by id ;

3) union 、自连接

( select s1.id ,s2.student
from seat s1, seat s2
where s1.id = s2.id -1 and s1.id MOD 2 =1
 )
union 
( select s1.id, s2.student 
from seat s1, seat s2 
 where s1.id = s2.id + 1 and s1.id MOD 2 = 0
)
union 
(
select s1.id, s1.student
from seat s1, seat s2
where s1.id mod 2 = 1 and s1.id = (select max(id) from seat ) 
)
order by id

数据库练习

标签：student   and   操作   编号   分析   create   group   _id   manage