[leetcode]Trips and Users
来源:互联网 发布:海盗船风扇控制软件 编辑:程序博客网 时间:2024/05/22 23:41
select Request_at as Day,round((max(counter)*1.0/count(Request_at)),2) as 'Cancellation Rate'from ( select Id,Status,Request_at, @counter:=if(@curDate=Request_at and Status <> 'completed',@counter+1,0) as temp_c,# same day @counter:=if(@curDate<>Request_at and Status <>'completed',1, @counter) as counter,# different day @curDate:=Request_at as curDate from (select * from Trips left join Users on Client_Id=Users_Id where Banned<>'Yes' and request_at >= '2013-10-01' and request_at <= '2013-10-03' order by Request_at) as temp_t, (select @count:=0,@curDate:='') as params ) as temp_ttgroup by Request_at
附
use test;create table Trips(Id int auto_increment primary key,Client_Id int,Driver_Id int,City_Id int,Status varchar(50),Request_at date);create table Users(Users_Id int auto_increment primary key,Banned varchar(10),Role varchar(20));alter table Users modify column Users_Id int;#丢弃自增insert into trips(Client_Id,Driver_Id,City_Id,Status,Request_at) values(1,10,1,'completed','2013-10-01'),(2,11,1,'cancelled_by_driver','2013-10-01'),(3,12,6,'completed','2013-10-01'),(4,13,6,'cancelled_by_client','2013-10-01'),(1,10,1,'completed','2013-10-02'),(2,11,6,'completed','2013-10-02'),(3,12,6,'completed','2013-10-02'),(2,12,12,'completed','2013-10-03'),(3,10,12,'completed','2013-10-03'),(4,13,12,'cancelled_by_driver','2013-10-03');insert into Users(Users_Id,Banned,Role) values(1,' No','client'),(2,'Yes','client'),(3,' No','client'),(4,' No','client'),(10,'No','driver'),(11,'No','driver'),(12,'No','driver'),(13,'No','driver');
0 0
- [leetcode]Trips and Users
- LeetCode 262. Trips and Users
- leetcode 262. Trips and Users
- [LeetCode]--262. Trips and Users
- [Leetcode] [Database] Trips and Users解题思路
- leetcode-database-262. Trips and Users
- 【leetcode Database】262. Trips and Users
- Trips and Users --Hard
- LeetCode_OJ【262】Trips and Users
- Trips and Users ---刷完database 留念
- Users and Groups----note
- SYS and SYSTEM Users
- SQL Server Logins and Users - Orphaned Users
- Linux for Programmers and Users
- Users and Groups in Linux
- Difference for Logins and Users
- Main Users and the pwd
- Oracle Default Users And Password
- #1082 : 然而沼跃鱼早就看穿了一切
- ARC内存管理
- 股票助手-您的理财好帮手
- Android helloword demo程序不能运行
- 普通屏幕元素与代码的对应关系
- [leetcode]Trips and Users
- 任务调度--使用java.util.Timer实现
- Ksoap2调用webservice中连接超时的判断方法(如何判断超时?)
- Http Post Get
- JNI字段描述符“([Ljava/lang/String;)V”
- js懒人工具包
- java之 ------ JUnit、注解、类加载器
- iOS – NSLog、UncaughtException日志保存到文件
- Shell之-获取tomcat_Log