Oracle PL/SQL •MySQL •SQL Server. Sql stuff описание

Оператор STUFF

Группировка данных массива в одну строку.

 

Для MS SQL Server 2005 и выше.

 

Например, есть запрос по сотрудникам компании и отделам (на примере БД AdventureWorks):

 

 

USE AdventureWorks

 

SELECT

 h.EmployeeID AS [Employee ID],  

 d.Name AS [Department Name]

 FROM HumanResources.EmployeeDepartmentHistory h

 INNER JOIN HumanResources.Department d ON d.DepartmentID = h.DepartmentID

 WHERE h.EmployeeID in (96, 140, 274)

 

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

 

Например, для сотрудника [Employee ID] 274 запрос возвратил бы строку: 'Marketing | Quality Assurance | Purchasing'.

 

Подобный запрос можно реализовать с помощью оператора STUFF:

 

--ШАБЛОН

--Крутим данные запроса: SELECT ID, [Name] FROM TableName

--[Name]        - что крутим

--ID                - по чему крутим

--' | '                - символ между значениями в скрученной строке

 

/*

select distinct t1.ID,

 STUFF((SELECT distinct ' | ' + t2.[Name]

        from (SELECT ID, [Name] FROM TableName

) t2

        where t1.ID = t2.ID

           FOR XML PATH(''), TYPE

           ).value('.', 'NVARCHAR(MAX)')

       ,1,0,'') data

from (SELECT ID, [Name] FROM TableName

) t1

*/

 

--ПРИМЕР (запрос по сотрудникам и отделам, в которых они работали; БД AdventureWorks MS SQL Server 2005)

--Крутим данные запроса:

--                                SELECT

--                                h.EmployeeID AS [Employee ID],  

--                                d.Name AS [Department Name]

--                                FROM HumanResources.EmployeeDepartmentHistory h

--                                INNER JOIN HumanResources.Department d ON d.DepartmentID = h.DepartmentID

--                                WHERE h.EmployeeID in (96, 140, 274)

 

--[Department Name] в строку по [Employee ID]

 

 

USE AdventureWorks

 

select distinct t1.[Employee ID],

STUFF((SELECT distinct ' | ' + t2.[Department Name]

        from (SELECT

                         h.EmployeeID AS [Employee ID],  

                         d.Name AS [Department Name]

                         FROM HumanResources.EmployeeDepartmentHistory h

                         INNER JOIN HumanResources.Department d ON d.DepartmentID = h.DepartmentID

                         WHERE h.EmployeeID in (96, 140, 274)

) t2

        where t1.[Employee ID] = t2.[Employee ID]

          FOR XML PATH(''), TYPE

          ).value('.', 'NVARCHAR(MAX)')

      ,1,0,'') data

from (SELECT

                         h.EmployeeID AS [Employee ID],  

                         d.Name AS [Department Name]

                         FROM HumanResources.EmployeeDepartmentHistory h

                         INNER JOIN HumanResources.Department d ON d.DepartmentID = h.DepartmentID

                         WHERE h.EmployeeID in (96, 140, 274)

) t1

 

 

При желании, полученное поле можно отформатировать (привести к тексту, например VARCHAR(7000)*) и отбросить из строки первый избыточный символ (разделитель элементов массива, в примере это первые 3 символа):

 

USE AdventureWorks

 

select [Employee ID], CAST(RIGHT(data, LEN(data) - 3) AS VARCHAR(7000)) AS Department from (

 

select distinct t1.[Employee ID],

STUFF((SELECT distinct ' | ' + t2.[Department Name]

        from (SELECT

                         h.EmployeeID AS [Employee ID],  

                         d.Name AS [Department Name]

                         FROM HumanResources.EmployeeDepartmentHistory h

                         INNER JOIN HumanResources.Department d ON d.DepartmentID = h.DepartmentID

                         WHERE h.EmployeeID in (96, 140, 274)

) t2

        where t1.[Employee ID] = t2.[Employee ID]

          FOR XML PATH(''), TYPE

          ).value('.', 'NVARCHAR(MAX)')

      ,1,0,'') data

from (SELECT

                         h.EmployeeID AS [Employee ID],  

                         d.Name AS [Department Name]

                         FROM HumanResources.EmployeeDepartmentHistory h

                         INNER JOIN HumanResources.Department d ON d.DepartmentID = h.DepartmentID

                         WHERE h.EmployeeID in (96, 140, 274)

) t1

 

 

) Employee_Dept

 

 

 

 

*

Результат STUFF-а может быть в формате BLOB, который не всегда совместим с другими аналитическими и информационными системами

 

pashelp.ru

SQL добавляет функцию STUFF в этот запрос MS SQL Server

Когда вы отправили запрос из другого вопроса ( последний SQL- запрос SQL SQL ), я буду использовать свой ответ, поскольку он чище, чем указанный выше запрос:

SELECT o.* , OrderID as LastOrderID FROM ( SELECT BuyerEMail , Name , COUNT(*) as TotalOrders FROM Orders WHERE Pay != 'PayPal' GROUP BY BuyerEmail, Name ) o CROSS APPLY ( SELECT TOP 1 OrderID, OrderDate FROM Orders s WHERE s.BuyerEmail = o.BuyerEmail ORDER BY OrderDate DESC ) ca

Вы отправили хороший пример, это не было полным. Вам понадобится следующий запрос xmlpath:

SELECT OrderID , Codes FROM tblLines r1 CROSS APPLY ( SELECT STUFF((SELECT ',' + CAST(Code AS NVARCHAR) FROM tblLines r2 WHERE r2.OrderID = r1.OrderID GROUP BY OrderID, Code ORDER BY Code FOR XML PATH (''), TYPE) .value('.', 'varchar(max)') , 1, 1, '')) OrderLines(Codes) GROUP BY OrderID, OrderList

Добавьте его в предыдущий оператор с простым соединением:

SELECT o.BuyerEMail ,o.Name ,o.TotalOrders , OrderID as LastOrderID , c.Codes FROM ( SELECT BuyerEMail , Name , COUNT(*) as TotalOrders FROM Orders WHERE Pay != 'PayPal' GROUP BY BuyerEmail, Name ) o CROSS APPLY ( SELECT TOP 1 OrderID, OrderDate FROM Orders s WHERE s.BuyerEmail = o.BuyerEmail ORDER BY OrderDate DESC ) ca INNER JOIN ( SELECT OrderID , Codes FROM tblLines r1 CROSS APPLY ( SELECT STUFF((SELECT ',' + CAST(Code AS NVARCHAR) FROM tblLines r2 WHERE r2.OrderID = r1.OrderID GROUP BY OrderID, Code ORDER BY Code FOR XML PATH (''), TYPE) .value('.', 'varchar(max)') , 1, 1, '')) OrderLines(Codes) GROUP BY OrderID, OrderList ) c ON ca.OrderID = c.OrderID SELECT TOP 1000 o.BuyerEMail, COUNT(*) HowMany, o.Name, l.Latest, STUFF((SELECT ', ' + li.Code FROM tblLines li WHERE li.OrderID = o2.OrderID FOR XML PATH ('')), 1, 1, '') AS [Codes]

FROM Orders o JOIN (SELECT BuyerEmail, MAX (OrderDate) Latest FROM Заказы GROUP BY BuyerEmail) l ON o.BuyerEmail = l.BuyerEmail JOIN Заказы o2 ON l.BuyerEmail = o2.BuyerEmail И l.OrderDate = o2.OrderDate WHERE Pay! = 'PayPal' GROUP BY o.BuyerEmail, o.Name, l.Latest ORDER BY

sqlserver.bilee.com

SQL Order By

ORDER BY - используется для сортировки результата.

ORDER BY - используется для сортировки результатов в указанной колонке.

ORDER BY - позволяет сортировать записи в возрастающем порядке по умолчанию.

Если вы хотите отсортировать записи в порядке убывания, то можно использовать ключевое слово DESC.

Сортировка SQL ORDER BY

Пример ORDER BY

Есть таблица "Persons":

P_IdLastNameFirstNameAddressCity

1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger
4	Nilsen	Tom	Vingvn 23	Stavanger

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

Для этого используем такой запрос:

Результат запроса:

P_IdLastNameFirstNameAddressCity

1	Hansen	Ola	Timoteivn 10	Sandnes
4	Nilsen	Tom	Vingvn 23	Stavanger
3	Pettersen	Kari	Storgt 20	Stavanger
2	Svendson	Tove	Borgvn 23	Sandnes

Пример ORDER BY DESC

Итак, мы хотим выбрать все записи из таблицы "Persons", однако, мы хотим, чтобы они были упорядоченны по фамилии и результат был выдан по убыванию.

Для этого используем такой запрос:

Результат запроса:

P_IdLastNameFirstNameAddressCity

2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger
4	Nilsen	Tom	Vingvn 23	Stavanger
1	Hansen	Ola	Timoteivn 10	Sandnes

dimonchik.com

ORDER BY оператор | Oracle PL/SQL •MySQL •SQL Server

Этот SQL руководство объясняет, как использовать SQL ORDER BY с синтаксисом и примерами.

Описание

SQL предложение ORDER BY используется для сортировки записей для набора результатов SELECT запроса.

Синтаксис

SELECT expressionsFROM tables[WHERE conditions]ORDER BY expression [ ASC | DESC ];

Параметры или аргументы

expressions — столбцы или расчеты, которые вы хотите получить.

tables – таблицы из которых вы хотите выгрузить данные. После оператора FROM должна быть указана хотя бы одна таблица.

WHERE conditions. Необязательный. Условия, которые должны быть выполнены для записей, которые будут выбраны.

ASC — необязательный. ASC сортирует набор результатов expressions в порядке возрастания. Этот параметр по умолчанию.

DESC — необязательный. DESC сортирует набор результатов expressions в порядке убывания.

Примечание

Если параметры ASC или DESC не указаны в операторе ORDER BY, то результаты expression будут отсортированы в порядке возрастания. Это эквивалентно ORDER BY expression ASC.

Пример сортировки без использования ASC / DESC

SELECT supplier_city FROM suppliers WHERE supplier_name = 'IBM' ORDER BY supplier_city;

SELECT supplier_city FROM suppliers WHERE supplier_name = 'IBM' ORDER BY supplier_city;

Этот SQL пример ORDER BY будет возвращать все записи отсортированные по полю supplier_city в порядке возрастания и будет эквивалентно следующему SQL запросу ORDER BY:

SELECT supplier_city FROM suppliers WHERE supplier_name = 'IBM' ORDER BY supplier_city ASC;

SELECT supplier_city FROM suppliers WHERE supplier_name = 'IBM' ORDER BY supplier_city ASC;

Большинство программистов при сортировке в порядке возрастания не указывают параметр ASC.

Пример сортировки в порядке убывания

При сортировке результирующего набора данных в порядке убывания, вы используете DESC в вашем ORDER BY следующим образом:

SELECT supplier_city FROM suppliers WHERE supplier_name = 'IBM' ORDER BY supplier_city DESC;

SELECT supplier_city FROM suppliers WHERE supplier_name = 'IBM' ORDER BY supplier_city DESC;

Этот SQL пример ORDER BY будет возвращать все записи отсортированные по полю supplier_city в порядке убывания.

Пример сортировки по относительной позиции

Вы также можете использовать SQL ORDER BY для сортировки по относительной позиции в наборе результатов, где первое поле в наборе результатов 1, следующее поле 2, и так далее.

SELECT supplier_city FROM suppliers WHERE supplier_name = 'IBM' ORDER BY 1 DESC;

SELECT supplier_city FROM suppliers WHERE supplier_name = 'IBM' ORDER BY 1 DESC;

Этот SQL ORDER BY будет возвращать все записи отсортированные по полю supplier_city в порядке убывания, так как поле supplier_city находится в положении # 1, а набор результатов и будет эквивалентен следующему SQL предложения ORDER BY:

SELECT supplier_city FROM suppliers WHERE supplier_name = 'IBM' ORDER BY supplier_city DESC;

SELECT supplier_city FROM suppliers WHERE supplier_name = 'IBM' ORDER BY supplier_city DESC;

Пример — использования ASC и DESC

При сортировке набора результатов с помощью SQL ORDER BY, вы можете использовать ASC и DESC в одном SQL запросе SELECT.

SELECT supplier_city, supplier_state FROM suppliers WHERE supplier_name = 'Intel' ORDER BY supplier_city DESC, supplier_state ASC;

SELECT supplier_city, supplier_state FROM suppliers WHERE supplier_name = 'Intel' ORDER BY supplier_city DESC, supplier_state ASC;

Этот SQL пример ORDER BY будет возвращать все отсортированные записи по полю supplier_city в порядке убывания, а по полю supplier_state в порядке возрастания.

oracleplsql.ru

---

• 
  Ultraiso установка windows 10 с флешки
• 
  Как ускорить вай фай на телефоне
• 
  Для чего нужен процессор в компьютере
• 
  Вы не можете встать в очередь потому что ваш статус заблокирован
• 
  Как узнать скорость сетевой карты
• 
  Командная строка set
• 
  Монитор почему
• 
  Визитка стильная шаблон
• 
  Что можно узнать по ip адресу компьютера
• 
  Комфортная скорость интернета
• 
  Контекстное меню где находится