INSERT INTO dbo. Orders (orderid, orderdate, empid, custid) SELECT 10001, '20090212', 3, 'A' UNION ALL SELECT 10002, '20090212', 5, 'B' UNION ALL SELECT 10003, '20090213', 4, 'B' UNION ALL SELECT 10004, '20090214', 1, 'A' UNION ALL SELECT 10005, '20090213', 1, 'C' UNION ALL SELECT 10006, '20090215', 3, 'C'; INSERT INTO dbo. Orders (orderid, orderdate, empid, custid) VALUES (10001, '20090212', 3, 'A'), (10002, '20090212', 5, 'B'), (10003, '20090213', 4, 'B'), (10004, '20090214', 1, 'A'), (10005, '20090213', 1, 'C'), (10006, "20090215", 3, "C");
Функции ранжирования Используя функцию ROW_NUMBER можно: Øзадать нумерацию, которая будет отличаться от порядка сортировки строк результирующего набора; Øсоздать "несквозную" нумерацию, т. е. выделить группы из общего множества строк и пронумеровать их отдельно для каждой группы; Øиспользовать одновременно несколько способов нумерации, поскольку, фактически, нумерация не зависит от сортировки строк запроса.
Пример 1. Пронумеровать все рейсы из таблицы Trip в порядке возрастания их номеров. Выполнить сортировку по {id_comp, trip_no}. SELECT row_number() over(ORDER BY trip_no) num, trip_no, id_comp FROM trip WHERE ID_comp < 3 ORDER BY id_comp, trip_no Num trip_no 3 1181 4 1182 5 1187 6 1188 7 1195 8 1196 1 1145 2 1146 id_comp 1 1 1 2 2
SELECT row_number() over(ORDER BY id_comp, trip_no) num, trip_no, id_comp FROM trip WHERE ID_comp<3 ORDER BY id_comp, trip_no Num 1 2 3 4 5 6 7 8 trip_no 1181 1182 1187 1188 1195 1196 1145 1146 id_comp 1 1 1 2 2
Пример 2. Пронумеровать рейсы каждой компании отдельно в порядке возрастания номеров рейсов. SELECT row_number() over(partition BY id_comp ORDER BY id_comp, trip_no) num, trip_no, id_comp FROM trip WHERE ID_comp < 3 ORDER BY id_comp, trip_no Num trip_noid _comp 1 1181 1 2 1182 1 3 1187 1 4 1188 1 5 1195 1 6 1196 1 1 1145 2 2 1146 2
Функции RANK() и DENSE_RANK() Cтроки, которые имеют одинаковые значения в столбцах, по которым выполняется упорядочивание, получают одинаковые номера (ранги). 1 5 6 6 6 1 1 5 2 6 3 6 3 Вопрос: с какого номера продолжится нумерация, если, скажем, в последовательности чисел появится 7 и т. д. ? Здесь есть два варианта: 1) с номера 4, т. к. это следующий номер по порядку; (DENSE_RANK) 2) с номера 6, т. к. следующая строка будет шестая по счету. (RANK)
SELECT *, ROW_NUMBER() OVER(ORDER BY type) num, RANK() OVER(ORDER BY type) rnk FROM Printer Code 2 3 1 6 4 5 model 1433 1434 1276 1288 1401 1408 color y y n n type Jet Laser Matrix price 270, 00 290, 00 400, 00 150, 00 270, 00 num 1 2 3 4 5 6 rnk 1 1 3 3 5 5
SELECT *, RANK() OVER(ORDER BY type) rnk, DENSE_RANK() OVER(ORDER BY type) rnk_dense FROM Printer Code 2 3 1 6 4 5 model 1433 1434 1276 1288 1401 1408 color y y n n type Jet Laser Matrix price 270, 00 290, 00 400, 00 150, 00 270, 00 rnk 1 1 3 3 5 5 rnk_dense 1 1 2 2 3 3
SELECT *, RANK() OVER(PARTITION BY type ORDER BY price) rnk FROM Printer Code 2 3 1 6 4 5 model 1433 1434 1276 1288 1401 1408 color y y n n type Jet Laser Matrix price 270, 00 290, 00 400, 00 150, 00 270, 00 rnk 1 2 1 2
А вот как можно выбрать самые дешевые модели в каждой категории: SELECT model, color, type, price FROM (SELECT *, RANK() OVER(PARTITION BY type ORDER BY price) rnk FROM Printer) Ranked_models WHERE rnk = 1 Model color type price 1433 y Jet 270, 00 1276 N Laser 400, 00 1288 N Laser 400, 00 1401 n Matrix 150, 00
Пример. Найти производителей, которые производят более 2 -х моделей PC. Эта задача имеет традиционное решение через агрегатные функции: SELECT maker FROM Product WHERE type = 'PC‘ GROUP BY maker HAVING COUNT(*) > 2 SELECT maker FROM (SELECT maker, RANK() OVER(PARTITION BY maker ORDER BY model) rnk FROM Product WHERE type = 'PC') Ranked_makers WHERE rnk > 2
PIVOT и UNPIVOT SELECT maker, type FROM product; П р о A и з B в о C д и D т е E л и Типы продукции Laptop PC Printer 2 3 1 0 0 2 0 3 1 Maker B A A E A D A C A A D E B A E E type PC PC Printer Laptop Printer Laptop PC PC
SELECT maker, SUM(CASE type WHEN 'pc' THEN 1 ELSE 0 END) PC, SUM(CASE type WHEN 'laptop' THEN 1 ELSE 0 END) Laptop, SUM(CASE type WHEN 'printer' THEN 1 ELSE 0 END) Printer FROM Product GROUP BY maker SELECT maker, [pc], [laptop], [printer] FROM Product PIVOT (COUNT(model) FOR type IN ([pc], [laptop], [printer]) ) pvt
![SELECT <non-pivoted column>, [first pivoted column] AS <column name>, [second pivoted column] AS <column](https://present5.com/presentation/53433469_222765947/image-14.jpg "SELECT <non-pivoted column>, [first pivoted column] AS <column name>, [second pivoted column] AS <column")
SELECT <non-pivoted column>, [first pivoted column] AS <column name>, [second pivoted column] AS <column name>, . . . [last pivoted column] AS <column name> FROM (<SELECT query that produces the data>) AS <alias for the source query> PIVOT ( <aggregation function>(<column being aggregated>) FOR [<column that contains the values that will become column headers>] IN ( [first pivoted column], [second pivoted column], . . . [last pivoted column]) ) AS <alias for the pivot table> <optional ORDER BY clause>;
SELECT screen, AVG(price) avg_ FROM Laptop GROUP BY screen avg_ 11 700. 00 12 960. 00 14 1175. 00 15 1050. 00 А вот как можно повернуть эту таблицу с помощью PIVOT: SELECT [avg_], [11], [12], [14], [15] FROM (SELECT 'average price' AS 'avg_', screen, price FROM Laptop) PIVOT (AVG(price) FOR screen IN([11], [12], [14], [15]) ) pvt avg_ average price 11 12 14 15 700. 00 960. 00 1175. 00 1050. 00
trip_no id_comp plane town_from town_to time_out 1100 4 Boeing Rostov Paris 14: 30: 00 trip_no 1100 1100 spec id_comp Plane town_from town_to time_out time_in 17: 50: 00 info 4 Boeing Rostov Paris 14: 30: 00 17: 50: 00 SELECT trip_no, spec, info FROM ( SELECT trip_no, CAST(id_comp AS CHAR(25)) id_comp, CAST(plane AS CHAR(25)) plane, CAST(town_from AS CHAR(25)) town_from, CAST(town_to AS CHAR(25)) town_to, CONVERT(CHAR(25), time_out, 108) time_out, CONVERT(CHAR(25), time_in, 108) time_in FROM Trip WHERE trip_no =1100 ) UNPIVOT( info FOR spec IN (id_comp, plane, town_from, town_to, time_out, time_in) ) unpvt;
![Рекурсивные запросы WITH [ RECURSIVE ] <имя_алиаса_запроса> [ ( <список столбцов> ) ] AS](https://present5.com/presentation/53433469_222765947/image-17.jpg "Рекурсивные запросы WITH [ RECURSIVE ] <имя_алиаса_запроса> [ ( <список столбцов> ) ] AS")
Рекурсивные запросы WITH [ RECURSIVE ] <имя_алиаса_запроса> [ ( <список столбцов> ) ] AS (<запрос select> ) <запрос, использующий имя_алиаса_запроса> Общее Табличной Выражение (CTE) CTE - это представление, построенное специально для запроса и используемое однократно: всякий раз, когда мы выполняем запрос. В некотором смысле его можно назвать "непостоянным представлением".
Фирма имеет несколько пунктов приема вторсырья. Каждый пункт получает деньги для их выдачи сдатчикам вторсырья. Сведения о получении денег на пунктах приема записываются в таблицу: Income_o(point, date, inc) Первичным ключом является (point, date). При этом в столбец date записывается только дата (без времени), т. е. прием денег (inc) на каждом пункте производится не чаще одного раза в день. Сведения о выдаче денег сдатчикам вторсырья записываются в таблицу: Outcome_o(point, date, out) В этой таблице также первичный ключ (point, date) гарантирует отчетность каждого пункта о выданных деньгах (out) не чаще одного раза в день. В случае, когда приход и расход денег может фиксироваться несколько раз в день, используется другая схема с таблицами, имеющими первичный ключ code: Income(code, point, date, inc) Outcome(code, point, date, out) Здесь также значения столбца date не содержат времени.
Найти максимальную сумму прихода/расхода среди всех 4 -х таблиц базы данных "Вторсырье", а также тип операции, дату и пункт приема, когда и где она была зафиксирована. SELECT inc as max_sum, type, date, point FROM ( SELECT inc, 'inc' type, date, point FROM Income UNION ALL SELECT inc, 'inc' type, date, point FROM Income_o UNION ALL SELECT out, 'out' type, date, point FROM Outcome ) X WHERE inc >= ALL( SELECT inc FROM Income UNION ALL SELECT inc FROM Income_o UNION ALL SELECT out FROM Outcome )
Найти максимальную сумму прихода/расхода среди всех 4 -х таблиц базы данных "Вторсырье", а также тип операции, дату и пункт приема, когда и где она была зафиксирована. CREATE VIEW Inc_Out AS SELECT inc, 'inc' type, date, point FROM Income UNION ALL SELECT inc, 'inc' type, date, point FROM Income_o UNION ALL SELECT out, 'out' type, date, point FROM Outcome SELECT inc as max_sum, type, date, point FROM Inc_Out WHERE inc >= ALL(SELECT inc FROM Inc_Out )
Найти максимальную сумму прихода/расхода среди всех 4 -х таблиц базы данных "Вторсырье", а также тип операции, дату и пункт приема, когда и где она была зафиксирована. WITH Inc_Out AS ( SELECT inc, 'inc' type, date, point FROM Income UNION ALL SELECT inc, 'inc' type, date, point FROM Income_o UNION ALL SELECT out, 'out' type, date, point FROM Outcome ) SELECT inc as max_sum, type, date, point FROM Inc_Out WHERE inc >= ALL( SELECT inc FROM Inc_Out)
Пассажиров рейса 7772 от 11 ноября 2005 года требуется отправить другим ближайшим рейсом, вылетающим позже в тот же день в тот же пункт назначения. Таблица, которую надо обновить WITH Trip_for_replace AS( SELECT * FROM Pass_in_trip WHERE trip_no=7772 AND date='20051129' ) SELECT * FROM Trip_for_replace;
Информация о рейсе WITH Trip_for_replace AS ( SELECT * FROM Pass_in_trip WHERE trip_no=7772 AND date='20051129' ), Trip_7772 AS ( SELECT * FROM Trip WHERE trip_no=7772 ) SELECT * FROM Trip_7772;
Подходящий рейс WITH Trip_for_replace AS( SELECT * FROM Pass_in_trip WHERE trip_no=7772 AND date='20051129' ), Trip_7772 AS ( SELECT * FROM Trip WHERE trip_no=7772 ), Trip_candidates as( SELECT Trip. * FROM Trip, Trip_7772 WHERE Trip. town_from+Trip. town_to = rip_7772. town_from+Trip_7772. town_to and Trip. time_out > Trip_7772. time_out ) SELECT * FROM Trip_candidates;
Ближайший по времени WITH Trip_for_replace AS( SELECT * FROM Pass_in_trip WHERE trip_no=7772 AND date='20051129' ), Trip_7772 AS ( SELECT * FROM Trip WHERE trip_no=7772 ), Trip_candidates AS( SELECT Trip. * FROM Trip, Trip_7772 WHERE Trip. town_from+Trip. town_to = Trip_7772. town_from+Trip_7772. town_to and Trip. time_out > Trip_7772. time_out ), Trip_replace AS( SELECT * FROM Trip_candidates WHERE time_out <= ALL(SELECT time_out FROM Trip_candidates) ) SELECT * FROM Trip_replace;
WITH Trip_for_replace AS( SELECT * FROM Pass_in_trip WHERE trip_no=7772 AND date='20051129' ), Trip_7772 AS ( SELECT * FROM Trip WHERE trip_no=7772 ), Trip_candidates AS( SELECT Trip. * FROM Trip, Trip_7772 WHERE Trip. town_from+Trip. town_to = Trip_7772. town_from+Trip_7772. town_to and Trip. time_out > Trip_7772. time_out ), Trip_replace AS( SELECT * FROM Trip_candidates WHERE time_out <= ALL(SELECT time_out FROM Trip_candidates) ) UPDATE Trip_for_replace SET trip_no = (SELECT trip_no FROM Trip_replace);
-- создать таблицу CREATE TABLE T_NEWS (NEW_ID INTEGER NOT NULL PRIMARY KEY, NEW_FORUM VARCHAR(16), NEW_QUESTION VARCHAR(32)) GO -- заполнить данными INSERT INTO T_NEWS VALUES (1, 'SQL', 'What is SQL ? ') INSERT INTO T_NEWS VALUES (2, 'SQL', 'What do we do now ? ') INSERT INTO T_NEWS VALUES (3, 'Microsoft', 'Is SQL 2005 ready for use ? ') INSERT INTO T_NEWS VALUES (4, 'Microsoft', 'Did SQL 2000 use RECURSION ? ') INSERT INTO T_NEWS VALUES (5, 'Microsoft', 'Where am I ? ') -- традиционный запрос: SELECT COUNT(NEW_ID) AS NEW_NBR, NEW_FORUM FROM T_NEWS GROUP BY NEW_FORUM HAVING COUNT(NEW_ID) = ( SELECT MAX(NEW_NBR) FROM ( SELECT COUNT(NEW_ID) AS NEW_NBR, NEW_FORUM FROM T_NEWS GROUP BY NEW_FORUM ) T ) -- Результат : NEW_NBR NEW_FORUM ---------------- 3 Microsoft
WITH Q_COUNT_NEWS (NBR, FORUM) AS (SELECT COUNT(NEW_ID), NEW_FORUM FROM T_NEWS GROUP BY NEW_FORUM) SELECT NBR, FORUM FROM Q_COUNT_NEWS WHERE NBR = (SELECT MAX(NBR) FROM Q_COUNT_NEWS) Чтобы сделать рекурсию, синтаксис SQL нуждается в двух трюках: ПЕРВЫЙ: Вы должны предоставить начальную точку рекурсии. Это должно делаться с помощью запроса, состоящего из двух частей. Первый запрос сообщает, откуда начинать, а второй запрос говорит, где перейти к следующему шагу. Эти два запроса объединяются теоретико-множественным оператором UNION ALL. ВТОРОЙ: Вы должны связать CTE и SQL внутри CTE , чтобы обеспечить пошаговое выполнение. Это делается посредством <имя_алиаса_запроса> внутри SQL, который строит CTE.
![Рекурсивный CTE имеет следующий вид: WITH <имя>[(<список столбцов>)] AS( <SELECT. . . > --](https://present5.com/presentation/53433469_222765947/image-29.jpg "Рекурсивный CTE имеет следующий вид: WITH <имя>[(<список столбцов>)] AS( <SELECT. . . > --")
Рекурсивный CTE имеет следующий вид: WITH <имя>[(<список столбцов>)] AS( <SELECT. . . > -- анкорная часть UNION ALL -- рекурсивная часть <SELECT. . . FROM <имя>…> WHERE <условие продолжения итераций> ). . . Обратите внимание, что в рекурсивной части присутствует ссылка на имя CTE, т. е. внутри CTE ссылается само на себя. Это, собственно, и есть рекурсия. Естественно, анкорная и рекурсивная части должны иметь одинаковый набор столбцов.
CREATE TABLE T_VEHICULE (VHC_ID INTEGER NOT NULL PRIMARY KEY, VHC_ID_FATHER INTEGER FOREIGN KEY REFERENCES T_VEHICULE (VHC_ID), VHC_NAME VARCHAR(16)) -- Наполнить данными INSERT INTO T_VEHICULE VALUES (1, NULL, 'ALL') INSERT INTO T_VEHICULE VALUES (2, 1, 'SEA') INSERT INTO T_VEHICULE VALUES (3, 1, 'EARTH') INSERT INTO T_VEHICULE VALUES (4, 1, 'AIR') INSERT INTO T_VEHICULE VALUES (5, 2, 'SUBMARINE') INSERT INTO T_VEHICULE VALUES (6, 2, 'BOAT') INSERT INTO T_VEHICULE VALUES (7, 3, 'CAR') INSERT INTO T_VEHICULE VALUES (8, 3, 'TWO WHEELES') INSERT INTO T_VEHICULE VALUES (9, 3, 'TRUCK') INSERT INTO T_VEHICULE VALUES (10, 4, 'ROCKET') INSERT INTO T_VEHICULE VALUES (11, 4, 'PLANE') INSERT INTO T_VEHICULE VALUES (12, 8, 'MOTORCYCLE') INSERT INTO T_VEHICULE VALUES (13, 8, 'BYCYCLE') ALL |--SEA | |--SUBMARINE | |--BOAT |--EARTH | |--CAR | |--TWO WHEELES | | |--MOTORCYCLE | | |--BYCYCLE | |--TRUCK |--AIR |--ROCKET |--PLANE
Мы хотим узнать, откуда пришел МОТОЦИКЛ (MOTORCYCLE). Другими словами, требуется найти всех предков "МОТОЦИКЛА". Начать следует со строки данных, которая содержат motorcycle: SELECT VHC_NAME, VHC_ID_FATHER FROM T_VEHICULE WHERE VHC_NAME = 'MOTORCYCLE' Мы должны иметь родительский ID, чтобы перейти к следующему шагу. Второй запрос, который делает этот следующий шаг, должен быть написан подобно следующему: SELECT VHC_NAME, VHC_ID_FATHER FROM T_VEHICULE
Как Вы видите, запросы отличаются только тем, что мы не задаем фильтр WHERE для перехода к следующему шагу. Мы должны объединить эти два запроса с помощью UNION ALL, что определит пошаговый метод: SELECT VHC_NAME, VHC_ID_FATHER FROM T_VEHICULE WHERE VHC_NAME = 'MOTORCYCLE' UNION ALL SELECT VHC_NAME, VHC_ID_FATHER FROM T_VEHICULE Разместим все это в CTE: WITH tree (data, id) AS (SELECT VHC_NAME, VHC_ID_FATHER FROM T_VEHICULE WHERE VHC_NAME = 'MOTORCYCLE' UNION ALL SELECT VHC_NAME, VHC_ID_FATHER FROM T_VEHICULE)
Последний шаг должен сделать цикл, чтобы организовать выполнение пошагового метода. Это делается при использовании имени CTE в качестве таблицы внутри SQLзапроса CTE. В нашем случае мы должны соединить второй запрос CTE с самим CTE, организовав цепочку по tree. id = (второй запрос). VHC_ID. Это можно сделать следующим образом: WITH tree (data, id) AS (SELECT VHC_NAME, VHC_ID_FATHER FROM T_VEHICULE WHERE VHC_NAME = 'MOTORCYCLE' UNION ALL data id SELECT VHC_NAME, VHC_ID_FATHER -------- FROM T_VEHICULE V MOTORCYCLE 8 INNER JOIN tree t TWO WHEELES 3 ON t. id = V. VHC_ID) EARTH 1 SELECT * ALL NULL FROM tree
correlation __________________ | v WITH tree (data, id) AS (SELECT VHC_NAME, VHC_ID_FATHER FROM T_VEHICULE WHERE VHC_NAME = 'MOTORCYCLE' UNION ALL SELECT VHC_NAME, VHC_ID_FATHER FROM T_VEHICULE V INNER JOIN tree t <-------- ON t. id = V. VHC_ID) SELECT * FROM tree
WITH tree (data, id, level, pathstr) AS (SELECT VHC_NAME, VHC_ID, 0, CAST('' AS VARCHAR(MAX)) FROM T_VEHICULE WHERE VHC_ID_FATHER IS NULL UNION ALL SELECT VHC_NAME, VHC_ID, t. level + 1, t. pathstr + V. VHC_NAME FROM T_VEHICULE V INNER JOIN tree t ON t. id = V. VHC_ID_FATHER) SELECT SPACE(level) + data as data, id, level, pathstr FROM tree ORDER BY pathstr, id Data id level ALL AIR PLANE 11 ROCKET 10 EARTH CAR TRUCK 9 TWO WHEELES 8 BYCYCLE MOTORCYCLE 12 SEA BOAT 6 SUBMARINE 1 4 2 2 3 7 2 2 13 3 2 2 5 pathstr 0 4 AIRPLANE AIRROCKET 1 EARTH 2 EARTHCAR EARTHTRUCK EARTHTWO WHEELES 3 EARTHTWO WHEELESBYCYCLE EARTHTWO WHEELESMOTORCYCLE 1 SEABOAT 2 SEASUBMARINE
SELECT * FROM T_VEHICULE WHERE RIGHT_BOUND > 12 AND LEFT_BOUND < 13
Второй пример: сложная сеть Проблема состоит в том, чтобы проехать на машине от Парижа до Тулузы, используя сеть автострад. CREATE TABLE T_JOURNEY (JNY_FROM_TOWN VARCHAR(32), JNY_TO_TOWN VARCHAR(32), JNY_MILES INTEGER) -- занести данные : INSERT INTO T_JOURNEY VALUES ('PARIS', 'NANTES', 385) INSERT INTO T_JOURNEY VALUES ('PARIS', 'CLERMONT-FERRAND', 420) INSERT INTO T_JOURNEY VALUES ('PARIS', 'LYON', 470) INSERT INTO T_JOURNEY VALUES ('CLERMONT-FERRAND', 'MONTPELLIER', 335) INSERT INTO T_JOURNEY VALUES ('CLERMONT-FERRAND', 'TOULOUSE', 375) INSERT INTO T_JOURNEY VALUES ('LYON', 'MONTPELLIER', 305) INSERT INTO T_JOURNEY VALUES ('LYON', 'MARSEILLE', 320) INSERT INTO T_JOURNEY VALUES ('MONTPELLIER', 'TOULOUSE', 240) INSERT INTO T_JOURNEY VALUES ('MARSEILLE', 'NICE', 205)
PARIS 385 470 420 NANTES CLERMONT-FERRAND LYON 305 335 320 MONTPELLIER MARSEILLE 375 240 TOULOUSE 205 NICE
все поездки между городами WITH journey (TO_TOWN) AS (SELECT DISTINCT JNY_FROM_TOWN FROM T_JOURNEY UNION ALL SELECT JNY_TO_TOWN FROM T_JOURNEY AS arrival INNER JOIN journey AS departure ON departure. TO_TOWN = arrival. JNY_FROM_TOWN) SELECT * FROM journey TO_TOWN --------CLERMONT-FERRAND LYON MARSEILLE MONTPELLIER PARIS NANTES CLERMONT-FERRAND LYON MONTPELLIER MARSEILLE NICE TOULOUSE MONTPELLIER TOULOUSE NICE MONTPELLIER MARSEILLE NICE TOULOUSE MONTPELLIER TOULOUSE
WITH journey (TO_TOWN) AS (SELECT DISTINCT JNY_FROM_TOWN FROM T_JOURNEY WHERE JNY_FROM_TOWN = 'PARIS' UNION ALL SELECT JNY_TO_TOWN FROM T_JOURNEY AS arrival INNER JOIN journey AS departure ON departure. TO_TOWN = arrival. JNY_FROM_TOWN) SELECT * FROM journey TO_TOWN ---------------- PARIS NANTES CLERMONT-FERRAND LYON MONTPELLIER MARSEILLE NICE TOULOUSE MONTPELLIER TOULOUSE
Отфильтровали пункт назначения WITH journey (TO_TOWN) AS (SELECT DISTINCT JNY_FROM_TOWN FROM T_JOURNEY WHERE JNY_FROM_TOWN = 'PARIS' UNION ALL SELECT JNY_TO_TOWN FROM T_JOURNEY AS arrival INNER JOIN journey AS departure ON departure. TO_TOWN = arrival. JNY_FROM_TOWN) SELECT * FROM journey WHERE TO_TOWN = 'TOULOUSE' TO_TOWN -------TOULOUSE
WITH journey (TO_TOWN, STEPS) AS (SELECT DISTINCT JNY_FROM_TOWN, 0 FROM T_JOURNEY WHERE JNY_FROM_TOWN = 'PARIS' UNION ALL SELECT JNY_TO_TOWN, departure. STEPS + 1 FROM T_JOURNEY AS arrival INNER JOIN journey AS departure ON departure. TO_TOWN = arrival. JNY_FROM_TOWN) SELECT * FROM journey WHERE TO_TOWN = 'TOULOUSE' TO_TOWN STEPS ------- TOULOUSE 3 TOULOUSE 2 TOULOUSE 3
WITH journey (TO_TOWN, STEPS, DISTANCE) AS (SELECT DISTINCT JNY_FROM_TOWN, 0, 0 FROM T_JOURNEY WHERE JNY_FROM_TOWN = 'PARIS' UNION ALL SELECT JNY_TO_TOWN, departure. STEPS + 1, departure. DISTANCE + arrival. JNY_MILES FROM T_JOURNEY AS arrival INNER JOIN journey AS departure ON departure. TO_TOWN = arrival. JNY_FROM_TOWN) TO_TOWN STEPS DISTANCE SELECT * --------------- FROM journey TOULOUSE 3 1015 WHERE TO_TOWN = 'TOULOUSE' TOULOUSE 2 795 TOULOUSE 3 995
WITH journey (TO_TOWN, STEPS, DISTANCE, WAY) AS (SELECT DISTINCT JNY_FROM_TOWN, 0, 0, CAST('PARIS' AS VARCHAR(MAX)) FROM T_JOURNEY WHERE JNY_FROM_TOWN = 'PARIS' UNION ALL SELECT JNY_TO_TOWN, departure. STEPS + 1, departure. DISTANCE + arrival. JNY_MILES, departure. WAY + ', ' + arrival. JNY_TO_TOWN FROM T_JOURNEY AS arrival INNER JOIN journey AS departure ON departure. TO_TOWN = arrival. JNY_FROM_TOWN) SELECT * FROM journey WHERE TO_TOWN = 'TOULOUSE' TO_TOWN STEPS DISTANCE WAY ----------- ------------------------TOULOUSE 3 1015 PARIS, LYON, MONTPELLIER, TOULOUSE 2 795 PARIS, CLERMONT-FERRAND, TOULOUSE 3 995 PARIS, CLERMONT-FERRAND, MONTPELLIER, TOULOUSE
Задача коммивояжера WITH journey (TO_TOWN, STEPS, DISTANCE, WAY) AS (SELECT DISTINCT JNY_FROM_TOWN, 0, 0, CAST('PARIS' AS VARCHAR(MAX)) FROM T_JOURNEY WHERE JNY_FROM_TOWN = 'PARIS' UNION ALL SELECT JNY_TO_TOWN, departure. STEPS + 1, departure. DISTANCE + arrival. JNY_MILES, departure. WAY + ', ' + arrival. JNY_TO_TOWN FROM T_JOURNEY AS arrival INNER JOIN journey AS departure ON departure. TO_TOWN = arrival. JNY_FROM_TOWN) SELECT TOP 1 FROM journey WHERE TO_TOWN = 'TOULOUSE' ORDER BY DISTANCE TO_TOWN STEPS DISTANCE WAY ----------- ----------------TOULOUSE 2 795 PARIS, CLERMONT-FERRAND, TOULOUSE
WITH journey (TO_TOWN, STEPS, DISTANCE, WAY) AS (SELECT DISTINCT JNY_FROM_TOWN, 0, 0, CAST('PARIS' AS VARCHAR(MAX)) FROM T_JOURNEY WHERE JNY_FROM_TOWN = 'PARIS' UNION ALL SELECT JNY_TO_TOWN, departure. STEPS + 1, departure. DISTANCE + arrival. JNY_MILES, departure. WAY + ', ' + arrival. JNY_TO_TOWN FROM T_JOURNEY AS arrival INNER JOIN journey AS departure ON departure. TO_TOWN = arrival. JNY_FROM_TOWN), short (DISTANCE) AS (SELECT MIN(DISTANCE) FROM journey WHERE TO_TOWN = 'TOULOUSE') SELECT * FROM journey j INNER JOIN short s ON j. DISTANCE = s. DISTANCE WHERE TO_TOWN = 'TOULOUSE'
Мы можем проехать из Парижа до Лиона, но нам не позволено совершить поездку из Лиона до Парижа. Для этого мы должны добавить обратные пути в таблицу, например: JNY_FROM_TOWN JNY_TO_TOWN JNY_MILES ------------------ LYON PARIS 470 Это может быть сделано с помощью очень простого запроса: INSERT INTO T_JOURNEY SELECT JNY_TO_TOWN, JNY_FROM_TOWN, JNY_MILES FROM T_JOURNEY
WITH journey (TO_TOWN, STEPS, DISTANCE, WAY) AS (SELECT DISTINCT JNY_FROM_TOWN, 0, 0, CAST('PARIS' AS VARCHAR(MAX)) FROM T_JOURNEY WHERE JNY_FROM_TOWN = 'PARIS' UNION ALL SELECT JNY_TO_TOWN, departure. STEPS + 1, departure. DISTANCE + arrival. JNY_MILES, departure. WAY + ', ' + arrival. JNY_TO_TOWN FROM T_JOURNEY AS arrival INNER JOIN journey AS departure ON departure. TO_TOWN = arrival. JNY_FROM_TOWN WHERE departure. WAY NOT LIKE '%' + arrival. JNY_TO_TOWN + '%') SELECT * FROM journey WHERE TO_TOWN = 'TOULOUSE'
TO_TOWN STEPS DISTANCE WAY ------------------------------------ TOULOUSE 3 1015 PARIS, LYON, MONTPELLIER, TOULOUSE 4 1485 PARIS, LYON, MONTPELLIER, CLERMONT-FERRAND, TOULOUSE 2 795 PARIS, CLERMONT-FERRAND, TOULOUSE 3 995 PARIS, CLERMONT-FERRAND, MONTPELLIER, TOULOUSE
CREATE PROCEDURE P_WHAT_TO_DELETE_BEFORE @TABLE_TO_DELETE VARCHAR(128), -- Таблица, которую требуется удалить @DB VARCHAR(128), -- база данных @USR VARCHAR(128) -- схема (dbo в большинстве случаев) AS WITH T_CONTRAINTES (table_name, father_table_name) AS (SELECT DISTINCT CTU. TABLE_NAME, TCT. TABLE_NAME FROM INFORMATION_SCHEMA. REFERENTIAL_CONSTRAINTS RFC INNER JOIN INFORMATION_SCHEMA. CONSTRAINT_TABLE_USAGE CTU ON RFC. CONSTRAINT_CATALOG = CTU. CONSTRAINT_CATALOG AND RFC. CONSTRAINT_SCHEMA = CTU. CONSTRAINT_SCHEMA AND RFC. CONSTRAINT_NAME = CTU. CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA. TABLE_CONSTRAINTS TCT ON RFC. UNIQUE_CONSTRAINT_CATALOG = TCT. CONSTRAINT_CATALOG AND RFC. UNIQUE_CONSTRAINT_SCHEMA = TCT. CONSTRAINT_SCHEMA AND RFC. UNIQUE_CONSTRAINT_NAME = TCT. CONSTRAINT_NAME WHERE CTU. TABLE_CATALOG = @DB AND CTU. TABLE_SCHEMA = @USR) , T_TREE_CONTRAINTES (table_to_delete, level) AS (SELECT DISTINCT table_name, 0 FROM T_CONTRAINTES WHERE father_table_name = @TABLE_TO_DELETE UNION ALL SELECT prior. T. table_name, level - 1 FROM T_CONTRAINTES prior. T INNER JOIN T_TREE_CONTRAINTES begin. T ON begin. T. table_to_delete = prior. T. father_table_name WHERE prior. T. father_table_name <> prior. T. table_name) SELECT DISTINCT * FROM T_TREE_CONTRAINTES ORDER BY level GO
Скачать презентацию INSERT INTO dbo Orders orderid orderdate empid custid
Презентация Microsoft Office PowerPoint.ppt