我做了一个旅游报名程序，以下是报名表(Regist)
-- fid为所带家属的id字符串，
--route为选择的线路id | fid | route1 |1,2 | A
2 |3,4 | B
3 |5,6 | A家属表 family
--子女收集身高信息，父母收集年龄。id 与Regist.fid关联
id | relation | height | age1 |子女 | 120 |null
2 |父亲 | null|60
3 |子女 | 110 |null
4 |父亲 | null|65
5 |子女 | 110 |null
6 |父亲 | null|65 有一个用来设置统计结果的表。
--toplimit上限,downlimit下限
--对子女来说是身高，对父亲来说是年龄，这些纪录是动态添加的relation |toplimit | downlimit子女 |100 | 110
子女 |111 | 120
子女 |121 | 130
父亲 |50 | 60
父亲 |61 | 70
===》要求的统计人数结果 子女（身高） 父亲年龄
线路 | 100-110 |111-120| 121-130| 50-60|61-70
----------------------------------------------
A|1|1|0|1|1B|1|0|0|0|1这个该如何实现？
if object_id('tempdb.dbo.#1') is not null drop table #1
create table #1(id int, fid varchar(8), route varchar(8))
insert into #1
select 1, '1,2', 'A' union all
select 2, '3,4', 'B' union all
select 3, '5,6', 'A'
--> 测试数据：#2
if object_id('tempdb.dbo.#2') is not null drop table #2
create table #2(id int, relation varchar(8), height int, age int)
insert into #2
select 1, '子女', 120, null union all
select 2, '父亲', null, 60 union all
select 3, '子女', 110, null union all
select 4, '父亲', null, 65 union all
select 5, '子女', 110, null union all
select 6, '父亲', null, 65
--> 测试数据：#3
if object_id('tempdb.dbo.#3') is not null drop table #3
create table #3(relation varchar(8), toplimit int, downlimit int)
insert into #3
select '子女', 100, 110 union all
select '子女', 111, 120 union all
select '子女', 121, 130 union all
select '父亲', 50, 60 union all
select '父亲', 61, 70--> case when
select a.route,
[100-110] = sum(case when c.toplimit=100 and c.downlimit=110 then 1 else 0 end),
[111-120] = sum(case when c.toplimit=111 and c.downlimit=120 then 1 else 0 end),
[121-130] = sum(case when c.toplimit=121 and c.downlimit=130 then 1 else 0 end),
[50-60] = sum(case when c.toplimit=50 and c.downlimit=60 then 1 else 0 end),
[61-70] = sum(case when c.toplimit=61 and c.downlimit=70 then 1 else 0 end)
from #1 a, #2 b, #3 c
where ','+a.fid+',' like '%,'+ltrim(b.id)+',%'
and b.relation=c.relation and isnull(b.height,b.age) between c.toplimit and c.downlimit
group by a.route/*
route 100-110 111-120 121-130 50-60 61-70
-------- ----------- ----------- ----------- ----------- -----------
A 1 1 0 1 1
B 1 0 0 0 1
*/--> 2005/pivot
select
route, [100-110], [111-120], [121-130], [50-60], [61-70]
from
(
select a.route, ltrim(c.toplimit)+'-'+ltrim(c.downlimit)r from #1 a, #2 b, #3 c
where ','+a.fid+',' like '%,'+ltrim(b.id)+',%'
and b.relation=c.relation and isnull(b.height,b.age) between c.toplimit and c.downlimit
) t
pivot
(
count(r) for r in ([100-110], [111-120], [121-130], [50-60], [61-70])
) p/*
route 100-110 111-120 121-130 50-60 61-70
-------- ----------- ----------- ----------- ----------- -----------
A 1 1 0 1 1
B 1 0 0 0 1
*/
declare @sql varchar(8000)
set @sql='select a.route'
select @sql=@sql +',sum(case when c.toplimit='+ltrim(toplimit)+' and c.downlimit='+ltrim(downlimit)+' then 1 else 0 end )as ['+ltrim(toplimit)+'-'+ltrim(downlimit)+']'
from (select toplimit,downlimit from #3) m
--print @sql
set @sql=@sql+'from #1 a, #2 b, #3 c
where '+''',''+'+'a.fid'+'+'','''+' like '+'''%,''+'+'ltrim(b.id)'+'+'',%'''+'
and b.relation=c.relation and isnull(b.height,b.age) between c.toplimit and c.downlimit
group by a.route'
print @sql
exec(@sql)
route 100-110 111-120 121-130 50-60 61-70
-------- ----------- ----------- ----------- ----------- -----------
A 1 1 0 1 1
B 1 0 0 0 1(2 行受影响)
create table Regist
( [id] int,
[fid] varchar(100),
[route] varchar(10)
)create table Family
( [id] int,
[relation] varchar(100),
[hegiht] int,
[age] int
)create table Relation_limit
( relation varchar(100),
toplimit varchar(10),
downlimit varchar(10)
)insert into Regist
select 1,'1,2','A' union all
select 2,'3,4','B' union all
select 3,'5,6','A' insert into Family
select 1,'子女',120,null union all
select 2,'父亲',null,60 union all
select 3,'子女',110,null union all
select 4,'父亲',null,65 union all
select 5,'子女',110,null union all
select 6,'父亲',null,65 insert into Relation_limit
select '子女',100,110 union all
select '子女',111,120 union all
select '子女',121,130 union all
select '父亲',50,60 union all
select '父亲',61,70
if exists( select 1 from sys.objects where name = 'relation_pivot')
begin
drop table relation_pivot
end
select b.route,
case when a.relation='父亲'
then '父亲(年龄)'+' '+a.toplimit+'-'+a.downlimit
when a.relation='子女'
then '子女(身高)'+' '+a.toplimit+'-'+a.downlimit
end as relation,
count(b.fid) as person_num
into relation_pivot
from Relation_limit a
left outer join (
select a.route,a.fid,b.relation,isnull(b.hegiht,b.age) as val
from (
select a.id,
substring(a.fid,b.number,charindex(',',a.fid+',',b.number+1)-b.number) as fid,
a.route
from Regist a
inner join master.dbo.spt_values b
on substring(','+a.fid,b.number,1) = ','
where b.type = 'P' and b.number > 0
) a
inner join Family b
on a.fid = b.id
) b
on a.relation = b.relation
and b.val between a.toplimit and a.downlimit
where b.route is not null
group by b.route,
case when a.relation='父亲'
then '父亲(年龄)'+' '+a.toplimit+'-'+a.downlimit
when a.relation='子女'
then '子女(身高)'+' '+a.toplimit+'-'+a.downlimit
end declare @pivot_name varchar(500),@select_name varchar(500)
select @pivot_name = '',@select_name = 'route,'select @pivot_name = @pivot_name+'['+relation+'],',
@select_name = @select_name + 'isnull([' + relation + '],0) as [' + relation + '],'
from ( select distinct
case when a.relation='父亲'
then '父亲(年龄)'+' '+a.toplimit+'-'+a.downlimit
when a.relation='子女'
then '子女(身高)'+' '+a.toplimit+'-'+a.downlimit
end as relation
from Relation_limit a
) a
order by relation-- 定义动态SQL
declare @sql varchar(8000)
select @sql = '
select ' + left(@select_name,len(@select_name)-1) + '
from relation_pivot a
pivot
(max(person_num) for relation in (' + left(@pivot_name,len(@pivot_name)-1) + ')) b
order by route'
exec (@sql)-- 结果
-- 结果
route [父亲(年龄) 50-60] [父亲(年龄) 61-70] [子女(身高) 100-110] [子女(身高) 111-120] [子女(身高) 121-130]
------ ------------ ------------ -------------- -------------- --------------
A 1 1 1 1 0
B 0 1 1 0 0
if object_id('tempdb.dbo.#1') is not null drop table #1
create table #1(id int, fid varchar(8), route varchar(8))
insert into #1
select 1, '1,2', 'A' union all
select 2, '3,4', 'B' union all
select 3, '5,6', 'A'
--> 测试数据：#2if object_id('tempdb.dbo.#2') is not null drop table #2
create table #2(id int, relation varchar(8), height int, age int)
insert into #2
select 1, '子女', 110, null union all
select 2, '父亲', null, 60 union all
select 3, '子女', 110, null union all
select 4, '父亲', null, 65 union all
select 5, '子女', 110, null union all
select 6, '父亲', null, 65
--> 测试数据：#3
if object_id('tempdb.dbo.#3') is not null drop table #3
create table #3(relation varchar(8), toplimit int, downlimit int)
insert into #3
select '子女', 100, 110 union all
select '子女', 111, 120 union all
select '子女', 121, 130 union all
select '父亲', 50, 60 union all
select '父亲', 100, 110declare @sql varchar(8000)
set @sql='select a.route'
select @sql=@sql +',sum(case when c.toplimit='+ltrim(toplimit)+' and c.downlimit='+ltrim(downlimit)+' then 1 else 0 end )as ['+ltrim(toplimit)+'-'+ltrim(downlimit)+']'
from (select toplimit,downlimit from #3) m
print @sql
set @sql=@sql+'from #1 a, #2 b, #3 c
where '+''',''+'+'a.fid'+'+'','''+' like '+'''%,''+'+'ltrim(b.id)'+'+'',%'''+'
and b.relation=c.relation and isnull(b.height,b.age) between c.toplimit and c.downlimit
group by a.route'
print @sql
exec(@sql)上面代码中如果子女身高和父亲年龄重叠，则会导致统计不正确！
再加上判断！ select @sql=@sql +',sum(case when b.height is not null and c.toplimit='+ltrim(toplimit)+' and c.downlimit='+ltrim(downlimit)+' then 1 else 0 end )as [Height'+ltrim(toplimit)+'-'+ltrim(downlimit)+']'+',sum(case when b.age is not null and c.toplimit='+ltrim(toplimit)+' and c.downlimit='+ltrim(downlimit)+' then 1 else 0 end )as [Age'+ltrim(toplimit)+'-'+ltrim(downlimit)+']'
set @sql1='select a.route'
select @sql1=@sql1 +',sum(case when c.toplimit='+ltrim(toplimit)+' and c.downlimit='+ltrim(downlimit)+' and c.relation = ''' + relation + ''' then 1 else 0 end )as '+quotename(relation+ ' ('+ltrim(toplimit)+'-'+ltrim(downlimit)+')')
from (select toplimit,downlimit,relation from #3) m
--print @sql
set @sql1=@sql1+'from #1 a, #2 b, #3 c
where '+''',''+'+'a.fid'+'+'','''+' like '+'''%,''+'+'ltrim(b.id)'+'+'',%'''+'
and b.relation=c.relation and isnull(b.height,b.age) between c.toplimit and c.downlimit
group by a.route'
print @sql1
exec(@sql1)