问题描述
我在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左外部联接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!