求助：帮忙修正access中根据出生日期统计年龄段的查询语句

李沐阳　发表于：2016-04-27 23:21:03 　

我想根据出生日计算出年龄后统计年龄段，语句如下：

SELECT SUM(iif(datediff(year, 出生日, date())) BETWEEN 21 AND 30 THEN 1 ELSE 0 END) AS '21-30',
SUM(iif(datediff(year, 出生日, date())) BETWEEN 31 AND 40 THEN 1 ELSE 0 END) AS '31-40',
SUM(iif(datediff(year, 出生日, date())) BETWEEN 41 AND 50 THEN 1 ELSE 0 END) AS '41-50'
FROM sjk

执行语句时提示语法错误，请各位专家帮忙看看，怎么修正，多谢！！！

Top

麥田发表于：2016-04-27 23:30:20

【Access交叉表查询】交叉表查询实现年龄分字段统计记录数及合计工资总额的示例[Access软件网]
http://www.accessoft.com/article-show.asp?id=9552

Top

李沐阳发表于：2016-04-27 23:57:20

就是需要实现这样的年龄段统计功能

Top

朱先生发表于：2016-04-28 02:04:19

SELECT IIf(Year(Date())-Year([出生日]) Between 20 And 30,Year(Date())-Year([出生日]),"") AS ['21-30'], IIf(Year(Date())-Year([出生日]) Between 30 And 40,Year(Date())-Year([出生日]),"") AS ['31-40'], IIf(Year(Date())-Year([出生日]) Between 40 And 50,Year(Date())-Year([出生日]),"") AS ['41-50']
FROM SJK;

Top

李沐阳发表于：2016-04-28 07:19:31

哈哈，出结果了，多谢朱先生了！但是没有sum函数，如果按照性别统计以上年龄段就更好了，

附图

Top

朱先生发表于：2016-04-28 08:47:08

SELECT SJK.性别, Sum(IIf(Year(Date())-Year([出生日]) Between 20 And 30,1,0)) AS ['21-30'], Sum(IIf(Year(Date())-Year([出生日]) Between 30 And 40,1,0)) AS ['31-40'], Sum(IIf(Year(Date())-Year([出生日]) Between 40 And 50,1,0)) AS ['41-50']
FROM SJK
GROUP BY SJK.性别;

Top

梧桐细雨发表于：2016-04-28 13:42:40

这里有个适合你的例子

http://www.accessoft.com/article-show.asp?id=10676

其中使用了IIf，得以方便统计“各单位各年龄段人数”。
关于年龄查询的sql语句如下：
Select 花名册.ID, 花名册.单位, 花名册.姓名, 花名册.性别, 花名册.出生年月, 花名册.技术职务, Year(Date())-Year([出生年月]) AS 年龄, IIf([年龄]>=30 And [年龄]<40,"30-39岁",IIf([年龄]>=40 And [年龄]<50,"40-49岁",IIf([年龄]>=50,">=50岁","30岁以下"))) AS 年龄分布
FROM 花名册;

Top

李沐阳发表于：2016-05-03 17:15:53

多谢各位专家热情的解答，问题已经解决，谢谢各位专家！

Top

总记录：7篇页次：1/1 9 1 :

Access交流中心

求助：帮忙修正access中根据出生日期统计年龄段的查询语句