在列中使用多行数据

Question

这可能不可能在一个查询，但我想看看我的选择是什么。

我有一个大查询，它返回库存(在本例中为树)的每个部分的数据。查询从几个不同的表中获取数据。我主要使用左外连接来引入这些信息，所以如果它不存在，我可以忽略它并取NULL。我有一个有趣的情况，"树"one_answers"害虫"之间存在一对多的关系。

• 树表:treeID, treeHeight等....
• 有害生物(对树的有害生物)表:瘟疫，树木，害虫.....

我需要一个查询，获取每棵树的前6个害虫，并将它们作为列返回:

• pest1, pest2, pest3…等等......

我知道我可以在多个查询中这样做，但是每次使用都会发生数千次，我们的服务器无法处理。

一些注意事项:我们使用ColdFusionMX7，我对存储过程的了解非常少。

Answer 1

一种方法是按树生成一个表示害虫排名的列，然后以排名作为连接条件将排名的害虫表连接到树表。确保使用ROW_NUMBER而不是RANK，因为平局会导致RANK中的数字重复(而不是ROW_NUMBER)，并确保使用LEFT OUTER连接，这样害虫较少的树就不会被排除在外。此外，我订购了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