SQL:在列中使用多行数据

Question

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

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

• 树表: treeID，treeHeight等。
• 病虫害表: pestID，treeID，pestRef.

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

• pest1，pest2，pest3.诸若此类。

我知道我可以在多个查询中做到这一点，但是每次使用都会发生数千次这种情况，而我们的服务器无法处理这个问题。

注意:我们使用的是ColdFusionMX7，而我对存储过程的了解非常低。

Answer 1

一种方法是生成一个逐树表示害虫等级的列，然后将排序的pest表加入到以秩作为连接条件的树表中。确保您使用的是ROW_NUMBER而不是秩，因为领带会导致重复的秩(但不是ROW_NUMBER)，并确保使用左外部联接，这样害虫较少的树就不会被排除在外。另外，我按两个条件订购，但是任何正常顺序的子句在这里都是有效的。

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