创建学生表,记录学号、出生日期。
Create table Stu
(
ID int ,
Birthday datetime
)
根据传入的年份,查询每个月出生的人数。
思路:
根据传入的年份,得到每个月出生的总人数,—为12个结果。
将12个结果组成一张临时表,查询出数据并删除该表。
存储过程:
create PROC [dbo].[P_GetCountByYear]
(
@Year int
)
AS
BEGIN
CREATE TABLE #TempCount
(
[MonthName] int PRIMARY KEY
,YearName nvarchar(20)
)
INSERT INTO #TempCount([MonthName],YearName)
SELECT 1,convert(nvarchar(4),@Year)
union
SELECT 2,convert(nvarchar(4),@Year)
union
SELECT 3,convert(nvarchar(4),@Year)
union
SELECT 4,convert(nvarchar(4),@Year)
union
SELECT 5,convert(nvarchar(4),@Year)
union
SELECT 6,convert(nvarchar(4),@Year)
union
SELECT 7,convert(nvarchar(4),@Year)
union
SELECT 8,convert(nvarchar(4),@Year)
union
SELECT 9,convert(nvarchar(4),@Year)
union
SELECT 10,convert(nvarchar(4),@Year)
union
SELECT 11,convert(nvarchar(4),@Year)
union
SELECT 12,convert(nvarchar(4),@Year)
SELECT #TempCount.YearName,#TempCount.[MonthName],ISNULL(nnt.CreatedCount,0) as CreatedCount FROM #TempCount
LEFT JOIN
(
SELECT COUNT(Id) as CreatedCount,CreatedMonth,CreatedYear FROM
(
SELECT Id,datepart(year,Birthday) as CreatedYear ,datepart(month,Birthday) as CreatedMonth FROM Stu
WHERE datepart(year,Birthday)=@Year
) nt
GROUP BY CreatedYear,CreatedMonth
) nnt on nnt.CreatedYear = #TempCount.YearName and nnt.CreatedMonth = #TempCount.[MonthName]
order by [MonthName]
drop table #TempCount
END
解析:
1.@Year为输入的参数变量
2.#TempCount为创建的临时表
3.insert into .T1.select ..T2..语句为:将后一个表的数据复制到另一张表T1上
4.datepart(year,Birthday)函数为,截取日期的某个部分"年"、"月"、"日"
如:
datepart(year,‘2012-02-22’)为2012
datepart(month,‘2012-02-22’)为2
datepart(day,‘2012-02-22’)为22
5. left join 为左连接
如:
A left join B on A.id = B.id
结果仍为A的结构,其中B为包含的数据填充为'NULL'
6.ISNULL()为替换函数
如:
ISNULL(nnt.CreatedCount,0)为若列"nnt.CreatedCount"的值为NULL,则用0代替
7.union连接符
存储过程含义insert into select join == insert into select 另一条语句insert into select