每组多桌最大n

发布于 2024-11-17 03:21:32 字数 511 浏览 0 评论 0原文

我知道有很多同样的问题，但我很难让它在多个表连接上工作。我有 3 个表

userinfo

user_id | firstname | lastname

subnumbers

subnumber | fkuserid

transaction

transid | fksubnumber | expires | transdate

这是我正在工作的部分

SELECT *
FROM (SELECT *
FROM subtransactions
WHERE Expires < now()
ORDER BY NewSubTrans DESC) AS s
GROUP BY FKSubNum

我想做的是选择订阅已过期的所有用户。为了确定用户是否过期，我从交易表中提取最后一笔交易及其子编号，fk 字段是到其他表的链接。

原文

i know there is heaps of this same question but im having trouble making it work on multiple table joins. i have 3 tables

userinfo

user_id | firstname | lastname

subnumbers

subnumber | fkuserid

transaction

transid | fksubnumber | expires | transdate

heres the part i have working

SELECT *
FROM (SELECT *
FROM subtransactions
WHERE Expires < now()
ORDER BY NewSubTrans DESC) AS s
GROUP BY FKSubNum

What i am trying to do is select all users with a subscription that is expired. to determine if a user is expired i pull the last transaction from the transaction table with thier sub number, the fk fields are the links to the other tables.

分享到QQ

分享到微博

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

发布评论

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

↘人皮目录ツ 2024-11-24 03:21:32

从你的描述来看，我认为你根本不需要任何团体。尝试仅使用这两个连接：

SELECT user_id, firstname, lastname
FROM
 userinfo
  JOIN subnumbers ON userinfo.user_id = subnumbers.fkuserid
  JOIN transaction ON subnumbers.subnumber = transaction.fksubnumber
WHERE transaction.expires < NOW()

编辑如果它按照下面的注释中的建议返回多行，请在选择列表中使用DISTINCT：

SELECT DISTINCT user_id, firstname, lastname
  -- etc...

From your description, I don't think you need any group at all. Try it with just these two joins:

SELECT user_id, firstname, lastname
FROM
 userinfo
  JOIN subnumbers ON userinfo.user_id = subnumbers.fkuserid
  JOIN transaction ON subnumbers.subnumber = transaction.fksubnumber
WHERE transaction.expires < NOW()

EDIT If it returns multiple rows as suggested in comments below, use DISTINCT in the select list:

SELECT DISTINCT user_id, firstname, lastname
  -- etc...

回复收藏 0 原文

ˇ宁静的妩媚 2024-11-24 03:21:32

这将获取所有 user_ids

select distinct(user_id) from userinfo
join subnumbers on userinfo.user_id = subnumbers.fkuserid
join transaction on transaction.fksubnumber = subnumbers.subnumber
where transaction.expires < NOW()

来获取完整的用户数据

select * from user where user_id in
(
select distinct(user_id) from userinfo
join subnumbers on userinfo.user_id = subnumbers.fkuserid
join transaction on transaction.fksubnumber = subnumbers.subnumber
where transaction.expires < NOW()
)

This will get all user_ids

select distinct(user_id) from userinfo
join subnumbers on userinfo.user_id = subnumbers.fkuserid
join transaction on transaction.fksubnumber = subnumbers.subnumber
where transaction.expires < NOW()

To get full user data

select * from user where user_id in
(
select distinct(user_id) from userinfo
join subnumbers on userinfo.user_id = subnumbers.fkuserid
join transaction on transaction.fksubnumber = subnumbers.subnumber
where transaction.expires < NOW()
)

回复收藏 0 原文

~没有更多了~