我现在想求这样的一个问题:
我有这样的一张表:户主姓名,年龄1,年龄2,年龄3,年龄4,年龄5,年龄6
我现在想计算年龄1,年龄2,....中>80的数目,即年龄1,年龄2中如果大于90的话就算是一个,将这6个年龄中满足条件的数目统计出来,再加上户主姓名输出到另外一张表中,该如何实现?
用图表示如下:
户主姓名|年龄1|年龄2|年龄3|年龄4|年龄5|年龄6
张三 |80 |50 | 52 | 92 | 20 | 10李四 |75 |81 | 63 | 40 | 23 | 85小五 |93 |78 | 77 | 50 | 26 | 33我现在想得到这样的结果:
户主姓名|60<年龄<75|75<年龄<90|年龄>90该如何实现,先谢谢了!!
我有这样的一张表:户主姓名,年龄1,年龄2,年龄3,年龄4,年龄5,年龄6
我现在想计算年龄1,年龄2,....中>80的数目,即年龄1,年龄2中如果大于90的话就算是一个,将这6个年龄中满足条件的数目统计出来,再加上户主姓名输出到另外一张表中,该如何实现?
用图表示如下:
户主姓名|年龄1|年龄2|年龄3|年龄4|年龄5|年龄6
张三 |80 |50 | 52 | 92 | 20 | 10李四 |75 |81 | 63 | 40 | 23 | 85小五 |93 |78 | 77 | 50 | 26 | 33我现在想得到这样的结果:
户主姓名|60<年龄<75|75<年龄<90|年龄>90该如何实现,先谢谢了!!
(
name varchar(10),
age1 int,
age2 int,
age3 int,
age4 int,
age5 int,
age6 int
)select * from #tempinsert into #temp values('张三',80,50,52,92,20,10)
insert into #temp values('李四',75,81,63,40,23,85)
insert into #temp values('小五',93,78,77,50,26,33)
select aaa.name 户主姓名,aaa.age6075 [60<年龄<75],bbb.age7590 [75<年龄<90],ccc.age90 [年龄>90]
from
(
select a.name,isnull(b.age6075,0) age6075
from #temp a,
(
select aa.name,isnull(count(*),0) as age6075 from
(
select name,age1 as age6075
from #temp
where age1>=60 and age1<75
union all
select name,age2
from #temp
where age2>=60 and age2<75
union all
select name,age3
from #temp
where age3>=60 and age3<75
union all
select name,age4
from #temp
where age4>=60 and age4<75
union all
select name,age5
from #temp
where age5>=60 and age5<75
union all
select name,age6
from #temp
where age6>=60 and age6<75
) aa
group by aa.name
) b
where a.name*=b.name
) aaa,
-----
(
select a.name,isnull(b.age7590,0) age7590
from #temp a,
(
select aa.name,isnull(count(*),0) as age7590 from
(
select name,age1 as age7590
from #temp
where age1>=75 and age1<90
union all
select name,age2
from #temp
where age2>=75 and age2<90
union all
select name,age3
from #temp
where age3>=75 and age3<90
union all
select name,age4
from #temp
where age4>=75 and age4<90
union all
select name,age5
from #temp
where age5>=75 and age5<90
union all
select name,age6
from #temp
where age6>=75 and age6<90
) aa
group by aa.name
) b
where a.name*=b.name
) bbb,
----
(
select a.name,isnull(b.age90,0) age90
from #temp a,
(
select aa.name,isnull(count(*),0) as age90 from
(
select name,age1 as age90
from #temp
where age1>=90
union all
select name,age2
from #temp
where age2>=90
union all
select name,age3
from #temp
where age3>=90
union all
select name,age4
from #temp
where age4>=90
union all
select name,age5
from #temp
where age5>=90
union all
select name,age6
from #temp
where age6>=90
) aa
group by aa.name
) b
where a.name*=b.name
) ccc
where aaa.name=bbb.name
and bbb.name=ccc.name