SQL объединяет несколько значений из столбца в одну ячейку
Уже есть тонна sql join q, но я не видел своего ответа, поэтому здесь., Я работаю с WPDB (база данных WordPress)/EZSql/MySQL 5.0. Попытка достичь «простого» желаемого результата ниже оказалась нелегкой.
MemberID поступает из таблицы a, MemberName поступает из таблицы a и таблицы b, а FruitName — из таблицы b. Поскольку я выводил много других столбцов из таблицы a, я «левой» соединил две таблицы через этот запрос:
Позже я буду печатать столбцы с помощью echo:
Я предполагаю, что я должен попытаться выполнить запрос/присоединиться к двум таблицам другим способом, хотя может быть возможно получить объявление при печати столбцов. Я нашел здесь обсуждение здесь и смоделировал мой вопрос после него, но я не понимаю их решений и надеюсь на что-то более простое.
LISTAGG ФУНКЦИЯ
Oracle/PLSQL функция LISTAGG объединяет значения measure_column для каждой группы на основе order_by_clause .
Синтаксис
Синтаксис Oracle/PLSQL функции LISTAGG:
Параметры или аргументы
measure_column столбец, значения которого вы хотите объединить вместе в наборе результатов. Нулевые значения в measure_column игнорируются.
delimiter не является обязательным. Это разделитель используется при разделении значений measure_column при выводе результатов.
order_by_clause определяет порядок связанных значении (т.е.: measure_column ) которые возвращаются.
Функция LISTAGG возвращает string значение.
Применение
Функцию LISTAGG можно использовать в следующих версиях Oracle/PLSQL:
- Oracle 12c, Oracle 11g Release 2
Пример
Функция LISTAGG может быть использована в Oracle/PLSQL.
Так как это более сложная функция, то чтобы ее понять, давайте посмотрим на примере, который включает данные, которые функция демонстрирует на выходе.
как в t-sql объединить ячейки?
Вот несколько вараинтов накидал вам. Выполните скрипт на тестовом сервере и выбирайте что нужно вам, будут вопросы, спрашивайте.
— создадим примеры для демо
use tempdb;
go
— допустим есть таблица товаров
create table dbo. MyTable ( Name nvarchar ( 100 ) , Color nvarchar ( 100 ) ) ;
insert dbo. MyTable values ( ‘стол’ , ‘черний’ ) ;
insert dbo. MyTable values ( ‘стул’ , ‘синий’ ) ;
insert dbo. MyTable values ( ‘кровать’ , ‘бэлый-дэцкий’ ) ;
select * from dbo. MyTable ;
go
————————————————————————
— вариант 1) насчитываем это прямо в запросе
select
Name,
Color,
Art = (
case
when Name = ‘стол’ then ‘1’
when Name = ‘стул’ then ‘2’
when Name = ‘кровать’ then ‘3’
else ‘0’
end +
case
when Color = ‘черний’ then ‘3’
when Color = ‘синий’ then ‘2’
when Color = ‘бэлый-дэцкий’ then ‘1’
else ‘0’
end
)
from
dbo. MyTable
;
go
————————————————————————
— вариант 2) создаем вычисляемый столбец
alter table dbo. MyTable add Art as (
case
when Name = ‘стол’ then ‘1’
when Name = ‘стул’ then ‘2’
when Name = ‘кровать’ then ‘3’
else ‘0’
end +
case
when Color = ‘черний’ then ‘3’
when Color = ‘синий’ then ‘2’
when Color = ‘бэлый-дэцкий’ then ‘1’
else ‘0’
end
)
;
go
— тогда запрос выглядит просто как
select
Name,
Color,
Art
from
dbo. MyTable
;
go
————————————————————————
— вариант 3) реалистичный. Допустим, товаров много и привязку кода для артикула мы держим в отдельныйх таблицах
create table dbo. MyTableNameArt ( Name nvarchar ( 100 ) primary key , Art varchar ( 10 ) ) ;
create table dbo. MyTableColorArt ( Color nvarchar ( 100 ) primary key , Art varchar ( 10 ) ) ;
insert dbo. MyTableNameArt select ‘стол’ , ‘1’ union all select ‘стул’ , ‘2’ union all select ‘кровать’ , ‘3’ ;
insert dbo. MyTableColorArt select ‘черний’ , ‘3’ union all select ‘синий’ , ‘2’ union all select ‘бэлый-дэцкий’ , ‘1’ ;
—тогда, получить артикул можно запросом:
select
t. Name ,
t. Color ,
Art = isnull ( n. Art , ‘0’ ) + isnull ( c. Art , ‘0’ )
from
dbo. MyTable t
left join dbo. MyTableNameArt n on t. Name = n. Name
left join dbo. MyTableColorArt c on t. Color = c. Color
;
go
————————————————————————
— вариант 3.1) inline-табличная функция. То же самое, только можно убрать это в табличную функцию
— требуется сервер выше 2000 и работать будет медленнее, чем предыдущий вариант
create function dbo. utf_GetArt ( @Name nvarchar ( 100 ) , @Color nvarchar ( 100 ) )
returns table as
return (
with n as (
select Art from dbo. MyTableNameArt n where @Name = n. Name
) ,
c as (
select Art from dbo. MyTableColorArt c where @Color = c. Color
)
select Art = isnull ( n. Art , ‘0’ ) + isnull ( c. Art , ‘0’ ) from n cross join c
)
go
— тогда сам запрос выглядит
select
t. Name ,
t. Color ,
a. Art
from
dbo. MyTable t
outer apply dbo. utf_GetArt ( t. Name ,t. Color ) a
;
go
————————————————————————
— вариант 3.2) скалярная функция. Можно сделать скалярную функцию и убрать ее либо в вчисляемую колонку,
— либо заюзать в запросе. Не рекомендую этот вариант, т.к. он хуже по скорости чем все предыдущие,
— кроме того, если для одного товара возможно несколько артикулов, то эта функция отработает иначе чем предыдущие варианты,
— из выгоды — можно убрать в вычисляемую колоку и сделать красивый компактный код
create function dbo. usf_GetArt ( @Name nvarchar ( 100 ) , @Color nvarchar ( 100 ) )
returns nvarchar ( 20 ) begin
declare @Art nvarchar ( 20 ) ;
set @Art = » ;
select top ( 1 ) @Art = @Art + isnull ( Art, ‘0’ ) from dbo. MyTableNameArt n where @Name = n. Name
select top ( 1 ) @Art = @Art + isnull ( Art, ‘0’ ) from dbo. MyTableColorArt c where @Color = c. Color
end ;
go
— тогда запрос будет такой
select
t. Name ,
t. Color ,
Art = dbo. usf_GetArt ( t. Name , t. Color )
from
dbo. MyTable t
go
— либо можно убрать в вычислямую колонку
alter table dbo. MyTable drop column Art; —удалим предыдущую колонку из примера 2)
alter table dbo. MyTable add Art as dbo. usf_GetArt ( Name,Color ) ;
go
—тогда запрос
select
t. Name ,
t. Color ,
t. Art
from
dbo. MyTable t
go
————————————————————————
— удалим функции и таблицы
drop table dbo. MyTableNameArt , dbo. MyTableColorArt , dbo. MyTable ;
drop function dbo. utf_GetArt ;
drop function dbo. usf_GetArt ;
Как объединить ячейки в sql
«Analytic Functions» for information on syntax, semantics, and restrictions of the ORDER BY clause and OVER clause
For a specified measure, LISTAGG orders data within each group specified in the ORDER BY clause and then concatenates the values of the measure column.
As a single-set aggregate function, LISTAGG operates on all rows and returns a single output row.
As a group-set aggregate, the function operates on and returns an output row for each group defined by the GROUP BY clause.
As an analytic function, LISTAGG partitions the query result set into groups based on one or more expression in the query_partition_clause .
The arguments to the function are subject to the following rules:
The ALL keyword is optional and is provided for semantic clarity.
The measure_expr is the measure column and can be any expression. Null values in the measure column are ignored.
The delimiter designates the string that is to separate the measure column values. This clause is optional and defaults to NULL .
If measure_expr is of type RAW , then the delimiter must be of type RAW . You can achieve this by specifying the delimiter as a character string that can be implicitly converted to RAW , or by explicitly converting the delimiter to RAW , for example, using the UTL_RAW.CAST_TO_RAW function.
The order_by_clause determines the order in which the concatenated values are returned. The function is deterministic only if the ORDER BY column list achieved unique ordering.
If you specify order_by_clause , you must also specify WITHIN GROUP and vice versa. These two clauses must be specified together or not at all.
The DISTINCT keyword removes duplicate values from the list.
If the measure column is of type RAW , then the return data type is RAW . Otherwise, the return data type is VARCHAR2 .
The maximum length of the return data type depends on the value of the MAX_STRING_SIZE initialization parameter. If MAX_STRING_SIZE = EXTENDED , then the maximum length is 32767 bytes for the VARCHAR2 and RAW data types. If MAX_STRING_SIZE = STANDARD , then the maximum length is 4000 bytes for the VARCHAR2 data type and 2000 bytes for the RAW data type. A final delimiter is not included when determining if the return value fits in the return data type.
Extended Data Types for more information on the MAX_STRING_SIZE initialization parameter
Appendix C in Oracle Database Globalization Support Guide for the collation derivation rules, which define the collation assigned to the character return value of LISTAGG
This clause controls how the function behaves when the return value exceeds the maximum length of the return data type.
ON OVERFLOW ERROR If you specify this clause, then the function returns an ORA-01489 error. This is the default.
ON OVERFLOW TRUNCATE If you specify this clause, then the function returns a truncated list of measure values.
The truncation_indicator designates the string that is to be appended to the truncated list of measure values. If you omit this clause, then the truncation indicator is an ellipsis ( . ).
If measure_expr is of type RAW , then the truncation indicator must be of type RAW . You can achieve this by specifying the truncation indicator as a character string that can be implicitly converted to RAW , or by explicitly converting the truncation indicator to RAW , for example, using the UTL_RAW.CAST_TO_RAW function.
If you specify WITH COUNT , then after the truncation indicator, the database appends the number of truncated values, enclosed in parentheses. In this case, the database truncates enough measure values to allow space in the return value for a final delimiter, the truncation indicator, and 24 characters for the number value enclosed in parentheses.
If you specify WITHOUT COUNT , then the database omits the number of truncated values from the return value. In this case, the database truncates enough measure values to allow space in the return value for a final delimiter and the truncation indicator.
If you do not specify WITH COUNT or WITHOUT COUNT , then the default is WITH COUNT .
The following single-set aggregate example lists all of the employees in Department 30 in the hr.employees table, ordered by hire date and last name:
The following group-set aggregate example lists, for each department ID in the hr.employees table, the employees in that department in order of their hire date:
The following example is identical to the previous example, except it contains the ON OVERFLOW TRUNCATE clause. For the purpose of this example, assume that the maximum length of the return value is an artificially small number of 200 bytes. Because the list of employees for department 50 exceeds 200 bytes, the list is truncated and appended with a final delimiter ‘ ; ‘, the specified truncation indicator ‘ . ‘, and the number of truncated values ‘ (23) ‘.
The following analytic example shows, for each employee hired earlier than September 1, 2003, the employee’s department, hire date, and all other employees in that department also hired before September 1, 2003: