我现在有个员工表T_A,现在我想得到一个表T_B 能够统计到每年员工的人数T_A员工 入职年份
a 2001-12-3
b 2002-4-1
c 2003-12-3
d 2004-11-4
e 2005-12-21
f 2006-6-12
g 2007-5-6
h 2008-10-29
T_B
年份 人数
2001 1
2002 2
2003 3
2004 4
2005 5
2006 6
2007 7==========请问如何实现啊
a 2001-12-3
b 2002-4-1
c 2003-12-3
d 2004-11-4
e 2005-12-21
f 2006-6-12
g 2007-5-6
h 2008-10-29
T_B
年份 人数
2001 1
2002 2
2003 3
2004 4
2005 5
2006 6
2007 7==========请问如何实现啊
(
select sum(1) from t_a where 入职年份<=a.入职年份
)as '人数'
from t_a a
这个显示的是每年的人数 并不是逐年增长的总人数啊
insert into t_a select 'a','2001-12-3'
insert into t_a select 'b','2002-4-1'
insert into t_a select 'c','2003-12-3'
insert into t_a select 'd','2004-11-4'
insert into t_a select 'e','2005-12-21'
insert into t_a select 'f','2006-6-12'
insert into t_a select 'g','2007-5-6'
insert into t_a select 'h','2008-10-29'
select distinct datepart(yy,入职年份) as '年份',
(
select sum(1) from t_a where 入职年份<=a.入职年份
)as '人数'
from t_a a年份 人数
2001 1
2002 2
2003 3
2004 4
2005 5
2006 6
2007 7
2008 8
婶婶的统计不出来吧?
create table T_A(员工 varchar(10),入职年份 datetime)
insert into T_A select 'a','2001-12-3'
insert into T_A select 'b','2002-4-1'
insert into T_A select 'c','2003-12-3'
insert into T_A select 'd','2004-11-4'
insert into T_A select 'e','2005-12-21'
insert into T_A select 'f','2006-6-12'
insert into T_A select 'g','2007-5-6'
insert into T_A select 'h','2008-10-29'select year(入职年份),(select count(1) from t_a where year(入职年份)<=year(a.入职年份)) from t_A a
一下多了好多人喂,上面应该加个distinct
如:select distinct year(入职年份),(select count(1) from t_a where year(入职年份)<=year(a.入职年份)) from t_A a
insert into T_A select 'a','2001-12-3'
insert into T_A select 'b','2002-4-1'
insert into T_A select 'c','2003-12-3'
insert into T_A select 'd','2004-11-4'
insert into T_A select 'e','2005-12-21'
insert into T_A select 'f','2006-6-12'
insert into T_A select 'g','2007-5-6'
insert into T_A select 'h','2008-10-29'select year(入职年份) as '年份',sum(1) as '人数' into T_B from T_A group by year(入职年份)select * from t_b年份 人数
----------- -----------
2001 1
2002 1
2003 1
2004 1
2005 1
2006 1
2007 1
2008 1(8 行受影响)
select distinct year(入职年份) as a ,(select count(1) from t_a
where year(入职年份)<=year(a.入职年份)) from t_a a9楼的最正确了
7楼的如果加一条
insert into t_a select 'a','2001-1-3'
就会有问题哦。
不过如果数据量大(尤其是年份比较多的)的时候性能会不太好可改一下:
如下:SELECT COUNT(*),b.years
FROM T_A a JOIN
(
SELECT DISTINCT YEAR(入职年份) as years FROM T_A
) b ON year(a.入职年份)<=b.years
GROUP BY b.years
join后的结果集大的多
create table t1(name varchar(10),time varchar(10))insert into t1
select 'a','2001-12-3' union all
select 'b','2002-4-1' union all
select 'c','2003-12-3' union all
select 'd','2004-11-4' union all
select 'e','2005-12-21' union all
select 'f','2006-6-12' union all
select 'g','2007-5-6' union all
select 'h','2008-10-29' union all
select 'i','2006-1-1'select left(time,4),count(*) from t1 group by left(time,4)