#sql #sql-server #concatenation
#sql #sql-сервер #объединение
Вопрос:
У меня есть 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');
Для заданного входного идентификатора города, года (таблица Emp) возьмите все разные идентификаторы школ и для каждого идентификатора ШКОЛЫ возьмите отдельную оценку из таблицы SCHOOL (все оценки должны быть объединены и отображены в одном столбце) ;
И для каждого jobid (EMP), который совпадает с jobid (JOB), для каждого jobname (из таблицы job) получите значения каждого пола (мужского и женского) для 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 по горизонтали ..?
Комментарии:
1. Использование SQL SERVER
2. Не лучше ли использовать для этого какой-нибудь инструмент отчетности или обработать его в клиентском коде?
3. может быть лучше, но требуется запрос
4. Какую версию SQL Server вы используете? Что вы уже пробовали?
Ответ №1:
Я сталкивался с подобной ситуацией в прошлом. Мне пришлось перебирать записи с помощью WHILE и объединять значения в поле. В вашем случае вы бы сравнили запись с предыдущей, и если они из одного города и школы, вы бы выполнили объединение в поле. Конечно, это зависит от того, сколько записей у вас в таблицах, потому что обработка таким способом выполняется не очень быстро.
Комментарии:
1. Это больше подходит в качестве комментария, чем ответа
2. Вы из полиции?
3. Это не дискуссионный форум, и я считаю, что ответы должны быть немного более точными относительно того, как решается проблема.
Ответ №2:
Могут помочь некоторые функции с табличным значением, начните с функции для объединения оценок
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
Теперь функция для подсчета, основанная на parms
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
Ответ №3:
Хорошо, это некрасиво, но это работает и делает то, что вы хотите. Подведение итогов было бы лучше выполнить на уровне представления, но я поместил первичные результаты во временную таблицу, чтобы мы могли затем выбирать из нее и объединять ее с итогами, затем упорядочивать с помощью CITYID и OrderKey, чтобы получить желаемую презентацию.
Declare @Results as table
(orderkey int, cityid int, schoolid varchar(5), Grades varchar(100), Jobname varchar(100), Male_A int, Male_B int, Female_A int, Female_B int)
INSERT INTO @results
Select DISTINCT 1 as orderkey, CITYID, e.schoolid , g.grades, j.JOBNAME,
empcount.scount as Male_A, empcountB.scountB as Male_B, empcountFA.scountFA as Female_A, empcountFB.scountFB as Female_B
from emp e
left join job j on e.JOBID=j.JOBID
outer apply (select STUFF((Select ',' GRADE from school s where s.SCHOOLID=e.SCHOOLID FOR XML PATH('')),1,1,'' ) as grades) g
outer apply (select count(sex) as scount from emp ee where ee.jobid=e.jobid and sex='M' and ee.empcat='A'
and (select STUFF((Select ',' GRADE from school s where s.SCHOOLID=ee.SCHOOLID FOR XML PATH('')),1,1,'' ))=g.grades
) empcount
outer apply (select count(sex) as scountB from emp ee where ee.jobid=e.jobid and sex='M' and ee.empcat='B'
and (select STUFF((Select ',' GRADE from school s where s.SCHOOLID=ee.SCHOOLID FOR XML PATH('')),1,1,'' ))=g.grades
) empcountB
outer apply (select count(sex) as scountFA from emp ee where ee.jobid=e.jobid and sex='F' and ee.empcat='A'
and (select STUFF((Select ',' GRADE from school s where s.SCHOOLID=ee.SCHOOLID FOR XML PATH('')),1,1,'' ))=g.grades
) empcountFA
outer apply (select count(sex) as scountFB from emp ee where ee.jobid=e.jobid and sex='F' and ee.empcat='B'
and (select STUFF((Select ',' GRADE from school s where s.SCHOOLID=ee.SCHOOLID FOR XML PATH('')),1,1,'' ))=g.grades
) empcountFB
Select * from @results
UNION ALL
Select DISTINCT 99,CITYID, '' ,'TOTAL', '',
sum(Male_A) as Male_A, sum(Male_B) as Male_B, sum(Female_A) as Female_A, sum(Female_B) as Female_B
from @results
group by CITYID
ORDER BY CITYID, orderkey