SQL Server CTE左外部联接 [英] SQL Server CTE left outer join

问题描述

我在SQL Server 2008中有2个表，其中customertest的列为客户id(cid)，其为老板的id(upid)，而conftest的列为cid，confname，confvalue

I have 2 tables in SQL Server 2008, customertest with columns customer id (cid) and it's boss id (upid), and conftest with cid, confname, confvalue

客户测试架构和数据:

质疑架构和数据:

我想知道如何设计CTE，如果conftest中的cid没有confname的confvalue，它将继续搜索upid，直到找到具有confname和confvalue.

I want to know how to design a CTE that if cid in conftest doesn't have that confname's confvalue, it will keep searching upid and till find a upper line which have confname and confvalue.

例如，如果我搜索cid = 4，我想得到100的值(这是正常情况).如果我搜索cid = 7或8，我希望得到200的值.

For example , I want to get value of 100 if I search for cid=4 (this is normal case). And I want to get value of 200 if I search for cid=7 or 8.

如果cid7和cid8具有子节点，那么如果我使用此CTE搜索，它们都将返回200(cid5).

And if cid7 and cid8 have child node , it will all return 200 (of cid5) if I search using this CTE.

我不知道如何执行此操作，我认为也许可以使用CTE和一些左外部联接，请给我一些示例?非常感谢.

I don't have a clue how to do this , I think maybe can use CTE and some left outer join, please give me some example ?? Thanks a lot.

推荐答案

从我的理解来看，我不认为您正在寻找CTE来做到这一点:

I don't think you are looking for a CTE to do that, from what I understand:

CREATE TABLE CustomerTest(
    CID INT,
    UPID INT
    );
CREATE TABLE ConfTest(
    CID INT,
    ConfName VARCHAR(45),
    ConfValue INT
    );

INSERT INTO CustomerTest VALUES
(1, 0),
(2, 1),
(3, 1),
(4, 2),
(5, 2),
(6, 3),
(7, 5),
(8, 5);

INSERT INTO ConfTest VALUES
(1, 'Budget', 1000),
(2, 'Budget', 700),
(3, 'Budget', 300),
(4, 'Budget', 100),
(5, 'Budget', 200),
(6, 'Budget', 300);

SELECT MAX(CNT.CID) AS CID,
       CNT.ConfName,
       MIN(CNT.ConfValue) AS ConfValue
FROM ConfTest CNT INNER JOIN CustomerTest CMT ON CMT.CID = CNT.CID
                                              OR CMT.UPID = CNT.CID
WHERE  CMT.CID = 7 -- You can test for values (8, 4) or any value you want :)
GROUP BY
       CNT.ConfName;

这篇关于SQL Server CTE左外部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！