最近写了一个人数统计的系统,要求是列及行都是动态生成的
比如:
数据库里面有个基础表A: 表A 姓名 部门 学历 出生年月
A 后勤 高中 1986-1-1
B 后勤 初中 1984-3-7
C 管理 本科 1987-2-1
D 操作 专科 1976-2-1
. . . .
. . . .
要求动态的生成一下效果的表:
部门 年龄段 高中 初中 本科.......
后勤 20-30 3 4 3 .......
30-40 3 9 0 .......
40-50 0 30 3 .......
管理 20-30 9 2 2 ....... 请问怎么实现啊???
比如:
数据库里面有个基础表A: 表A 姓名 部门 学历 出生年月
A 后勤 高中 1986-1-1
B 后勤 初中 1984-3-7
C 管理 本科 1987-2-1
D 操作 专科 1976-2-1
. . . .
. . . .
要求动态的生成一下效果的表:
部门 年龄段 高中 初中 本科.......
后勤 20-30 3 4 3 .......
30-40 3 9 0 .......
40-50 0 30 3 .......
管理 20-30 9 2 2 ....... 请问怎么实现啊???
比如:
select * from 表where 部门 = ‘’;
union
select * from 表where 管理 = ‘’;
union
select * from 表where 后勤 = ‘’;其他只是技术问题了
http://topic.csdn.net/u/20100121/11/891f547f-c16f-4a28-9342-83286b526b7d.html
--A 后勤 高中 1986-1-1
--B 后勤 初中 1984-3-7
--C 管理 本科 1987-2-1
--D 操作 专科 1976-2-1
drop function ageTime
go
--获取年龄段
create function ageTime(@date varchar(10))
returns varchar(10)
as
begin
--所除的整数
declare @i_year int,@result int
select @i_year=datediff(year,@date,getdate())
select @result=@i_year/10
if(@i_year%10>0)
begin
select @date=Convert(varchar(3),@result*10)+'-'+Convert(varchar(3),(@result+1)*10)
end
else
begin
select @date=Convert(varchar(3),(@result-1)*10)+'-'+Convert(varchar(3),@result*10)
end
return @date
end
goif object_id('tempdb.dbo.#tb') is not null drop table tempdb.#tb
go
create table #tb(姓名 varchar(20),部门 varchar(20),学历 varchar(20),出生年月 varchar(10),年龄段 varchar(10))
insert into #tb
select 'A','后勤','高中','1986-1-1',dbo.ageTime('1986-1-1') union all
select 'B','后勤','初中','1984-3-7',dbo.ageTime('1984-3-7') union all
select 'C','管理','专科','1987-2-1',dbo.ageTime('1987-2-1') union all
select 'D','操作','专科','1976-2-1',dbo.ageTime('1976-2-1') union all
select 'E','后勤','高中','1986-1-1',dbo.ageTime('1986-1-1') union all
select 'F','后勤','初中','1984-3-7',dbo.ageTime('1984-3-7') union all
select 'G','管理','专科','1987-2-1',dbo.ageTime('1987-2-1') union all
select 'H','操作','专科','1976-2-1',dbo.ageTime('1976-2-1') select * from #tb
go--适合于2005以上的
select 部门,年龄段,高中,初中,本科,小学
from #tb pivot(count(姓名) for 学历 in([高中],[初中],[本科],[小学])) as t order by 部门--sql2000
--用union all
后勤初中 20-30 3
后勤高中 30-40 4
.
.
.
管理初中 20-30 9
.
.
.
这样的话你就可以用
select xxxxx
from 表A inner join 表A
group by XXXXXX
这样来实现了如果你非要你的那种结果,你可以用上面的结果结合程序显示在前台,或者直接用程序统计出来显示在前台
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([姓名] varchar(1),[部门] varchar(4),[学历] varchar(4),[出生年月] datetime)
insert [tb]
select 'A','后勤','高中','1986-1-1' union all
select 'B','后勤','初中','1984-3-7' union all
select 'C','管理','本科','1987-2-1' union all
select 'D','操作','专科','1976-2-1'
--------------开始查询--------------------------
declare @sql varchar(8000)
set @sql = 'select 部门'
select @sql = @sql + ' , sum(case 学历 when ''' + 学历 + ''' then 1 else 0 end) [' + 学历 + ']'
from (select distinct 学历 from tb) as a
set @sql = @sql + ' from tb group by 部门'
exec(@sql)
----------------结果----------------------------
/* 部门 本科 初中 高中 专科
---- ----------- ----------- ----------- -----------
操作 0 0 0 1
管理 1 0 0 0
后勤 0 1 1 0(3 行受影响)*/
--A 后勤 高中 1986-1-1
--B 后勤 初中 1984-3-7
--C 管理 本科 1987-2-1
--D 操作 专科 1976-2-1
drop function ageTime
go
--获取年龄段
create function ageTime(@date varchar(10))
returns varchar(10)
as
begin
--所除的整数
declare @i_year int,@result int
select @i_year=datediff(year,@date,getdate())
select @result=@i_year/10
if(@i_year%10>0)
begin
select @date=Convert(varchar(3),@result*10)+'-'+Convert(varchar(3),(@result+1)*10)
end
else
begin
select @date=Convert(varchar(3),(@result-1)*10)+'-'+Convert(varchar(3),@result*10)
end
return @date
end
goif object_id('tempdb.dbo.#tb') is not null drop table tempdb.#tb
go
create table #tb(姓名 varchar(20),部门 varchar(20),学历 varchar(20),出生年月 varchar(10),年龄段 varchar(10))
insert into #tb
select 'A','后勤','高中','1986-1-1',dbo.ageTime('1986-1-1') union all
select 'B','后勤','初中','1984-3-7',dbo.ageTime('1984-3-7') union all
select 'C','管理','专科','1987-2-1',dbo.ageTime('1987-2-1') union all
select 'D','操作','专科','1976-2-1',dbo.ageTime('1976-2-1') union all
select 'E','后勤','高中','1986-1-1',dbo.ageTime('1986-1-1') union all
select 'F','后勤','初中','1984-3-7',dbo.ageTime('1984-3-7') union all
select 'G','管理','专科','1987-2-1',dbo.ageTime('1987-2-1') union all
select 'H','操作','专科','1976-2-1',dbo.ageTime('1976-2-1') select * from #tb
go
--适合于2005以上的
select 部门,年龄段,sum(高中),sum([初中]),sum([本科])
from
(
select 部门,年龄段,高中,初中,本科
from #tb pivot(count(姓名) for 学历 in([高中],[初中],[本科])) as t
) as tb group by 部门,年龄段
go
--sql2000
--用union alldeclare @sql varchar(8000)
set @sql = 'select 部门,年龄段'
select @sql = @sql + ' , sum(case 学历 when ''' + 学历 + ''' then 1 else 0 end) [' + 学历 + ']'
from (select distinct 学历 from #tb) as a
set @sql = @sql + ' from #tb group by 部门,年龄段'
exec(@sql)
set @sql = 'select department,CASE
WHEN 19<datediff(yy, Birthday, getdate()) and datediff(yy, Birthday, getdate())< 30 THEN '20-29'
WHEN 29<datediff(yy, Birthday, getdate()) and datediff(yy, Birthday, getdate())< 40 THEN '30-39'
WHEN 39<datediff(yy, Birthday, getdate()) and datediff(yy, Birthday, getdate())< 50 THEN '40-49'
WHEN 49<datediff(yy, Birthday, getdate()) and datediff(yy, Birthday, getdate())< 60 THEN '50-59'
WHEN 59<datediff(yy, Birthday, getdate()) THEN '60岁以上' end as 年龄段'
select @sql = @sql + ' , sum(case LatterEducation when ''' + LatterEducation + ''' then 1 else 0 end) [' + LatterEducation+ ']'
from (select distinct LatterEducation from v_hdemployee) as a
set @sql = @sql + ' from v_hdemployee group by department,case
WHEN 19<datediff(yy, Birthday, getdate()) and datediff(yy, Birthday, getdate())< 30 THEN '20-29'
WHEN 29<datediff(yy, Birthday, getdate()) and datediff(yy, Birthday, getdate())< 40 THEN '30-39'
WHEN 39<datediff(yy, Birthday, getdate()) and datediff(yy, Birthday, getdate())< 50 THEN '40-49'
WHEN 49<datediff(yy, Birthday, getdate()) and datediff(yy, Birthday, getdate())< 60 THEN '50-59'
WHEN 59<datediff(yy, Birthday, getdate()) THEN '60岁以上' end'
exec(@sql)
drop function ageTime
go
--获取年龄段
create function ageTime(@date varchar(10))
returns varchar(10)
as
begin
--所除的整数
declare @i_year int,@result int
select @i_year=datediff(year,@date,getdate())
select @result=@i_year/10
--if(@i_year%10>=0)
--begin
select @date=Convert(varchar(3),@result*10)+'-'+Convert(varchar(3),(@result+1)*10-1)
--end
--else
--begin
-- select @date=Convert(varchar(3),(@result-1)*10)+'-'+Convert(varchar(3),@result*10)
--end
return @date
end
go--下面是修改你刚刚的T-SQL,你试试,如果有错误,发给我看一下...
set @sql = 'select department,年龄段'
select @sql = @sql + ' , sum(case LatterEducation when ''' + LatterEducation + ''' then 1 else 0 end) [' + LatterEducation+ ']'
from (select distinct LatterEducation from v_hdemployee) as a
set @sql = @sql + ' from (selct *,dbo.ageTime(Birthday) 年龄段 from v_hdemployee) as tb group by department,年龄段'exec(@sql)
go
--获取年龄段
create function ageTime(@date varchar(10))
returns varchar(10)
as
begin
--所除的整数
declare @i_year int,@result int
select @i_year=datediff(year,@date,getdate())
select @result=@i_year/10
--if(@i_year%10>=0)
--begin
select @date=Convert(varchar(3),@result*10)+'-'+Convert(varchar(3),(@result+1)*10-1)
--end
--else
--begin
-- select @date=Convert(varchar(3),(@result-1)*10)+'-'+Convert(varchar(3),@result*10)
--end
return @date
end 这段程序在什么地方运行啊??可以在存储过程中运行吗
--1。先运行这个函数
drop function ageTime
go
--获取年龄段
create function ageTime(@date varchar(10))
returns varchar(10)
as
begin
--所除的整数
declare @i_year int,@result int
select @i_year=datediff(year,@date,getdate())
select @result=@i_year/10
--if(@i_year%10>=0)
--begin
select @date=Convert(varchar(3),@result*10)+'-'+Convert(varchar(3),(@result+1)*10-1)
--end
--else
--begin
-- select @date=Convert(varchar(3),(@result-1)*10)+'-'+Convert(varchar(3),@result*10)
--end
return @date
end
go
-------------------------------------------------
-----------------------------
-------------------------------------------------if object_id('v_hdemployee') is not null drop table v_hdemployee
go
--创建表,添加记录
create table v_hdemployee(name varchar(20),department varchar(20),LatterEducation varchar(20),Birthday varchar(10))
insert into v_hdemployee
select 'A','后勤','高中','1986-1-1' union all
select 'B','后勤','初中','1984-3-7' union all
select 'C','管理','专科','1987-2-1' union all
select 'D','操作','专科','1976-2-1' union all
select 'E','后勤','高中','1986-1-1' union all
select 'F','后勤','初中','1984-3-7' union all
select 'G','管理','专科','1987-2-1' union all
select 'H','操作','专科','1976-2-1'select * from v_hdemployee
go
declare @sql varchar(8000)
select @sql='select department,年龄段'
select @sql=@sql+',sum(case LatterEducation when '''+LatterEducation+'''then 1 else 0 end) ['+LatterEducation+']'+char(10)+char(13)
from (select distinct LatterEducation from v_hdemployee) as a
select @sql=@sql+' from (select *,dbo.ageTime(Birthday) 年龄段 from v_hdemployee) as tb group by department,年龄段'print @sql--select department,年龄段,sum(case LatterEducation when '初中'then 1 else 0 end) [初中]
--,sum(case LatterEducation when '高中'then 1 else 0 end) [高中]
--,sum(case LatterEducation when '专科'then 1 else 0 end) [专科]
-- from (select *,dbo.ageTime(Birthday) 年龄段 from v_hdemployee) as tb group by department,年龄段
drop function ageTime
go
--获取年龄段
create function ageTime(@date varchar(10))
returns varchar(10)
as
begin
--所除的整数
declare @i_year int,@result int
select @i_year=datediff(year,@date,getdate())
select @result=@i_year/10
--if(@i_year%10>=0)
--begin
select @date=Convert(varchar(3),@result*10)+'-'+Convert(varchar(3),(@result+1)*10-1)
--end
--else
--begin
-- select @date=Convert(varchar(3),(@result-1)*10)+'-'+Convert(varchar(3),@result*10)
--end
return @date
end
go
-------------------------------------------------
-----------------------------
------------------------------------------------- if object_id('v_hdemployee') is not null drop table v_hdemployee
go
--创建表,添加记录
create table v_hdemployee(name varchar(20),department varchar(20),LatterEducation varchar(20),Birthday varchar(10))
insert into v_hdemployee
select 'A','后勤','高中','1986-1-1' union all
select 'B','后勤','初中','1984-3-7' union all
select 'C','管理','专科','1987-2-1' union all
select 'D','操作','专科','1976-2-1' union all
select 'E','后勤','高中','1986-1-1' union all
select 'F','后勤','初中','1984-3-7' union all
select 'G','管理','专科','1987-2-1' union all
select 'H','操作','专科','1976-2-1' select * from v_hdemployee
go declare @sql varchar(8000)
select @sql='select department,年龄段'
select @sql=@sql+',sum(case LatterEducation when '''+LatterEducation+'''then 1 else 0 end) ['+LatterEducation+']'+char(10)+char(13)
from (select distinct LatterEducation from v_hdemployee) as a
select @sql=@sql+' from (select *,dbo.ageTime(Birthday) 年龄段 from v_hdemployee) as tb group by department,年龄段' print @sql
exec @sql
http://www.cnblogs.com/zengxiangzhan/archive/2010/02/04/1663468.html
这个功能的实现,在VS里面需要用到什么控件啊,貌似GRIDVIEW不能使用啊