自加入表中

发布于 2024-12-06 12:59:51 字数 369 浏览 1 评论 0原文

 CustID       Name         ReferredBy
 1         Neeta Sayam  
 2         Dolly Dilly         1
 3         Meena Kimi          2

查找由其他人推荐的所有客户的姓名。

输出应该是 dolly dilly 和 meena kimi。

我已经成功找到通过查询推荐其他人的客户

 SELECT c1.name FROM Customer c1 JOIN Customer c2 ON c1.custid=c2. ReferredBy

原文

 CustID       Name         ReferredBy
 1         Neeta Sayam  
 2         Dolly Dilly         1
 3         Meena Kimi          2

Find the names of all customers who are referred by others.

the output should be dolly dilly and meena kimi.

I have succesfully found out for customers who have reffred others by query

 SELECT c1.name FROM Customer c1 JOIN Customer c2 ON c1.custid=c2. ReferredBy

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

瘫痪情歌 2024-12-13 12:59:51

除非我错过了什么：

SELECT *
FROM Customer
WHERE ReferredBy IS NOT NULL

Unless I'm missing something:

SELECT *
FROM Customer
WHERE ReferredBy IS NOT NULL

回复收藏 0 原文

悲欢浪云 2024-12-13 12:59:51

有很多方法可以实现您想要的目标，但一个有趣的是使用 CTE，因为它允许您按级别获取参考，例如在您的情况下，零级将是没有参考的 Neeta syam ，第一级是 dolly dilly 和 meena kimi。以下查询将返回 dolly dilly 和 meena kimi，它们位于 where reference = 1 指定的一级，如下所示：

WITH CTEs (Id, CustomerName, Reference, RefCustomer)
AS
(
  SELECT 
    Id,
    Name,
    0 As Reference, 
    CONVERT(VARCHAR(length), 'No Reference') AS RefCustomer 
  FROM Customers 
  WHERE ReferredBy IS NULL
  UNION ALL
  SELECT
    c.CustId,
    c.Name, 
    cs.Reference + 1, 
    cs.CustomerName 
  FROM Customers c 
  INNER JOIN Ctes cs ON c.ReferedBy = cs.Id
)
SELECT CustomerName, RefCustomer 
FROM Ctes 
WHERE Reference = 1;

There are a lot of ways to achieve what you want, but one interesting is using CTE because it allows you to get reference by level, for example in your case level zero will be Neeta syam that has no reference, level one is dolly dilly and meena kimi. The following query will return dolly dilly and meena kimi which are in the level one specified by where reference = 1 like so:

WITH CTEs (Id, CustomerName, Reference, RefCustomer)
AS
(
  SELECT 
    Id,
    Name,
    0 As Reference, 
    CONVERT(VARCHAR(length), 'No Reference') AS RefCustomer 
  FROM Customers 
  WHERE ReferredBy IS NULL
  UNION ALL
  SELECT
    c.CustId,
    c.Name, 
    cs.Reference + 1, 
    cs.CustomerName 
  FROM Customers c 
  INNER JOIN Ctes cs ON c.ReferedBy = cs.Id
)
SELECT CustomerName, RefCustomer 
FROM Ctes 
WHERE Reference = 1;

回复收藏 0 原文

~没有更多了~