【MySQL笔记】存储过程

分页存储过程

CREATE PROC sp_page(
@table NVARCHAR(128),
@pageIndex INT,
@pageSize INT,
@whereCondition NVARCHAR(MAX)=NULL,
@totalCount INT OUT
)
AS
BEGIN
DECLARE @sql NVARCHAR(MAX),@countSql NVARCHAR(MAX),@begin INT,@end INT
SET @begin=(@pageIndex-1)*@pageSize+1
SET @end=@begin+@pageSize-1

SET @sql = N'SELECT * FROM(
SELECT *,ROW_NUMBER() OVER(ORDER BY student_id) rowNum FROM '+@table+
CASE WHEN @whereCondition is NOT NULL THEN ' WHERE '+@whereCondition ELSE '' END+
') temp
WHERE rowNum BETWEEN '+CAST(@begin AS VARCHAR(6))+' AND '+CAST(@end AS VARCHAR(6))

SET @countSql = N'SELECT @totalCount=COUNT(*) FROM '+@table+
CASE WHEN @whereCondition is NOT NULL THEN ' WHERE '+@whereCondition ELSE '' END

exec sp_executesql @countSql,N'@totalCount INT OUT',@totalCount OUTPUT
exec sp_executesql @sql
END

调用：

DECLARE @totalCount INT;
exec sp_page 'student',2,3,'1=1',@totalCount OUT;
PRINT @totalCount;

行转列(利用group by+case when)

select 
gender,
sum(case when birth_date>'2000/01/01 00:00:00' AND birth_date<'2001/01/01 00:00:00' then 1 else 0 end ) as '2000年出生',
sum(case when birth_date>'2001/01/01 00:00:00' AND birth_date<'2002/01/01 00:00:00' then 1 else 0 end ) as '2001年出生'
from student
group by gender;

连续3天登录

生成rowNumber，登录日期减rowNumber生成一个新日期列，分组判断数量是否大于3

WITH t1 AS (
SELECT DISTINCT ID,LoginDate,Name FROM LoginLog
),
t2 AS(
SELECT ID,Name,LoginDate,ROW_NUMBER() OVER (PARTITION BY Name ORDER BY LoginDate) rn FROM t1
),
t3 AS(
SELECT ID,Name,LoginDate,DATEADD(day,-rn,LoginDate) tempDate FROM t2
)
SELECT DISTINCT Name FROM t3 GROUP BY tempDate,Name HAVING COUNT(tempDate)>=3