34,590
社区成员
发帖
与我相关
我的任务
分享
;WITH Staff
AS
(
SELECT 部门编号
,CASE WHEN 年龄 BETWEEN 18 AND 20 THEN '18~20'
WHEN 年龄 BETWEEN 21 AND 30 THEN '20~30'
ELSE 'other'
END AS 年龄段
,姓名
FROM 员工表
)
SELECT a.部门名称
,b.年龄段
,COUNT(*) AS 人数
,姓名=STUFF((SELECT ','+姓名 FROM Staff WHERE 部门编号=b.部门编号 AND 年龄段=b.年龄段 FOR XML PATH('') ),1,1,'')
FROM 部门表 AS a
INNER JOIN Staff AS b ON a.部门编号 = b.部门编号
GROUP BY a.部门名称
,b.年龄段
,b.部门编号;
WITH dp(deptno,deptname) AS (
SELECT '001',N'销售部' UNION
SELECT '002',N'财务部' UNION
SELECT '003',N'人事部'
),emp(deptno,empno,fullname,age)AS(
SELECT '001','9527',N'赵六',19 UNION
SELECT '001','9527',N'张三',20 UNION
SELECT '001','9528',N'李四',25 UNION
SELECT '002','9529',N'王二',30 UNION
SELECT '003','9530',N'孙五',41
),stat AS (
SELECT dp.deptno,dp.deptname,COUNT(0) AS empcount,CASE WHEN emp.age BETWEEN 18 AND 20 THEN '18~20' WHEN emp.age BETWEEN 21 AND 30 THEN '21~30' ELSE 'other' END AS agerang
FROM emp LEFT JOIN dp ON dp.deptno=emp.deptno
GROUP BY dp.deptno, dp.deptname,CASE WHEN emp.age BETWEEN 18 AND 20 THEN '18~20' WHEN emp.age BETWEEN 21 AND 30 THEN '21~30' ELSE 'other' END
)
SELECT stat.deptname,stat.agerang,empcount,STUFF(emps.s,1,1,'') AS emps FROM stat
OUTER APPLY (SELECT ','+emp.fullname FROM emp WHERE deptno=stat.deptno AND stat.agerang=CASE WHEN emp.age BETWEEN 18 AND 20 THEN '18~20' WHEN emp.age BETWEEN 21 AND 30 THEN '21~30' ELSE 'other' END
FOR XML PATH('') ) emps(s)
CREATE TABLE #Department(Nr VARCHAR(50),Name NVARCHAR(50));
INSERT INTO #Department
( Nr, Name )
VALUES ( '001',N'人事部'),( '002',N'销售部'),( '003',N'财务部')
CREATE TABLE #Person(DeparNr VARCHAR(50),Nr VARCHAR(50),Name NVARCHAR(50),Age INT);
INSERT INTO #Person
( DeparNr, Nr, Name, Age )
VALUES ( '001', '9527', N'张三', 18 ),
( '001', '9528', N'李四', 25 ),
( '002', '9529', N'王五', 27 ),
( '003', '9530', N'赵六', 31 ),
( '001', '9531', N'钱七', 25 ),
( '001', '9532', N'孙八', 25 )
;WITH CTE AS
(
SELECT CONVERT(VARCHAR(50),'18~20') AS DisplayName,18 AS MinAge,20 AS MaxAge
UNION ALL
SELECT CONVERT(VARCHAR(50),RTRIM(CTE.MaxAge) + '~' + RTRIM(CTE.MaxAge+10)) ,CTE.MaxAge,CTE.MaxAge+10
FROM CTE
WHERE CTE.MaxAge < 60
),CTE2 AS(
SELECT *
FROM #Department
FULL JOIN CTE ON 1=1)
SELECT a.Name,a.DisplayName,STUFF(b.Col.query('Employee/text()').value('.','nvarchar(100)'),1,1,'')
FROM CTE2 a
CROSS APPLY(SELECT ',' + Name AS Employee FROM #Person WHERE a.Nr = DeparNr AND Age >= a.MinAge AND Age < a.MaxAge FOR XML PATH(''),TYPE) b(Col)
WHERE b.Col IS NOT NULL
Name DisplayName
-------------------------------------------------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------
人事部 18~20 张三
人事部 20~30 李四,钱七,孙八
销售部 20~30 王五
财务部 30~40 赵六
;WITH Staff
AS
(
SELECT 部门编号
,CASE WHEN 年龄 BETWEEN 18 AND 20 THEN '18~20'
WHEN 年龄 BETWEEN 21 AND 30 THEN '20~30'
ELSE 'other'
END AS 年龄段
,姓名
FROM 员工表
)
SELECT a.部门名称
,b.年龄段
,COUNT(*) AS 人数
,姓名=STUFF((SELECT ','+姓名 FROM Staff WHERE 部门编号=b.部门编号 AND 年龄段=b.年龄段 FOR XML PATH('') ),1,1,'')
FROM 部门表 AS a
INNER JOIN Staff AS b ON a.部门编号 = b.部门编号
GROUP BY a.部门名称
,b.年龄段;
WITH dp(deptno,deptname) AS (
SELECT '001',N'销售部' UNION
SELECT '002',N'财务部' UNION
SELECT '003',N'人事部'
),emp(deptno,empno,fullname,age)AS(
SELECT '001','9527',N'赵六',19 UNION
SELECT '001','9527',N'张三',20 UNION
SELECT '001','9528',N'李四',25 UNION
SELECT '002','9529',N'王二',30 UNION
SELECT '003','9530',N'孙五',41
),stat AS (
SELECT dp.deptno,dp.deptname,COUNT(0) AS empcount,CASE WHEN emp.age BETWEEN 18 AND 20 THEN '19~20' WHEN emp.age BETWEEN 21 AND 30 THEN '21~30' ELSE 'other' END AS agerang
FROM emp LEFT JOIN dp ON dp.deptno=emp.deptno
GROUP BY dp.deptno, dp.deptname,CASE WHEN emp.age BETWEEN 18 AND 20 THEN '19~20' WHEN emp.age BETWEEN 21 AND 30 THEN '21~30' ELSE 'other' END
)
SELECT stat.deptname,stat.agerang,empcount,STUFF(emps.s,1,1,'') AS emps FROM stat
OUTER APPLY (SELECT ','+emp.fullname FROM emp WHERE deptno=stat.deptno FOR XML PATH('') ) emps(s)
返回结果:
deptname agerang empcount emps
销售部 19~20 2 张三,赵六,李四
销售部 21~30 1 张三,赵六,李四
财务部 21~30 1 王二
人事部 other 1 孙五