Краткий курс SQL - История изменений

SYSDBA: Reverted edits by ErvarBocro (Talk); changed back to last version by Bugmenot

2008-12-17T09:08:37Z

Reverted edits by ErvarBocro (Talk); changed back to last version by Bugmenot

Внесённые изменения

ErvarBocro: vimonrel

2008-12-16T18:27:48Z

vimonrel

Внесённые изменения

Bugmenot: /* Использование подзапросов */ Откатил совсем последнюю часть изменений HkoCcb от 19:27, 9 July 2007)

2008-12-14T05:56:27Z

‎Использование подзапросов: Откатил совсем последнюю часть изменений HkoCcb от 19:27, 9 July 2007)

Bugmenot: /* Использование подзапросов */ Откатил последнюю часть изменений HkoCcb от 19:27, 9 July 2007

2008-12-14T05:54:53Z

‎Использование подзапросов: Откатил последнюю часть изменений HkoCcb от 19:27, 9 July 2007

Bugmenot: /* Представления */ Откатил часть изменений HkoCcb от 19:27, 9 July 2007

2008-12-14T05:53:23Z

‎Представления: Откатил часть изменений HkoCcb от 19:27, 9 July 2007

Bugmenot: /* Использование подзапросов */ поотдельности -> по отдельности

2008-12-14T05:34:43Z

‎Использование подзапросов: поотдельности -> по отдельности

Bugmenot: /* Объединение результатов нескольких запросов */

2008-12-14T05:19:53Z

‎Объединение результатов нескольких запросов

Bugmenot: /* Агрегатные функции и группировка записей */

2008-12-14T05:06:03Z

‎Агрегатные функции и группировка записей

SYSDBA: Reverted edits by BocchIleto (Talk); changed back to last version by SYSDBA

2008-07-15T10:08:31Z

Reverted edits by BocchIleto (Talk); changed back to last version by SYSDBA

Внесённые изменения

BocchIleto: olovarlie

2008-07-14T16:02:09Z

olovarlie

Внесённые изменения

@@ Строка 604: / Строка 604: @@
    (COALESCE((SELECT SUM(IIF(r.homescore > r.awayscore, 1, 0)) FROM lsn_result r WHERE r.homekey = t.id), 0)
-      COALESCE((SELECT SUM(IIF(r.homescore < r.awayscore, 1, 0)) FROM lsn_result r WHERE r.awaykey = t.id), 0)) AS W
+    + COALESCE((SELECT SUM(IIF(r.homescore < r.awayscore, 1, 0)) FROM lsn_result r WHERE r.awaykey = t.id), 0)) AS W
 Если приглядеться внимательно, выражение состоит из двух подзапросов. Один определяет количество побед текущей команды в домашних матчах, а второй -- в гостевых. Мы воспользовались функцией IFF, которая принимает на вход три аргумента, первый из которых должен быть булевским варажением. Если Истина, то возвращается второй аргумент, если Ложь, -- третий. Таким образом IIF(r.homescore > r.awayscore, 1, 0) вернет единицу, если домашняя команда одержала победу (забила голов больше) и 0 -- в противном случае. Просумировав все единицы и нули с помощью уже известной нам функции SUM получим количество побед в домашних матчах. Второе слагаемое -- количество побед в гостях. Но, зачем тут еще и функция COALESCE? Попытаемся разобраться. Логика ее действия такова: вернуть первый аргумент, если он не равен NULL или второй -- в противном случае. Осталось понять, что такое NULL значение.

@@ Строка 579: / Строка 579: @@
      (SELECT COUNT(*) FROM lsn_result r WHERE r.homekey = t.id OR r.awaykey = t.id) AS GP,
      (COALESCE((SELECT SUM(IIF(r.homescore > r.awayscore, 1, 0)) FROM lsn_result r WHERE r.homekey = t.id), 0)
-        COALESCE((SELECT SUM(IIF(r.homescore < r.awayscore, 1, 0)) FROM lsn_result r WHERE r.awaykey = t.id), 0)) AS W,
+      + COALESCE((SELECT SUM(IIF(r.homescore < r.awayscore, 1, 0)) FROM lsn_result r WHERE r.awaykey = t.id), 0)) AS W,
      (COALESCE((SELECT SUM(IIF(r.homescore = r.awayscore, 1, 0)) FROM lsn_result r
        WHERE r.homekey = t.id OR r.awaykey = t.id), 0)) AS D,
      (COALESCE((SELECT SUM(IIF(r.homescore < r.awayscore, 1, 0)) FROM lsn_result r WHERE r.homekey = t.id), 0)
-        COALESCE((SELECT SUM(IIF(r.homescore > r.awayscore, 1, 0)) FROM lsn_result r WHERE r.awaykey = t.id), 0)) AS L,
+      + COALESCE((SELECT SUM(IIF(r.homescore > r.awayscore, 1, 0)) FROM lsn_result r WHERE r.awaykey = t.id), 0)) AS L,
      (COALESCE((SELECT SUM(r.homescore) FROM lsn_result r WHERE r.homekey = t.id), 0)
-        COALESCE((SELECT SUM(r.awayscore) FROM lsn_result r WHERE r.awaykey = t.id), 0)) AS GS,
+      + COALESCE((SELECT SUM(r.awayscore) FROM lsn_result r WHERE r.awaykey = t.id), 0)) AS GS,
      (COALESCE((SELECT SUM(r.homescore) FROM lsn_result r WHERE r.awaykey = t.id), 0)
-        COALESCE((SELECT SUM(r.awayscore) FROM lsn_result r WHERE r.homekey = t.id), 0)) AS GA
+      + COALESCE((SELECT SUM(r.awayscore) FROM lsn_result r WHERE r.homekey = t.id), 0)) AS GA
    FROM
      lsn_team t

@@ Строка 628: / Строка 628: @@
 === Представления ===
-Пристально взглянув на получившуюся у нас турнирную таблицу мы увидим, что она несколько отличается от тех таблиц, которые печатают в спортивных изданиях. Чего-то не хватает. Отсутствует колонка с очками, набранными командами. К сожалению, мы не можем написать в запросе выражение, сославшись на алиасы колонок, что-нибудь вроде: W * 3   D (по три очка за победу и по одному за ничью), а переписывать повторно эти жуткие формулы с COALESCE как-то не хочется. Что ж, достроить нашу турнирную таблицу поможет такой объект метаданных, как Представление (VIEW). Представление создается на основе SELECT запроса и ведет себя подобно обычной таблице, т.е. его можно использовать в других SQL запросах. Данные представления не хранятся в базе, а при каждом обращении динамически подготавливаются сервером. Создается представление с помощью команды [[CREATE VIEW]]:
+Пристально взглянув на получившуюся у нас турнирную таблицу мы увидим, что она несколько отличается от тех таблиц, которые печатают в спортивных изданиях. Чего-то не хватает. Отсутствует колонка с очками, набранными командами. К сожалению, мы не можем написать в запросе выражение, сославшись на алиасы колонок, что-нибудь вроде: W * 3 + D (по три очка за победу и по одному за ничью), а переписывать повторно эти жуткие формулы с COALESCE как-то не хочется. Что ж, достроить нашу турнирную таблицу поможет такой объект метаданных, как Представление (VIEW). Представление создается на основе SELECT запроса и ведет себя подобно обычной таблице, т.е. его можно использовать в других SQL запросах. Данные представления не хранятся в базе, а при каждом обращении динамически подготавливаются сервером. Создается представление с помощью команды [[CREATE VIEW]]:
   CREATE VIEW lsn_vtable (name, gp, w, d, l, gs, ga) AS
@@ Строка 635: / Строка 635: @@
     (SELECT COUNT(*) FROM lsn_result r WHERE r.homekey = t.id OR r.awaykey = t.id),
     (COALESCE((SELECT SUM(IIF(r.homescore > r.awayscore, 1, 0)) FROM lsn_result r WHERE r.homekey = t.id), 0)
-       COALESCE((SELECT SUM(IIF(r.homescore < r.awayscore, 1, 0)) FROM lsn_result r WHERE r.awaykey = t.id), 0)),
+     + COALESCE((SELECT SUM(IIF(r.homescore < r.awayscore, 1, 0)) FROM lsn_result r WHERE r.awaykey = t.id), 0)),
     (COALESCE((SELECT SUM(IIF(r.homescore = r.awayscore, 1, 0)) FROM lsn_result r
       WHERE r.homekey = t.id OR r.awaykey = t.id), 0)),
     (COALESCE((SELECT SUM(IIF(r.homescore < r.awayscore, 1, 0)) FROM lsn_result r WHERE r.homekey = t.id), 0)
-       COALESCE((SELECT SUM(IIF(r.homescore > r.awayscore, 1, 0)) FROM lsn_result r WHERE r.awaykey = t.id), 0)),
+     + COALESCE((SELECT SUM(IIF(r.homescore > r.awayscore, 1, 0)) FROM lsn_result r WHERE r.awaykey = t.id), 0)),
     (COALESCE((SELECT SUM(r.homescore) FROM lsn_result r WHERE r.homekey = t.id), 0)
-       COALESCE((SELECT SUM(r.awayscore) FROM lsn_result r WHERE r.awaykey = t.id), 0)),
+     + COALESCE((SELECT SUM(r.awayscore) FROM lsn_result r WHERE r.awaykey = t.id), 0)),
     (COALESCE((SELECT SUM(r.homescore) FROM lsn_result r WHERE r.awaykey = t.id), 0)
-       COALESCE((SELECT SUM(r.awayscore) FROM lsn_result r WHERE r.homekey = t.id), 0))
+     + COALESCE((SELECT SUM(r.awayscore) FROM lsn_result r WHERE r.homekey = t.id), 0))
   FROM
     lsn_team t
@@ Строка 650: / Строка 650: @@
    SELECT
-     name, gp, w, d, l, gs, ga, (w * 3   d) AS Pt
+     name, gp, w, d, l, gs, ga, (w * 3 + d) AS Pt
    FROM
      lsn_vtable

← Предыдущая		Версия 05:34, 14 декабря 2008
Строка 593:		Строка 593:
	1		1

−	Так сложно! -- Скажете вы. Попробуем разобраться с каждой строкой представленного запроса ~~поотдельности~~.	+	Так сложно! -- Скажете вы. Попробуем разобраться с каждой строкой представленного запроса по отдельности.

	t.name		t.name

@@ Строка 544: / Строка 544: @@
    WHERE
      r.homescore > r.awayscore
-−
    UNION ALL
-−
    SELECT
      r.gamedate,

@@ Строка 425: / Строка 425: @@
 Агрегатные функции позволяют выполнять вычисления над группой записей. Выведем следующую статистическую информацию: количество игр, среднее количество голов, забитых дома, и среднее количество голов, забитых в гостях.
-SELECT
+ SELECT
     COUNT(*) AS GameCount,
     AVG(CAST(homescore AS NUMERIC(4,2))) AS HomeAvg,
@@ Строка 509: / Строка 509: @@
 Обратите внимание, как мы сначала извлекли из даты год и месяц с помощью функций EXTRACT и затем объединили их в одну строку с помощью оператора ||.
 === Объединение результатов нескольких запросов ===