Как я могу “объединиться”, “сгладиться”, или “центр” следует из запроса, который возвращает несколько строк в единственный результат?

Question

Как я могу “объединиться”, “сгладиться”, или “центр” следует из запроса, который возвращает несколько строк в единственный результат?

У меня есть простой запрос за столом, который возвращает результаты как следующее:

id    id_type  id_ref
2702  5        31
2702  16       14
2702  17       3
2702  40       1
2703  23       4
2703  23       5
2703  34       6
2704  1        14

И я хотел бы объединить результаты в одну строку, например:

id    concatenation 
2702  5,16,17,40:31,14,3,1
2703  23,23,34:4,5,6
2704  1:14

Там какой-либо путь состоит в том, чтобы сделать это в триггере?

NB: я знаю, что могу использовать курсор, но я действительно предпочел бы не тому, если нет никакого лучшего пути.

База данных является версией 12.5.4 Sybase.

8

sql sybase pivot flatten

задан George Dontas 3 June 2010 в 12:47

6 ответов

У меня нет сервера sybase для тестирования, но, читая документацию в Интернете, кажется, что поддерживаются общие табличные выражения. Я не был уверен в том, что ROW_NUMBER используется в других решениях, поэтому вот решение, которое его не использует.

Я считаю, что Sybase использует || для конкатенации строк, хотя в документах, которые я читал, упоминается, что также можно использовать '+', поэтому я использовал это. Пожалуйста, измените при необходимости.

Я прокомментировал запрос, чтобы попытаться объяснить, что происходит.

Запрос объединяет все значения id_type и id_ref с одним и тем же идентификатором в порядке возрастания id_type.

/* a common table expression is used to concatenate the values, one by one */
WITH ConcatYourTable([id],  /* the id of rows being concatenated */
      concat_id_type,       /* concatenated id_type so far */
      concat_id_ref,        /* concatenated id_ref so far */
      last_id_type,         /* the last id_type added */
      remain)               /* how many more values are there to concatenate? */
AS 
(
  /* start with the lowest id_type value for some id */
  SELECT id, id_type, id_ref, 
     id_type, /* id_type was concatentated (it's presently the only value) */
     (SELECT COUNT(*) FROM YourTable f2 WHERE f2.id=f.id)-1
     /* how many more values to concatenate -1 because we've added one already */
  FROM YourTable f 
  WHERE NOT EXISTS
  /* start with the lowest value - ensure there are no other values lower. */
     (SELECT 1 FROM YourTable f2 WHERE f2.id=f.id AND f2.id_type<f.id_type)
  UNION ALL
  /* concatenate higher values of id_type for the same id */
  SELECT f.id, 
    c.id_type + ',' + f.id_type,   /* add the new id_type value to the current list */
    c.id_ref + ',' + f.id_ref,     /* add the new id_ref value to the current list */
    f.id_type,  /* the last value added - ensured subsequent added values are greater */
    c.remain-1  /* one less value to add */
  FROM ConcatYourTable c           /* take what we have concatenated so far */    
   INNER JOIN YourTable f  /* add another row with the same id, and > id_type */
     ON f.id = c.id AND f.id_type > c.last_id_type
     /* we really want the next highest id_type, not just one that is greater */
   WHERE NOT EXISTS (SELECT 1 FROM YourTable f2
     WHERE f2.id=f.id AND f2.id_type<f.id_type AND
     f2.id_type>c.last_id_type)
)
/* Select the rows where all values for and id were concatenated (remain=0) */
/* Concatenate the cumulated id_type and id_ref fields to format id_type values:id_ref values*/
SELECT id, id_type+':'+id_ref FROM ConcatYourTable 
WHERE remain=0

Запрос довольно «грубый» в том смысле, что в нем не используются более сложные функции, которые могли бы улучшить читаемость или, возможно, производительность. Я сделал это, так как плохо знаю sybase, и использовал те функции, которые, как я уверен, поддерживаются. Для лучшей производительности убедитесь, что id и (id, id_type) проиндексированы.

Чтобы использовать это в триггере, таком как триггер INSERT или UPDATE для поддержки таблицы на основе этого объединенного запроса, расширьте предложение WHERE базового случая (перед UNION ALL), чтобы включить id = @ changed_id. Это обеспечит вычисление только объединенной строки для измененного идентификатора. Затем вы можете делать все, что хотите, с вычисленной объединенной строкой. Если вы материализуете объединенный запрос в таблицу, тогда УДАЛИТЬ текущую строку объединения для @changed_id в таблице и ВСТАВИТЬ новую строку из результата указанного выше запроса объединения. Вы также можете проверить, содержит ли ваша таблица конкатенации уже значение с changed_id, и вместо этого использовать оператор UPDATE.

0