VPF::слияние строк в sql-запросе - Форум программистов

swetik1981

Дата 16.9.2009, 22:55 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 117
Регистрация: 20.12.2007

Репутация: нет
Всего: нет

Имею базу данных, которая упрощённо выглядит так:

База данных - Предприятие:

Отдел             Сотрудник

1                 Степанов
1                 Фёдоров
1                 Иванов
2                 Пивоваров
3                 Сидоров
3                 Коновалов

Мне необходимо вывести на экран таблицу, состоящую из трёх столбцов с указанием номера отдела, количества работающих в нём сотрудников и списка сотрудников. Причём список всех сотрудников, работающих в отделе  должен быть представлен в одной ячейке данных

С количеством сотрудников всё понятно: "SELECT Отдел, COUNT(Отдел) FROM Предприятие GROUP BY Отдел"
Но как загнать в одну ячейку всех сотрудников, работающих в соответствующем разделе, что вывод данных был таков:

Отдел              Количество              Сотрудники

1                  3                        Степанов Фёдоров Иванов
2                  1                        Пивоваров
3                  2                        Сидоров Коновалов

Есть ли какая-то функция собирающая в одну строку все строки, представленные в соответствующем наборе записей?

Deniz

Дата 17.9.2009, 05:49 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1251
Регистрация: 16.10.2004
Где: Новый Уренгой

Репутация: 6
Всего: 44

СУБД?
Вроде стандартным методами не получится. (имеется ввиду 1 sql-запросом по стандартам SQL)
А вдруг в отделе будет работать >100 чел? Строчка получится очень длинной.
Может пересмотреть подход и сделать другую реализацию?

Это сообщение отредактировал(а) Deniz - 17.9.2009, 05:51

--------------------

"Для того чтобы сделать шаг вперед, достаточно пинка сзади" (с)

Akina

Дата 17.9.2009, 07:53 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20581
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 45
Всего: 454

В некоторых СУБД такая функция есть (скажем group_concat в MySQL), в других это надо организовывать пользовательской функцией.

--------------------

О(б)суждение моих действий - в соответствующей теме, пожалуйста. Или в РМ. И высшая инстанция - Администрация форума.

skyboy

Дата 17.9.2009, 08:59 (ссылка)

(нет голосов)

Загрузка ...

неОпытный

Профиль
Группа: Модератор
Сообщений: 9820
Регистрация: 18.5.2006
Где: Днепропетровск

Репутация: 15
Всего: 260

...а в некоторых сделать это средствами только SQL может вовсе оказаться невозможно...
в таком случае можно делать группировку на клиенте.

Zloxa

Дата 17.9.2009, 09:21 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 53
Всего: 161

Ну, коли уж тут почти все старожилы отметились и я свои пять копеек внесу.... smile

Вообще такая функция очень не лаконично вписывается в концепцию аггрегации SQL.
Все агрегатные функции ANSI SQL детерминированы - на одном и том же наборе данных дают одинаковый результат в не зависимости от порядка перебора набора данных при группировке. В случае же с аггрегирующей конкатенацией мы детерминированный результат получить можем лишь дополнительно указав критерии сортировки. А такая фича находится за пределами стандарта и вне концепции аггрегирования предусмотренной нынешним стандартом.

Другими словами аггрегирующая конкатенация - очень не кошерна. smile

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

Gluttton

Дата 17.9.2009, 09:56 (ссылка)

(нет голосов)

Загрузка ...

Начинающий

Профиль
Группа: Завсегдатай
Сообщений: 1170
Регистрация: 28.8.2008
Где: Феодосия

Репутация: 24
Всего: 54

И тут такой я на белом коне smile

...

Итак для таблицы:
user posted image

Вот такой вот запрос (Firebird 2.1):

Код


select sb.dep, sb.c, min(sb.employee)
from
(
    with recursive
        sub as
        (
            select department.dep, department.employee, 1 as c
            from department
            union all
            select sub.dep, sub.employee||' '||department.employee, sub.c+1
            from sub, department
                where sub.employee not like '%'||department.employee||'%'
                and sub.dep=department.dep
        )
    select distinct sub.dep, sub.c, sub.employee
    from sub
        where
        (
            sub.c=
            (
                select max(s.c)
                from sub as s
                    where sub.dep=s.dep
            )
        )
)   as sb
    group by sb.dep, sb.c

Вернет вот таки данные:
user posted image

Что происхоит в этом запросе...
Для краткости я приведу результат внутреннего подзапроса:

Код


    with recursive
        sub as
        (
            select department.dep, department.employee, 1 as c
            from department
            union all
            select sub.dep, sub.employee||' '||department.employee, sub.c+1
            from sub, department
                where sub.employee not like '%'||department.employee||'%'
                and sub.dep=department.dep
        )
    select distinct sub.dep, sub.c, sub.employee
    from sub

А всё остальное (т.е. внешний запрос) необходимо для того, что бы получить требуемые данные.

Кстати это мой дебют в рекурсивных запросах smile

...

--------------------

Слава Україні!

Gudinya

Дата 17.9.2009, 10:22 (ссылка)

(голосов:1)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 46
Регистрация: 2.2.2008

Репутация: 2
Всего: 8

Ну раз на коне тут уже влетали, въеду ка я на сером ослике... при том что 99% кода подсмотрел в Oracle Magazine

Код


CREATE OR REPLACE TYPE strsum_t AS OBJECT (
   ls_sum         VARCHAR2(32767), 
   MEMBER FUNCTION get_separator RETURN VARCHAR2, 
   STATIC FUNCTION ODCIAggregateInitialize (ctx IN OUT strsum_t) RETURN NUMBER, 
   MEMBER FUNCTION ODCIAggregateIterate (self IN OUT strsum_t, value IN VARCHAR2) RETURN NUMBER, 
   MEMBER FUNCTION ODCIAggregateMerge (self IN OUT strsum_t, ctx IN strsum_t) RETURN NUMBER, 
   MEMBER FUNCTION ODCIAggregateTerminate (self IN strsum_t, value OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER
 );
/

CREATE OR REPLACE TYPE BODY strsum_t
AS
  MEMBER FUNCTION get_separator RETURN VARCHAR2
  IS
    ls_separator       CONSTANT CHAR(1) := ' ';
   BEGIN
     RETURN ls_separator;
   END get_separator;

  STATIC FUNCTION ODCIAggregateInitialize (ctx IN OUT strsum_t) RETURN NUMBER
  IS
  BEGIN
    ctx := strsum_t(NULL);
    RETURN ODCIConst.Success;
  END ODCIAggregateInitialize;

  MEMBER FUNCTION ODCIAggregateIterate (self IN OUT strsum_t, value IN VARCHAR2) RETURN NUMBER
  IS
  BEGIN
    self.ls_sum := self.ls_sum || self.get_separator || value;
    RETURN ODCIConst.Success;
  END ODCIAggregateIterate;

  MEMBER FUNCTION ODCIAggregateMerge (self IN OUT strsum_t, ctx IN strsum_t) RETURN NUMBER
  IS
  BEGIN
    self.ls_sum := LTRIM(self.ls_sum, self.get_separator)|| self.get_separator|| LTRIM(ctx.ls_sum, self.get_separator);
    RETURN ODCIConst.Success;
  END ODCIAggregateMerge;

  MEMBER FUNCTION ODCIAggregateTerminate (self IN strsum_t, value OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER
  IS
  BEGIN
    value := LTRIM(self.ls_sum, self.get_separator);
    RETURN ODCIConst.Success;
  END ODCIAggregateTerminate;
END;
/

CREATE OR REPLACE FUNCTION strsum (as_str_i VARCHAR2) RETURN VARCHAR2 PARALLEL_ENABLE
  AGGREGATE USING
  strsum_t;
/

with c as (
  Select 
  '1' id,  'Степанов' sname from dual
  union all
  Select '1','Фёдоров' from dual 
  union all 
  Select '1','Иванов' from dual 
  union all
  Select '2','Пивоваров' from dual 
  union all
  Select '3','Сидоров' from dual   
  union all
  Select '3','Коновалов' from dual)
  Select 
    c.id,
    count(*), 
    STRSUM(sname)
  From c
Group by c.id;

Код


SQL> 
 
Type created
 
Type body created
 
Function created
 
ID   COUNT(*) STRSUM(SNAME)
-- ---------- --------------------------------------------------------------------------------
1           3 Степанов Фёдоров Иванов
2           1 Пивоваров
3           2 Сидоров Коновалов

Zloxa

Дата 17.9.2009, 10:31 (ссылка)

(голосов:1)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 53
Всего: 161

Цитата(Gluttton @ 17.9.2009, 09:56

)

это мой дебют

Поздравляю! smile

Чесгря, именно это слово, по тому же поводу, я и хотел написать даже до того, как вычитал засереное ;)

Цитата(Gluttton @ 17.9.2009, 09:56

)

Код


 where
        (
            sub.c=
            (
                select max(s.c)
                from sub as s
                    where sub.dep=s.dep
            )
        )

см бабушкин метод

Цитата(Gluttton @ 17.9.2009, 09:56

)

where sub.employee not like '%'||department.employee||'%'

не понравилось.. оверхедно както и недетрменировано
я бы предпочел sub.dept_id<department.dept_id sub.employe_id < department.employe_id (наверняка ведь оно нормализовано)
но у тебя тогда получается как бы distinct что тоже как бы находка.

Цитата(Gluttton @ 17.9.2009, 09:56

)

И тут такой я на белом коне smile ...

Цитата(Gudinya @ 17.9.2009, 10:22

)

я на сером ослике

ну коли пошла такая пьянка.... мой давний выезд
правда только для оракли :(

Это сообщение отредактировал(а) Zloxa - 17.9.2009, 10:50

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

Gluttton

Дата 17.9.2009, 11:45 (ссылка)

(нет голосов)

Загрузка ...

Начинающий

Профиль
Группа: Завсегдатай
Сообщений: 1170
Регистрация: 28.8.2008
Где: Феодосия

Репутация: 24
Всего: 54

Цитата(Zloxa @ 17.9.2009, 10:31

)

Поздравляю!

Спасибо!

Цитата(Zloxa @ 17.9.2009, 10:31

)

не понравилось.. оверхедно както и недетрменировано

Оверхедно - да, недетерминировано - не сказал бы. Запрос не будет работать (будет не коректно работать) на однофамильцах...
Идея в том, что бы тех, кого "уже сосчитали" не рассматривали повторно...
Про однофамильцев, думаю, что при такой структуре таблицы, коректно обработать однофамильцев из одного отдела будет крайне сложно (для этого прийдеться "играться" с подсчетом количества однофамильцев в отделе и потом их столько раз и повторять)... А вот если был бы ключик, тогда можно было бы устранить этот недостаток...

swetik1981, что с однофамильцами делать будем?

Цитата(Zloxa @ 17.9.2009, 10:31

)

см бабушкин метод

А вот за это спасибо!
[вот что получилось после всех внесенных изменений:

Код


with recursive
    sub as
    (
        select department.dep, department.employee, 1 as c
        from department
        union all
        select sub.dep, department.employee||' '||sub.employee, sub.c+1
        from sub, department
            where sub.employee<department.employee
            and sub.dep=department.dep
    )
select
    sub.dep,
    substring(max (cast(sub.c as char(3))||sub.employee) from 1 for 3) as c,
    substring(max (cast(sub.c as char(3))||sub.employee) from 4) as employee
from sub
    group by sub.dep

Gudinya, не разобрался я в приведенном запросе... Видать пора начинать изучать Oracle smile

...

--------------------

Слава Україні!

Zloxa

Дата 17.9.2009, 12:40 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 53
Всего: 161

Цитата(Gluttton @ 17.9.2009, 11:45

)

недетерминировано - не сказал бы

под недетерменированностью я подразумевал что порядок следования фамилий в результате будет зависеть от внешних факторов (порядок заполнения записями таблицы, план запроса....)... Т.е. в разные моменты времени (или разных средах), но на одинаковых данных запрос может возвращать разные результаты

Цитата(Gluttton @ 17.9.2009, 11:45

)

будет не коректно работать

Я эту "некорректность" наоборот воспринял как фишку.... smile

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

1 Пользователей читают эту тему (1 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| Составление SQL-запросов \| Следующая тема »