пятница, 11 марта 2011 г.

Полезные SQL запросы

Приведу тут пару приемов, которые в свое время помогли мне решить некие локальные задачи. На авторство не претендую, но все же…
Итак, имеется таблица в БД (таблица 1).

Таблица 1 – Данные по расположению книг

Stand
Author
Стенд 1
Толстой
Стенд 1
Пушкин
Стенд 1
Чехов
Стенд 1
Гоголь
Стенд 2
Лондон
Стенд 2
Брэдбери
Стенд 2
Верн


А нужно получить данные как в таблице 2.

Таблица 2 – Требуемые данные

Stand
Authors
Стенд 1
Толстой Пушкин Чехов Гоголь
Стенд 2
Лондон Брэдбери Верн


Сделать это можно, используя средства извлечения XML-данных, а именно новый для SQL Server 2005 режим FOR XML PATH. Т.е. для каждой сущности Stand «склеиваются» сущности Author при помощи XML. Запрос приведен в листинге 1.

Листинг 1 – «Склеивание» при помощи FOR XML PATH
SELECT
     l.Stand,
      CAST(
      (
        SELECT l2.Author AS 'data()'
        FROM dbo.t_Library l2
        WHERE l2.Stand = l.Stand
        GROUP BY l2.Author
        FOR XML PATH('')
      ) AS varchar(128)) AS Authors
FROM dbo.t_Library l
GROUP BY
     l.Stand
ORDER BY l.Stand

Следующий пример. Допустим, у нас есть данные по возврату книг (таблица 3).

Таблица 3 – Возвраты книг

Author
Return_Date
Толстой
02.03.2011
Толстой
04.03.2011
Толстой
10.03.2011
Чехов
01.03.2011
Чехов
09.03.2011
Лондон
31.01.2011
Лондон
01.03.2011


Нужно получить выборку с книгами и их последними датами возврата таблица 4).

Таблица 4 – Последние возвраты книг

Author
Return_Date
Лондон
01.03.2011
Толстой
10.03.2011
Чехов
09.03.2011


Сделать это можно при помощи запроса из листинга 2.

Листинг 2 – Получение данных с последними обновлениями
SELECT TOP 1 WITH TIES
     b.Author, b.Return_Date
FROM t_Books b
ORDER BY ROW_NUMBER()
OVER (PARTITION BY b.Author ORDER BY b.Return_Date DESC) 

Комментариев нет: