时间戳

首页 > 解决方案 > SQL Server：将不同的结果连接到匹配表中的单个列中

sql - SQL Server：将不同的结果连接到匹配表中的单个列中

问题描述

我有 3 张桌子EMP, SCHOOL, JOB；结构和值是这样的：

CREATE TABLE SCHOOL
(
    SCHOOLID CHAR(2),
    STUDENTID  SMALLINT,
    GRADE      CHAR(4)    
);

CREATE TABLE JOB 
(
    JOBID SMALLINT UNIQUE NOT NULL,
    JOBNAME CHAR(15)
);

CREATE TABLE EMP
(
    EMPID SMALLINT, 
    JOBID SMALLINT, 
    SAL SMALLINT, 
    CITYID SMALLINT,
    YEAR SMALLINT,
    SCHOOLID CHAR(2),
    SEX  CHAR(1),
    EMPCAT CHAR(2)
);

INSERT INTO SCHOOL(SCHOOLID, STUDENTID, GRADE) 
VALUES ('S1', 10, 'PKG'),
       ('S1', 20, 'LKG'),
       ('S2', 10, 'PKG'),
       ('S2', 20, 'LKG'),
       ('S2', 30, '1ST'),
       ('S2', 30, '2ND');

INSERT INTO JOB(JOBID, JOBNAME) 
VALUES (1, 'PRINCIPAL'),
       (2, 'ASST PRINCIPAL'),
       (3, 'TEACHING'),
       (4, 'CLERICAL'),
       (7, 'HELPER');

INSERT INTO EMP (EMPID , JOBID, SAL, CITYID, YEAR, SCHOOLID, SEX, EMPCAT) 
VALUES (100, 1, 1000, 10, 2015, 'S1', 'M', 'A'),
       (200, 2, 2000, 10, 2015, 'S1', 'M', 'B'),
       (300, 1, 2500, 10, 2015, 'S1', 'F', 'A'),
       (400, 1, 1000, 10, 2015, 'S2', 'M', 'B'),
       (500, 1, 3000, 10, 2015, 'S2', 'F', 'A'),
       (600, 3, 1000, 10, 2015, 'S2', 'M', 'A'),
       (700, 3, 2000, 20, 2015, 'S2', 'F', 'A');

对于给定的输入 cityid ， YEAR （ Emp 表），获取所有不同的 SCHOOLID 并为每个 SCHOOLID 从 SCHOOL 表中获取不同的成绩（所有成绩需要连接并显示在单列中）；

对于与 jobid (JOB) 匹配的每个 jobid (EMP)，对于每个 jobname（来自 job table ），以水平方式获取 EMPCAT 'A'、'B' 的每个性别（男性和女性）的计数；和总数。

输出应该是这样的：

CITYID  SCHOOLID  GRADES              Jobname                  Male           FEMALE              TOTAL
                                                             A    B           A    B          
10      S1        PKG-LKG            PRINCIPAL               1    0           1    0                 2
10      S1        PKG-LKG            ASST PRINCIPAL          0    1           0    0                 1
10      S2        PKG-LKG-1ST        PRINCIPAL               0    1           1    0                 2
10      S2        PKG-LKG-1ST        TEACHING                1    0           0    0                 1
TOTAL                                                        1    2           2    0                 6

20      S2        PKG-LKG-1ST        TEACHING                1    0           0    0                 1     
TOTAL                                                        1    0           0    0                 1

如何以水平方式连接单列中的学校成绩和 EMPCAT 结果..？

标签： sqlsql-serverconcatenation

解决方案

一些表值函数可能会有所帮助，从连接成绩的函数开始

ALTER FUNCTION [dbo].[GetSchoolGrades] ()
RETURNS 
@school_grades table
(
    SCHOOLID  CHAR(2),
    GRADES    CHAR(24)    
)
AS
BEGIN
    with cte_grades (SCHOOLID, GRADES) as
    (
        select a1.SCHOOLID, 
               (
                select rtrim(x1.GRADE) + '-'
                from   SCHOOL x1
                where  x1.SCHOOLID = a1.SCHOOLID
                group  by x1.GRADE
                for    xml path ('')
            ) as GRADES
        from   SCHOOL a1
        group  by a1.SCHOOLID
    ) 
    insert into @school_grades(SCHOOLID, GRADES)
    select SCHOOLID, LEFT(GRADES, len(GRADES) -1) as GRADES
    from   cte_grades;
    RETURN 
END

现在是一个计数函数，基于参数

ALTER FUNCTION [dbo].[GetEmpCatCounts]
(
    @JOBID     SMALLINT,  
    @SCHOOLID  CHAR(2),
    @SEX       CHAR(1),
    @EMPCAT    CHAR(2)
)
RETURNS int
AS
BEGIN
    return (
       select count(*) from EMP m1 
       where  m1.JOBID    = @JOBID
       and    m1.SCHOOLID = @SCHOOLID
       and    m1.SEX      = @SEX
       and    m1.EMPCAT   = @EMPCAT
    )

END

像这样把它们绑在一起

select a1.CITYID,
       a1.SCHOOLID,
       g1.GRADES,
       j1.JOBNAME,
       (select dbo.GetEmpCatCounts(a1.JOBID, a1.SCHOOLID, 'M', 'A')) as EMPCNT_MALE_A,
       (select dbo.GetEmpCatCounts(a1.JOBID, a1.SCHOOLID, 'M', 'B')) as EMPCNT_MALE_B,
       (select dbo.GetEmpCatCounts(a1.JOBID, a1.SCHOOLID, 'F', 'A')) as EMPCNT_FEMALE_A,
       (select dbo.GetEmpCatCounts(a1.JOBID, a1.SCHOOLID, 'F', 'B')) as EMPCNT_FEMALE_B
from   EMP  a1
join   JOB  j1 
on     a1.JOBID = j1.JOBID
join   GetSchoolGrades() g1 
on     a1.SCHOOLID = g1.SCHOOLID
group  by a1.CITYID,
       a1.JOBID,
       a1.SCHOOLID,
       g1.GRADES,
       j1.JOBNAME
order  by a1.CITYID,
       a1.SCHOOLID,
       a1.JOBID,
       g1.GRADES

推荐阅读