sqlserver 查询练习

-- 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.