php mysql 连接多个表

发布于 2024-12-29 15:26:04 字数 1589 浏览 0 评论 0原文

我有两个表

用户

userID username     email
  1       abc    [email protected]
  2       def    [email protected]
  3       ghi    [email protected]

推荐

 refID userID      invEmail
   1      1      [email protected]
   2      1      [email protected]
   3      1      [email protected]

所以我的计划是给一个受邀用户5点同时一个邀请者10点，所以基本上userID 1获得30 同时 userID 2 获得了 5。我可以在 PHP 中做的一点，但我面临的困难是何时识别 invEmail。如果它有效的话，我不介意分成多个查询。

我如何在sql中显示这个？

我尝试过类似的方法

SELECT *, count(r.userID) FROM user u, referral r WHERE u.userID = r.userID OR u.email = r.invEmail GROUP BY r.userID

它返回了错误的值。

我希望它返回什么，有多少邀请者和被邀请者（根据邀请者邀请注册的匹配电子邮件）

我应该怎么做？

谢谢。

编辑：我忘了添加一些问题，如果我希望邀请者只有在被邀请者注册时才能获得 10 积分怎么办？我的意思是，只有当 u.email 中存在 invEmail 时，才只有 userID 收到 10 分。为我的错误感到抱歉。

原文

I have two table

User

userID username     email
  1       abc    [email protected]
  2       def    [email protected]
  3       ghi    [email protected]

Referral

 refID userID      invEmail
   1      1      [email protected]
   2      1      [email protected]
   3      1      [email protected]

So what i plan is to give a invited user 5 point meanwhile a inviter 10 point, so basically userID 1 gained 30 meanwhile userID 2 gained 5. The point i can do in PHP but one part i faced difficulty is when a invEmail to be identified. I don't mind separating into multiple queries if its will work.

How do i show this in sql?

I tried something like

SELECT *, count(r.userID) FROM user u, referral r WHERE u.userID = r.userID OR u.email = r.invEmail GROUP BY r.userID

It returned wrong value.

What i would like it to return, how much count is there inviter and invitee(matched email who has registered based on inviter invitation)

How should i do it?

Thank you.

Edit: i forgot to add something into question, what if i wanted the inviter to receive 10 points only if invitee registered? what i meant is that, only if invEmail exists in u.email then only userID received 10 point. Sorry for my mistake.

分享到QQ

分享到微博

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

发布评论

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

奢华的一滴泪 2025-01-05 15:26:04

您也许可以这样做：

select points.userId, points.username, points.email, sum(points.points)
FROM
(select u.*, count(*)*10 as points
  from user u
    join referral on u.userID = r.userID
    join user verify_user on r.invEmail = verify_user.email
  group by u.userID, u.username, u.email
UNION
select u.*, count(*)*5 as points
  from user u
    join referral on u.email = r.invEmail and u.userID != r.userID
  group by u.userID, u.username, u.email
) as points
group by points.userId, points.username, points.email

我认为您需要两个单独的选择来获取每种注册类型的积分，并结合联合声明。

You might be able to do something like this:

select points.userId, points.username, points.email, sum(points.points)
FROM
(select u.*, count(*)*10 as points
  from user u
    join referral on u.userID = r.userID
    join user verify_user on r.invEmail = verify_user.email
  group by u.userID, u.username, u.email
UNION
select u.*, count(*)*5 as points
  from user u
    join referral on u.email = r.invEmail and u.userID != r.userID
  group by u.userID, u.username, u.email
) as points
group by points.userId, points.username, points.email

I think you need two separate selects to get the points for each type of registration, combined with a union statement.

回复收藏 0 原文

~没有更多了~