我的Sql server数据库中有一字段“出生日期”,为日期型。
我想打出的表格如下:
男工 女工
16周岁
17周岁
.
.
.
大于45我想统计16周岁,17周岁...大于45的男工及女工人数。
请问如何统计呢?如何根据出生日期计算年龄(周岁),然后再统计?
我想打出的表格如下:
男工 女工
16周岁
17周岁
.
.
.
大于45我想统计16周岁,17周岁...大于45的男工及女工人数。
请问如何统计呢?如何根据出生日期计算年龄(周岁),然后再统计?
select
sum(case when datediff(year,出生日期,getdate() = 16 then 1 else then 0 end) as 16周岁.....
from table
select datediff(date-now) as older from table
后再上表查
(select datediff(year,a.出生日期,getdate()) as 年龄,count(*) as 男工
from table
where 性别 = '男'
group by datediff(year,a.出生日期,getdate())
order by datediff(year,a.出生日期,getdate()))a,
(select datediff(year,a.出生日期,getdate()) as 年龄,count(*) as 女工
from table
where 性别 = '女'
group by datediff(year,a.出生日期,getdate())
order by datediff(year,a.出生日期,getdate())) b,
(select distinct datediff(year,出生日期,getdate()) as '年龄' from table) c
where
c.年龄*=a.年龄 and c.年龄*=b.年龄
DataSet['年龄']:=YearsBetween(DataSet['出生日期'], now);
create table table1( 姓名 char(15),性别 CHAR(2),出生日期 datetime)
insert into table1 values('张三','男','1988-08-10')
insert into table1 values('李四','男','1985-08-10')
insert into table1 values('王五','男','1985-08-10')
insert into table1 values('李丽','女','1980-08-10')
insert into table1 values('张勇','男','1980-08-10')
insert into table1 values('赵燕','女','1982-08-10')
insert into table1 values('白雪','女','1983-08-09')
insert into table1 values('钱六','男','1981-08-12')
SQL语句如下:
select datediff(year,出生日期,getdate()) as 年龄,
count(case when 性别='男' then 姓名 end) as 男工,
count(case when 性别='女' then 姓名 end) as 女工
from table1
group by datediff(year,出生日期,getdate())
where datediff(year,出生日期,getdate())<45
是小于45岁吧????
结果集如下:
年龄 男工 女工
15 1 0
18 2 0
20 0 1
21 0 1
22 1 0
23 1 1
这么复杂呀,我就一个表呀。
where datediff(year,出生日期,getdate())<45
这句有语法错误,我是用adoquery调用的。
select datediff(year,出生日期,getdate()) as 年龄,
count(case when 性别='男' then 姓名 end) as 男工,
count(case when 性别='女' then 姓名 end) as 女工
from table1
where datediff(year,出生日期,getdate())<45
group by datediff(year,出生日期,getdate())
DATEDIFF
Returns the number of date and time boundaries crossed between two specified dates. Syntax
DATEDIFF ( datepart , startdate , enddate ) Arguments
datepartIs the parameter that specifies on which part of the date to calculate the difference. The table lists dateparts and abbreviations recognized by Microsoft® SQL Server™.Datepart Abbreviations
Year yy, yyyy
quarter qq, q
Month mm, m
dayofyear dy, y
Day dd, d
Week wk, ww
Hour hh
minute mi, n
second ss, s
millisecond ms
sum(case when 分厂='一分厂' and 性别='男' then 1 else 0 end) 一分厂男工,
sum(case when 分厂='一分厂' and 性别='女' then 1 else 0 end) 一分厂女工,
sum(case when 分厂='二分厂' and 性别='男' then 1 else 0 end) 二分厂男工,
sum(case when 分厂='二分厂' and 性别='女' then 1 else 0 end) 二分厂女工
from table1
where datediff(year,出生日期,getdate())<45
group by datediff(year,出生日期,getdate())
我用你的方法试了,在Sql server中查询时正常,但是当我在Delphi里运行时结果总是为0。
我的代码:
adoquery1.Close;
adoquery1.SQL.Clear;
adoquery1.SQL.Add('SELECT datediff(year, birthday, getdate()) AS 年龄,');
adoquery1.SQL.Add('COUNT(CASE WHEN sex=:a THEN name END) AS 男工,');
adoquery1.SQL.Add('COUNT(CASE WHEN sex =:b THEN name END) AS 女工');
adoquery1.SQL.Add(' FROM pcjtworkersdata WHERE factory =:c');
adoquery1.SQL.Add(' and datediff(year, birthday, getdate())=22');
adoquery1.SQL.Add(' GROUP BY datediff(year, birthday, getdate())');
adoquery1.SQL.Add(' ORDER BY 年龄 ');
adoquery1.Parameters.ParamByName('a').Value:='男';
adoquery1.Parameters.ParamByName('b').Value:='女';
adoquery1.Parameters.ParamByName('c').Value:='集团';
adoquery1.Open;
//birthday为出生日期,sex为性别
//工厂factory为集团,年龄为22岁的
edit1.Text:=inttostr( adoquery1.fieldbyname('男工').Asinteger);
结果应该为18,但是却为0,不知为什么?
sum(case when 性别='女' then 1 else 0 end) as 女工
from table1我用大力的方法统计的结果计算出来的男工和女工的和与上述select语句有出入,结果不一样,差了几十个呢,为什么呀?