列出给定年龄范围内的员工姓名 [英] List the name of employees within a given age range

问题描述

我必须列出属于给定年龄范围的员工的姓名。我是学习在SQL中使用变量/参数的新手，我很难获得输出。



我不知道如何设置变量，以便用户可以输入年龄范围，然后获取属于该范围的员工的姓名。





运行此查询时出现此错误



关键字'with'附近的语法不正确。如果此语句是公用表表达式，xmlnamespaces子句或更改跟踪上下文子句，则必须以分号终止先前的语句。

消息102，级别15，状态1，行72



我的尝试：

声明@AgeRange int 
设置@AgeRange = DATEDIFF（年份，'1946-06-08'，GETDATE（））
 
使用AgeData作为
（
 SELECT FirstName，LastName，
 DateOfBirth，
 DATEDIFF（YEAR，DateOfBirth，GETDATE（））AS AGE 
 FROM ptWMember as wm 
 JOB ptWorkingCertification as wc on wc.PK = wm。 fkptWCertification 
），
 GroupAge AS 
（
 SELECT FirstName，LastName，
 DateOfBirth，
年龄，
 CASE 
 WHEN @ AgeRange< 30 THEN'30岁以下'
当@AgeRange介于31和40之间时'31  -  40'
当@AgeRange介于41和50之间时41' -  50'
 W HEN @AgeRange> 50那么'超过50'
 ELSE'无效的出生日期'
结束AS [年龄组] 
来自AgeData 
）
 SELECT FirstName，LastName 
来自AgeData 

解决方案

更改此：

  WITH  AgeData  as  

to：

< pre lang =SQL>; WITH AgeData as

或使用[; ]完成上一行：

 设置  @ AgeRange  = DATEDIFF（年，'  1946-06-08'，GETDATE（））; 

I have to list the names of the employees who fall within a given age range. I am new in learning to use variables/parameters in SQL, and I am having a hard time to get an output.

I don't know how to set a variable so a user can enter a age range and then get the names of the employees who fall within that range.


I am getting this error when I run this query

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 72

What I have tried:

Declare @AgeRange int
Set @AgeRange = DATEDIFF(YEAR, '1946-06-08', GETDATE()) 

WITH AgeData as 
  (
    SELECT FirstName, LastName,
    DateOfBirth, 
    DATEDIFF(YEAR, DateOfBirth, GETDATE()) AS AGE
    FROM ptWMember as wm
    JOIN ptWorkingCertification as wc on wc.PK = wm.fkptWCertification
  ),
  GroupAge AS
(
  SELECT FirstName, LastName,
         DateOfBirth,
         Age,
         CASE
             WHEN @AgeRange < 30 THEN 'Under 30'
             WHEN @AgeRange BETWEEN 31 AND 40 THEN '31 - 40'
             WHEN @AgeRange BETWEEN 41 AND 50 THEN '41 - 50'
             WHEN @AgeRange > 50 THEN 'Over 50'
             ELSE 'Invalid Birthdate'
         END AS [Age Groups]
  FROM AgeData
 )
SELECT FirstName, LastName
FROM AgeData

解决方案

Change this:

WITH AgeData as 

to:

;WITH AgeData as 

or finish previous line with [;]:

Set @AgeRange = DATEDIFF(YEAR, '1946-06-08', GETDATE()); 

这篇关于列出给定年龄范围内的员工姓名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！