Блог

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

У меня есть большой запрос, который возвращает данные для каждой части инвентаря (в данном случае деревьев). Запрос получает данные из нескольких разных таблиц. В основном я использую левые внешние соединения для ввода этой информации, поэтому, если ее там нет, я могу проигнорировать ее и принять значение NULL. У меня интересная ситуация, когда между «деревом» и его «вредителями» существует отношение «один ко многим».

• древовидная таблица: treeID, treeHeight и т.д….
• таблица pest (вредитель для дерева): pestID, treeID, pestRef…..

Мне нужен запрос, который получает 6 лучших вредителей для каждого дерева и возвращает их в виде столбцов:

• pest1, pest2, pest3… и так далее.

Я знаю, что мог бы сделать это в нескольких запросах, однако это происходило бы тысячи раз только за одно использование, и наши серверы не могут с этим справиться.

Некоторые примечания: мы используем ColdFusionMX7, и мои знания хранимых процедур очень низки.

Один из подходов заключается в создании столбца, представляющего ранг вредителя по дереву, затем присоедините ранжированную таблицу вредителей к таблице дерева с рангом в качестве условия соединения. Убедитесь, что вы используете ROW_NUMBER, а не RANK, потому что привязка приведет к повторению чисел в RANK (но не ROW_NUMBER), и убедитесь, что вы используете ВНЕШНИЕ соединения СЛЕВА, чтобы не исключались деревья с меньшим количеством вредителей. Кроме того, я заказал по 2 условиям, но здесь допустимо все, что допустимо в обычном предложении ORDER BY.

 DECLARE @t TABLE (TreeID INT, TreeName VARCHAR(25));
DECLARE @p TABLE (PestID INT, TreeID INT, PestName VARCHAR(25));

INSERT INTO @t VALUES (1,'ash'),(2,'elm'),(3,'oak')
INSERT INTO @p VALUES (1,1,'ash borer'),(2,1,'tent catapilar'),(3,1,'black weevil'),(4,1,'brown weevil');
INSERT INTO @p VALUES (5,2,'elm thrip'),(6,2,'wooly adelgid');
INSERT INTO @p VALUES (7,3,'oak gall wasp'),(8,3,'asian longhorn beetle'),(9,3,'aphids');

WITH cteRankedPests as (
    SELECT PestID, TreeID, PestName, ROW_NUMBER() OVER (PARTITION BY TreeID ORDER BY PestName,PestID) as PestRank
    FROM @p 
)
SELECT T.TreeID, T.TreeName 
    , P1.PestID as P1ID, P1.PestName as P1Name
    , P2.PestID as P2ID, P2.PestName as P2Name
    , P3.PestID as P3ID, P3.PestName as P3Name
FROM @t as T 
    LEFT OUTER JOIN cteRankedPests as P1 ON T.TreeID = P1.TreeID AND P1.PestRank = 1
    LEFT OUTER JOIN cteRankedPests as P2 ON T.TreeID = P2.TreeID AND P2.PestRank = 2
    LEFT OUTER JOIN cteRankedPests as P3 ON T.TreeID = P3.TreeID AND P3.PestRank = 3