Warning表
ID warningname condition
3 年龄大于30岁的人 datediff(year,birthday,getdate())>30
4 当月过生日的人 month(birthday)=month(getdate())
Person表
ID Name birthday
3EF71730-2F56-4198-A898-03F6C5F454D5 测试 NULL
AF23D452-1273-47DD-AC08-2D66C32EAD8C 321 1953-03-01 00:00:00.000
6FE54A1E-CC2F-48A4-80C0-31550429C662 *** 1953-03-01 00:00:00.000
712C7221-AC74-4357-9C55-E046B18E4A14 123 2013-11-20 00:00:00.000需要的效果,需要一个语句,读出warning表,并且加一列计算出符合条件的人数:
ID warningname condition 符合条件的人数
3 年龄大于30岁的人 datediff(year,birthday,getdate())>30
4 当月过生日的人 month(birthday)=month(getdate())
ID warningname condition
3 年龄大于30岁的人 datediff(year,birthday,getdate())>30
4 当月过生日的人 month(birthday)=month(getdate())
Person表
ID Name birthday
3EF71730-2F56-4198-A898-03F6C5F454D5 测试 NULL
AF23D452-1273-47DD-AC08-2D66C32EAD8C 321 1953-03-01 00:00:00.000
6FE54A1E-CC2F-48A4-80C0-31550429C662 *** 1953-03-01 00:00:00.000
712C7221-AC74-4357-9C55-E046B18E4A14 123 2013-11-20 00:00:00.000需要的效果,需要一个语句,读出warning表,并且加一列计算出符合条件的人数:
ID warningname condition 符合条件的人数
3 年龄大于30岁的人 datediff(year,birthday,getdate())>30
4 当月过生日的人 month(birthday)=month(getdate())
-- Author :fredrickhu(小F,向高手学习)
-- Date :2013-11-26 16:10:03
-- Verstion:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
-- Feb 10 2012 19:39:15
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[Warning]
if object_id('[Warning]') is not null drop table [Warning]
go
create table [Warning]([ID] int,[warningname] varchar(16),[condition] varchar(36))
insert [Warning]
select 3,'年龄大于30岁的人','datediff(year,birthday,getdate())>30' union all
select 4,'当月过生日的人','month(birthday)=month(getdate())'
--> 测试数据:[Person]
if object_id('[Person]') is not null drop table [Person]
go
create table [Person]([ID] uniqueidentifier,[Name] varchar(4),[birthday] datetime)
insert [Person]
select '3EF71730-2F56-4198-A898-03F6C5F454D5','测试',null union all
select 'AF23D452-1273-47DD-AC08-2D66C32EAD8C','321','1953-03-01 00:00:00.000' union all
select '6FE54A1E-CC2F-48A4-80C0-31550429C662','***','1953-03-01 00:00:00.000' union all
select '712C7221-AC74-4357-9C55-E046B18E4A14','123','2013-11-20 00:00:00.000'
--------------开始查询--------------------------
SELECT
a.warningname,a.condition,b.num AS 符合条件的人数
FROM
Warning AS a INNER JOIN
(
SELECT '年龄大于30岁的人' AS warningname,SUM (CASE WHEN datediff(year,birthday,getdate())>30 AND birthday IS NOT NULL THEN 1 ELSE 0 END) AS num FROM Person
UNION ALL
SELECT '当月过生日的人' AS warningname,SUM (CASE WHEN month(birthday)=month(getdate()) AND birthday IS NOT NULL THEN 1 ELSE 0 END) AS num FROM Person) AS b
ON a.warningname=b.warningname
----------------结果----------------------------
/* warningname condition 符合条件的人数
---------------- ------------------------------------ -----------
年龄大于30岁的人 datediff(year,birthday,getdate())>30 2
当月过生日的人 month(birthday)=month(getdate()) 1(2 行受影响)*/
isnull(@sql+' union all ','')+N'select N'''+
WarningName+''' as [WarningName],''' +
condition + ''' as [condition], count(1) as [人数] from #person where ' +
condition from #warning
exec(@sql)是不是这样?