带条件和递归的 SQL UNION
我的数据库类的 2 个查询遇到问题。
我有一个表“客户”,其中包含以下列:
name | id | date | points | REFERRAL
---------------------------------------------
daniel | 123456 | 01-01-01 | 50 | 321321
jack | 321321 | 01-01-01 | 30 | 555555
请注意,丹尼尔是由杰克推荐的
1)通过一个人的 ID 显示所有客户推荐以及他们对第三参考级别的推荐
首先,我想到这样的事情,只是为了参考电平 2:
SELECT C1.name
FROM Clients C1, Clients C2
WHERE C1.REFERRAL= 21000301
AND C1.id= C2.REFERRAL
这显然不适用于 AND。 然后我尝试 UNION:
SELECT C1.id, C1.REFERRAL
FROM Clients C1
WHERE C1.REFERRAL= 21000301
UNION
SELECT C2.id, C2.REFERRAL
FROM Clients C2
WHERE C2.REFERRAL= C1.ID
但无法在第二个 SELECT 上访问 C1
所以,我的问题是有一种方法可以使用 UNION 制定条件。 如果没有,我该如何解决这个问题?
2) 显示更多直接和间接推荐的客户的姓名和推荐总数。
在这一点上我完全迷失了:D,可能它是递归的? SQL中有类似的东西吗?
预先感谢,抱歉我的英语不好。
i'm having problems with 2 query for my DB class.
I have one table "clients" with this columns:
name | id | date | points | REFERRAL
---------------------------------------------
daniel | 123456 | 01-01-01 | 50 | 321321
jack | 321321 | 01-01-01 | 30 | 555555
Note that daniel was refered by jack
1) With the ID of one person show all client referrals and referrals from them to a third reference level
First i think on something like this, just for a reference level of 2:
SELECT C1.name
FROM Clients C1, Clients C2
WHERE C1.REFERRAL= 21000301
AND C1.id= C2.REFERRAL
This obviously not work for the AND.
Then I try UNION:
SELECT C1.id, C1.REFERRAL
FROM Clients C1
WHERE C1.REFERRAL= 21000301
UNION
SELECT C2.id, C2.REFERRAL
FROM Clients C2
WHERE C2.REFERRAL= C1.ID
But can't access C1 on the second SELECT
So, my question is there is a way to make conditions with the UNION.
If not, how can i solve this?
2) Show the name and the total number of referrals to the client with more direct and indirect referrals.
On this one I'm completely lost :D, May be it is recursive? Is there something like that in SQL?
Thanks in advance, and sorry my poor English.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
我使用:
c1.referral = 21000301
,因为我需要一个人的推荐,并提供 ID,并且该 ID 就在桌子上,仅供测试之用。我尝试这样做:
但它只显示一个名字 8 次。
关于数据库我真的不知道,我的大学正在做托管,如果有帮助的话我正在使用 Oracle SQL Developer。无论如何,我想任何代码都会对我有帮助。
谢谢!
Im using:
c1.referral = 21000301
because i need the referrals of one person given the ID and that ID is on the table, just for testing.I try this:
But it only shows one name 8 times.
And about the database i really don't know, my college is doing the hosting, I'm using Oracle SQL Developer if helps. Anyway I guess any code will help me.
Thanks!
我明白了 :D
I figure it out :D
您的第一个示例不起作用的原因是
c1.referral = 21000301
:没有这样的行。如果忽略这一点,查询将起作用:或者更清楚地重写为两个级别的联接:
您只需要对任意级别的引用进行递归查询。每个数据库的递归查询语法都不同,因此您必须告诉我们您正在使用哪个数据库。
The reason your first example doens't work is the
c1.referral = 21000301
: there is no row like that. If you omit that, the query works:Or rewritten more clearly as a join for two levels:
You'd only need a recursive query for an arbitrary level of referrals. Recursive query syntax differs per database, so you'd have to tell us which database you are using.