-- 1.0
use master
go
if exists(select * from master.dbo.sysdatabases where name='LeetCode')
begin
drop database LeetCode
select 1 as deleted_leetcode
end
create database LeetCode
use LeetCode
go
if exists(select * from sys.objects where name='friend_requests')
drop table friend_requests
create table friend_requests
(
sender_id int,
send_to_id int,
request_date date
)
insert into friend_requests values
(1, 2, '2016-06-01'),
(1, 3, '2016-06-01'),
(1, 4, '2016-06-01'),
(2, 3, '2016-06-02'),
(3, 4, '2016-06-09')
go
if exists(select * from sys.objects where name='request_accepted')
drop table request_accepted
create table request_accepted
(
requester_id int,
accepter_id int,
accept_date date
)
insert into request_accepted values
(1, 2, '2016-06-03'),
(1, 3, '2016-06-08'),
(2, 3, '2016-06-08'),
(3, 4, '2016-06-09'),
(3, 4, '2016-06-10')
go
select *
from friend_requests
select *
from request_accepted
-- 通过数/申请总数
declare @num1 float --decimal(p,s) float(n)
declare @num2 float
declare @num3 decimal(5,2)
set @num1=
(
select count(*)
from request_accepted
)
set @num2=
(
select count(*)
from
(
select distinct requester_id, accepter_id
from request_accepted
) a
)
--set @num3=@num2/@num1
--select @num3 as accept_rate
--print cast(@num2/@num1 as decimal(5,2))
--print convert(decimal(5,2), @num2/@num1)
print round(@num2/@num1, 2)
-- 2.convert(decimal(5,2), @num)
select convert(decimal(5,2),
isnull(
(select count(*) from (select distinct requester_id, accepter_id from request_accepted) b)
* 1.0
/
(select count(*) from (select distinct sender_id, send_to_id from friend_requests) a), 0)
) as rate
-- 进阶
1. 按年year;月month;日day汇总
2. DATEPART 获取日期/时间的单独部分
3. DATEADD
4. DATEDIFF
5. CONVERT(VARCHAR(20), GETDATE() [, 111; 112; 120])
eg.