у меня есть таблица1
И стол2
Я использую этот код для просмотра/выбора уникальных результатов объединенных/объединенных столбцов, таких как concat(c,',',d)
, concat(b,',',d,',',g)
и т. д. из table1
, и он отлично работает:
SELECT DISTINCT(CONCAT(c,',',d))
FROM table1
Но вместо того, чтобы писать вручную, как concat(c,',',d)
, я хочу обратиться к table2.criteria
, чтобы получить ссылки на столбцы, которые будут объединены/объединены из table1
, чтобы я мог видеть все уникальные результаты по каждому объединенному критерию.
Пробовал это, но получил ошибку:
SELECT DISTINCT(SELECT criteria FROM table2)
FROM table1
ОШИБКА: более одной строки возвращено подзапросом, используемым в качестве выражения
Состояние SQL: 21000
Ожидаемый уникальный результат примерно такой;
| criteria | result |
| ------------ | ---------- |
| a,b | 15,2 |
| a,b | 10,2 |
| a,b | 20,2 |
| a,b | 12,2 |
| a,b | 18,2 |
| b,c,f,h | 2,2,2,2 |
| b,c,f,h | 2,2,0,2 |
| b,c,f,h | 2,2,0,0 |
| a,b,e,g,h | 20,2,0,0,2 |
| a,b,e,g,h | 12,2,0,0,0 |
| a,b,e,g,h | 15,2,0,0,0 |
| a,b,e,g,h | 10,2,0,1,2 |
| a,b,e,g,h | 18,2,0,1,2 |
| c,e | 2,0 |
SQL не позволяет параметризовать идентификаторы. Существуют различные способы обойти это ограничение.
Из вопроса неясно, но, согласно комментариям, вы хотите объединить данный шаблон для каждой строки в table1
.
Создайте вспомогательную функцию (один раз!), которая динамически объединяет и выполняет операторы. Основы:
CREATE OR REPLACE FUNCTION f_concat_cols(_cols text)
RETURNS TABLE (result text)
LANGUAGE plpgsql AS
$func$
BEGIN
RETURN QUERY EXECUTE format(
$q$SELECT concat_ws(',', %s) FROM table1 ORDER BY line$q$, _cols);
END
$func$;
Это функция, возвращающая множество (также известная как «табличная функция»), которая возвращает одну строку результата для каждой строки в table1
для каждого заданного шаблона.
Предупреждение. Преобразование пользовательского ввода в такой код — отличная возможность для SQL-инъекций. Вы должны убедиться, что table1.criteria
может содержать только допустимые строки!
Чтобы получить полную матрицу результатов (с различными результатами для каждой строки в таблице 2), запрос теперь прост:
SELECT DISTINCT line AS t2_line, criteria, t1.*
FROM table2, f_concat_cols(criteria) t1
ORDER BY t2_line;
SELECT DISTINCT t2.line AS t2_line, t2.criteria, c.*
FROM table2 t2
CROSS JOIN (SELECT line, to_json(t) AS js FROM table1 t) t1
CROSS JOIN LATERAL (
SELECT string_agg(t1.js->>sub, ',') AS result
FROM unnest(string_to_array(t2.criteria, ',')) sub
) c
ORDER BY t2_line;
После преобразования строк из t1 в запись JSON мы можем напрямую обращаться к ключам (преобразованным из имен столбцов). Я раскрываю шаблон, получаю доступ к каждому отдельному ключу и агрегирую результат в подзапросе LATERAL. Видеть:
Вы можете инкапсулировать логику в функцию, как в 1., но в данном случае это необязательно.
SELECT DISTINCT t2.line AS t2_line, t2.criteria, c.*
FROM table2 t2
CROSS JOIN (SELECT line, ARRAY [a,b,c,d,e,f,g,h] AS arr FROM table1 t) t1
CROSS JOIN LATERAL (
SELECT string_agg(t1.arr[idx]::text, ',') AS result
FROM unnest(string_to_array(translate(t2.criteria, 'abcdefgh', '12345678'), ',')::int[]) idx
) c
ORDER BY t2_line;
Подобно «трюку» с JSON, мы можем избежать динамического SQL, преобразовав столбцы в простой массив Postgres. Затем проецируйте имена столбцов на индексы целочисленного массива. Я использую translate()
для простого случая, но это работает только для отдельных букв! Используйте replace()
или regexp_replace()
или какой-либо другой метод для более длинных имен.
Остальное как выше.
рабочий пример - показываю все.
@Gulya: я тоже учел номер строки.
@ Erwin Brandstetter по-прежнему общий вывод составляет всего 4 строки, результат самого «a, b» должен быть 5 строк, например («18,2», «20,2», «10,2», «12,2» , и "15,2")
@Gulya Можете ли вы включить это требование в вопрос? И объясните, почему вы добавили DISTINCT
. Я добавил еще несколько решений выше.
@ Эрвин, я изменил вопрос, пожалуйста, проверьте его. Спасибо
Интегрированный РАЗЛИЧНЫЙ.
Не могли бы вы проверить следующую ссылку [dbfiddle.uk/huOOMD8G], вывод основан только на 1-й строке, но мне нужна сводка всей таблицы. Спасибо