SQL: получение столбца из строки, выбранной агрегированной функцией в представлении
У меня есть три таблицы SQL: Release (который представляет собой выпуск фильма), Media (который представляет отдельные части записываемых носителей в этих выпусках; т.е. для комбинаций Blu-ray/DVD, будет две строки в Mediaодин Blu-ray и один DVD, которые указывают на одну и ту же строку в Release) а также MediaType (который определяет Blu-ray, DVD, VHS и т. д.). Есть отношения один ко многим для Release/Media а также MediaType/Media, с Media находясь на "многих" сторонах обоих отношений. У меня есть вид на Release, vRelease, который содержит агрегатные функции, такие как COUNT это показывает, сколько медиа связано с этим выпуском. Это то, что я до сих пор имею для этого взгляда:
SELECT dbo.Release.ReleaseID
,dbo.Release.Name
,CASE WHEN Release.Compilation = 0 THEN 'No' WHEN Release.Compilation = 1 THEN 'Yes' END AS Compilation
,dbo.Release.Owner
,CASE WHEN Release.LentOut = 0 THEN 'No' WHEN Release.LentOut = 1 THEN 'Yes' END AS LentOut
,COUNT(dbo.Media.ReleaseID) AS NumberOfMedia
,MIN(dbo.Media.MediaID) AS FirstMediaID
,MIN(dbo.MediaType.Name) AS FirstMediaType
FROM dbo.MediaType INNER JOIN
dbo.Media ON dbo.MediaType.MediaTypeID = dbo.Media.MediaTypeID RIGHT OUTER JOIN
dbo.Release ON dbo.Media.ReleaseID = dbo.Release.ReleaseID
GROUP BY dbo.Release.ReleaseID, dbo.Release.Name, dbo.Release.Compilation, dbo.Release.Owner, dbo.Release.LentOut
Вы заметите, что я также включил два других статистических столбца: FirstMediaID захватывает идентификатор носителя, связанного с этим выпуском, который появляется первым в Media таблица (т. е. если с выпуском связаны два DVD-диска, он получает один с меньшим значением идентификатора). Этот столбец сам по себе бесполезен; то, что я хочу сделать, это, в свою очередь, получить MediaType что это Media связан с. Другими словами, я хочу столбец, который показывает MediaType из первых Media который прикреплен к каждому Release, Колонка после этого, FirstMediaType, должен сделать это, но вместо этого он получает MediaType среди всех Media связано с Release и выбирает тот, который находится в алфавитном порядке первым - это означает, что Blu-ray всегда будет иметь приоритет над DVD (что хорошо), но Audio CD всегда будет иметь приоритет над всем остальным (что не хорошо).
Как я могу получить FirstMediaType столбец в этом представлении, чтобы получить MediaType СМИ, указанных в FirstMediaID?
ОБНОВЛЕНИЕ: Вот таблицы, их столбцы и некоторые примеры строк.
Пара из Release:
+-----------+----------------------------------------+-------+-------------+---------+
| ReleaseID | Name | Owner | Compilation | LentOut |
+-----------+----------------------------------------+-------+-------------+---------+
| 2 | Alice in Wonderland | NULL | 0 | 0 |
| 6 | 4 Film Favorites - Family Comedies | NULL | 1 | 0 |
| 8 | Aladdin | NULL | 0 | 0 |
| 463 | Harry Potter and the Half-Blood Prince | NULL | 0 | 1 |
| 534 | Spirited Away | Ryan | 0 | 0 |
| 571 | The Original Christmas Classics | NULL | 1 | 0 |
+-----------+----------------------------------------+-------+-------------+---------+
Compilation указывает на выпуск, в котором более одного фильма.
Соответствующие записи в Media:
+---------+-------------+-------------------------------------------------------------------------------------+-----------+
| MediaID | MediaTypeID | Name | ReleaseID |
+---------+-------------+-------------------------------------------------------------------------------------+-----------+
| 2 | 2 | Movie | 2 |
| 3 | 1 | Movie | 2 |
| 12 | 1 | Space Jam; Looney Tunes: Back in Action | 6 |
| 13 | 1 | Funky Monkey; Osmosis Jones | 6 |
| 17 | 3 | Movie | 8 |
| 620 | 1 | Movie | 463 |
| 726 | 1 | Movie | 534 |
| 807 | 1 | Rudolph the Red-Nosed Reindeer; Cricket on the Hearth | 571 |
| 808 | 1 | Frosty the Snowman; Frosty Returns | 571 |
| 809 | 1 | Santa Claus is Comin' to Town!; Mr. Magoo's Christmas Carol; The Little Drummer Boy | 571 |
| 810 | 4 | Tracks 1-7 | 571 |
+---------+-------------+-------------------------------------------------------------------------------------+-----------+
Первые несколько в MediaType:
+-------------+--------------+
| MediaTypeID | Name |
+-------------+--------------+
| 1 | DVD Disc |
| 2 | Blu-ray Disc |
| 3 | VHS |
| 4 | Audio CD |
+-------------+--------------+
Соответствующие записи в vRelease ДОЛЖНО быть так:
+-----------+----------------------------------------+-------------+-------+---------+---------------+--------------+----------------+
| ReleaseID | Name | Compilation | Owner | LentOut | NumberOfMedia | FirstMediaID | FirstMediaType |
+-----------+----------------------------------------+-------------+-------+---------+---------------+--------------+----------------+
| 2 | Alice in Wonderland | No | NULL | No | 2 | 2 | Blu-ray Disc |
| 6 | 4 Film Favorites - Family Comedies | Yes | NULL | No | 2 | 12 | DVD Disc |
| 8 | Aladdin | No | NULL | No | 1 | 17 | VHS |
| 463 | Harry Potter and the Half-Blood Prince | No | NULL | Yes | 1 | 620 | DVD Disc |
| 534 | Spirited Away | No | Ryan | No | 1 | 726 | DVD Disc |
| 571 | The Original Christmas Classics | Yes | NULL | No | 4 | 807 | DVD Disc |
+-----------+----------------------------------------+-------------+-------+---------+---------------+--------------+----------------+
Но на самом деле это так:
+-----------+----------------------------------------+-------------+-------+---------+---------------+--------------+----------------+
| ReleaseID | Name | Compilation | Owner | LentOut | NumberOfMedia | FirstMediaID | FirstMediaType |
+-----------+----------------------------------------+-------------+-------+---------+---------------+--------------+----------------+
| 2 | Alice in Wonderland | No | NULL | No | 2 | 2 | Blu-ray Disc |
| 6 | 4 Film Favorites - Family Comedies | Yes | NULL | No | 2 | 12 | DVD Disc |
| 8 | Aladdin | No | NULL | No | 1 | 17 | VHS |
| 463 | Harry Potter and the Half-Blood Prince | No | NULL | Yes | 1 | 620 | DVD Disc |
| 534 | Spirited Away | No | Ryan | No | 1 | 726 | DVD Disc |
| 571 | The Original Christmas Classics | Yes | NULL | No | 4 | 807 | Audio CD |
+-----------+----------------------------------------+-------------+-------+---------+---------------+--------------+----------------+
Это последняя проблема.
4 ответа
Очень удобный метод, который возвращает целые строки, связанные с потребностями, такими как "Первый", "Последний", "Самый ранний", "Последний", заключается в использовании row_number() over(), Здесь вам нужен "первый тип носителя", поэтому он уместен здесь.
Как вы увидите в следующем запросе, присоединение таблицы [Media] заменяется подзапросом, который включает вычисление номера строки. Мы тут partition by ReleaseID и order by MediaID, поэтому для каждого ReleaseID первой строкой будет строка с наименьшим значением MediaID. Затем в соединении с этой производной таблицей добавляется дополнительное условие, чтобы рассматривать только строки с номером строки 1.
Предлагаемый запрос
SELECT
r.ReleaseID
, m.MediaID
, mt.MediaTypeID
, mt.name MediaName
, r.Name
, CASE
WHEN r.Compilation = 0 THEN 'No'
WHEN r.Compilation = 1 THEN 'Yes'
END AS compilation
, r.Owner
, CASE
WHEN r.LentOut = 0 THEN 'No'
WHEN r.LentOut = 1 THEN 'Yes'
END AS lentout
FROM dbo.Release r
INNER JOIN (
SELECT
Media.*
, ROW_NUMBER() OVER(PARTITION BY ReleaseID
ORDER BY MediaID) AS rn
FROM dbo.Media
) m ON r.ReleaseID = m.ReleaseID and rn = 1
INNER JOIN dbo.MediaType mt ON m.MediaTypeID = mt.MediaTypeID
Результат
| ReleaseID | MediaID | MediaTypeID | MediaName | Name | compilation | Owner | lentout |
|-----------|---------|-------------|--------------|----------------------------------------|-------------|--------|---------|
| 2 | 2 | 2 | Blu-ray Disc | Alice in Wonderland | No | (null) | No |
| 6 | 12 | 1 | DVD Disc | 4 Film Favorites - Family Comedies | Yes | (null) | No |
| 8 | 17 | 3 | VHS | Aladdin | No | (null) | No |
| 463 | 620 | 1 | DVD Disc | Harry Potter and the Half-Blood Prince | No | (null) | Yes |
| 534 | 726 | 1 | DVD Disc | Spirited Away | No | Ryan | No |
| 571 | 807 | 1 | DVD Disc | The Original Christmas Classics | Yes | (null) | No |
В итоге я нашел простой способ сделать то, что хотел. Он не такой причудливый, как ответ Used_By_Already (который, насколько я мог сказать, сработал) и, вероятно, где-то нарушает правило SQL Best Practices, но его гораздо легче понять и поддерживать - по крайней мере, для моего новичка.
Так как проблема заключалась в том, чтобы попытаться заставить представление использовать агрегированный столбец, вычисленный в соединении, я просто разделил двухэтапное действие на два представления. vReleasePre имеет все столбцы, которые я выделил в моем исходном запросе, за исключением FirstMediaType, vRelease теперь просто берет все столбцы из vReleasePre и добавляет FirstMediaType, который берет свое значение от объединения в конце: LEFT OUTER JOIN dbo.vMedia ON dbo.vReleasePre.FirstMediaID = dbo.vMedia.MediaID, где vMedia это вид со всеми столбцами из Mediaплюс MediaType колонка (у меня уже была vMedia валяется).
Поскольку эта база данных используется в веб-приложении ASP.NET MVC через Entity Framework, а EF довольно странно относится к тому, что она будет принимать и не принимать в модель данных, я полагаю, что простое, хотя и обходное решение, вероятно, будет мой лучший вариант.
vReleasePre:
SELECT dbo.Release.ReleaseID
,dbo.Release.Name
,CASE WHEN Release.Compilation = 0 THEN 'No' WHEN Release.Compilation = 1 THEN 'Yes' END AS Compilation
,dbo.Release.Owner
,CASE WHEN Release.LentOut = 0 THEN 'No' WHEN Release.LentOut = 1 THEN 'Yes' END AS LentOut
,COUNT(dbo.Media.ReleaseID) AS NumberOfMedia
,MIN(dbo.Media.MediaID) AS FirstMediaID
FROM dbo.MediaType INNER JOIN
dbo.Media ON dbo.MediaType.MediaTypeID = dbo.Media.MediaTypeID RIGHT OUTER JOIN
dbo.Release ON dbo.Media.ReleaseID = dbo.Release.ReleaseID
GROUP BY dbo.Release.ReleaseID, dbo.Release.Name, dbo.Release.Compilation, dbo.Release.Owner, dbo.Release.LentOut
vRelease:
SELECT dbo.vReleasePre.ReleaseID
,dbo.vReleasePre.Name
,dbo.vReleasePre.Compilation
,dbo.vReleasePre.Owner
,dbo.vReleasePre.LentOut
,dbo.vReleasePre.NumberOfMedia
,dbo.vMedia.MediaType
FROM dbo.vReleasePre LEFT OUTER JOIN
dbo.vMedia ON dbo.vReleasePre.FirstMediaID = dbo.vMedia.MediaID
Самый простой способ - добавить еще одно присоединение к вашему MediaType стол на FirstMediaId = MediaType.MediaId
;WITH data AS (
SELECT dbo.Release.ReleaseID
,dbo.Release.Name
,CASE WHEN Release.Compilation = 0 THEN 'No' WHEN Release.Compilation = 1 THEN 'Yes' END AS Compilation
,dbo.Release.Owner
,CASE WHEN Release.LentOut = 0 THEN 'No' WHEN Release.LentOut = 1 THEN 'Yes' END AS LentOut
,COUNT(dbo.Media.ReleaseID) AS NumberOfMedia
,MIN(dbo.Media.MediaID) AS FirstMediaID
FROM dbo.MediaType
INNER JOIN dbo.Media
ON dbo.MediaType.MediaTypeID = dbo.Media.MediaTypeID
RIGHT OUTER JOIN dbo.Release
ON dbo.Media.ReleaseID = dbo.Release.ReleaseID
GROUP BY dbo.Release.ReleaseID, dbo.Release.Name, dbo.Release.Compilation, dbo.Release.Owner, dbo.Release.LentOut
)
SELECT data.ReleaseId
,data.Name
,data.Compilation
,data.Owner
,data.LentOut
,data.NumberOfMedia
,data.FirstMediaId
,MediaType.Name as FirstMediaName
FROM data
LEFT OUTER JOIN dbo.MediaType
ON data.FirstMediaId = MediaType.MediaTypeId
Для мозга новичка, это подзапрос, который я использовал
SELECT
ROW_NUMBER() OVER(PARTITION BY ReleaseID
ORDER BY MediaID) AS rn
, Media.*
FROM dbo.Media
и это то, что он делает (см. столбец рН)
| rn | MediaID | MediaTypeID | Name | ReleaseID |
|----|---------|-------------|-------------------------------------------------------------------------------------|-----------|
| 1 | 2 | 2 | Movie | 2 |
| 2 | 3 | 1 | Movie | 2 |
| 1 | 12 | 1 | Space Jam; Looney Tunes: Back in Action | 6 |
| 2 | 13 | 1 | Funky Monkey; Osmosis Jones | 6 |
| 1 | 17 | 3 | Movie | 8 |
| 1 | 620 | 1 | Movie | 463 |
| 1 | 726 | 1 | Movie | 534 |
| 1 | 807 | 1 | Rudolph the Red-Nosed Reindeer; Cricket on the Hearth | 571 |
| 2 | 808 | 1 | Frosty the Snowman; Frosty Returns | 571 |
| 3 | 809 | 1 | Santa Claus is Comin' to Town!; Mr. Magoo's Christmas Carol; The Little Drummer Boy | 571 |
| 4 | 810 | 4 | Tracks 1-7 | 571 |
Теперь оставьте только те строки с 1 в столбце rn:
| rn | MediaID | MediaTypeID | Name | ReleaseID |
|----|---------|-------------|-------------------------------------------------------|-----------|
| 1 | 2 | 2 | Movie | 2 |
| 1 | 12 | 1 | Space Jam; Looney Tunes: Back in Action | 6 |
| 1 | 17 | 3 | Movie | 8 |
| 1 | 620 | 1 | Movie | 463 |
| 1 | 726 | 1 | Movie | 534 |
| 1 | 807 | 1 | Rudolph the Red-Nosed Reindeer; Cricket on the Hearth | 571 |
Затем присоедините только эти строки к Releases и MediaType
Бинго
= желаемый результат.
Не сложно, действительно не сложно. Вы действительно захотите узнать об этих оконных функциях, потому что они могут решить кучу проблем.