sqlserver- select + case……

-- 求问题的回答率
-- 回答率 = 回答数/显示数  即ratio = answer/show
USE master
GO

IF EXISTS(SELECT * FROM master.dbo.sysdatabases WHERE name='LeetCode')
	BEGIN
		DROP DATABASE LeetCode
		SELECT 1
	END
GO
CREATE DATABASE LeetCode
GO
USE LeetCode
GO

--IF EXISTS(SELECT * FROM sysobjects WHERE name='survey_log')
IF (OBJECT_ID('survey_log', 'U') IS NOT Null)
	DROP TABLE survey_log
GO
CREATE TABLE survey_log
(
	Id INT IDENTITY
		CONSTRAINT PK_surveyLog PRIMARY KEY,
	uid INT,
	action VARCHAR(20) 
		CONSTRAINT Check_action 
		CHECK(action IN ('show', 'skip', 'answer')),
		--CHECK(action='show' or action='answer' or action='skip'),
	--action VARCHAR(20),
	--CONSTRAINT Check_action CHECK(action in ('show', 'answer', 'skip')),
	question_id INT,
	answer_id INT, 
	q_num INT,
	timestamp INT,
	date DATETIME 
		CONSTRAINT Default_date DEFAULT GETDATE()
)
GO

INSERT INTO survey