mysql 选择唯一记录

发布于 2024-08-14 13:51:50 字数 432 浏览 4 评论 0原文

我有以下查询：

select * from members,subscriptions
where members.MemberID = subscriptions.MemberID
and subscriptions.Year = 2009
and members.ASSCID = 15
and subscriptions.Untildate between '$2009-01-01' and '2009-12-31'
order by members.Memberlastname

会员要么支付一次年度订阅（年度订阅），要么支付两次，一次在一月，一次在六月（六个月订阅）。我想要的是拉至少付费过一次的会员。

上述声明将给一些会员带来两次（一月和六月都付款的会员）。

可以拉出至少付费一次的会员（无论他们支付年度订阅还是六个月订阅）。避免重复。

原文

I've got the following query:

select * from members,subscriptions
where members.MemberID = subscriptions.MemberID
and subscriptions.Year = 2009
and members.ASSCID = 15
and subscriptions.Untildate between '$2009-01-01' and '2009-12-31'
order by members.Memberlastname

Members pay either their annual subscription once (annual subscription) or they pay 2 times, one in january and one in june (six month subscriptions). What i want is to pull the members who paid at least once.

The statement above will bring some members twice (those who paid both on january and june).

Is is possible to pull the members who paid at least once (no matter if they paid an annual or a six month subscription). Avoiding duplicates.

分享到QQ

分享到微博

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

发布评论

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

空城旧梦 2024-08-21 13:51:50

使用 SELECT DISTINCT，这将仅获取您选择的列的唯一值。

回复收藏 0 原文

天冷不及心凉 2024-08-21 13:51:50

编辑后的答案

您可以对订阅执行“存在”操作，以查找在给定年份中至少支付过一次的会员：

select * from members
where  members.ASSCID = 15 and   
exists (select 1 from subscriptions  
where members.MemberID = subscriptions.MemberID   
and subscriptions.Year = 2009   

) 
order by members.Memberlastname

Edited Answer

You can do an exists on subscriptions to find the members who have paid at least once for a given year:

select * from members
where  members.ASSCID = 15 and   
exists (select 1 from subscriptions  
where members.MemberID = subscriptions.MemberID   
and subscriptions.Year = 2009   

) 
order by members.Memberlastname

回复收藏 0 原文

暗地喜欢 2024-08-21 13:51:50

成员表中每个成员至少有一个订阅的所有记录：

select members.*
from members join
(
   select
   members.MemberID, count(*)
   from members join subscriptions on (members.MemberID = subscriptions.MemberID)
   where subscriptions.Year = 2009
   and subscriptions.Untildate between '2009-01-01' and '2009-12-31'
   group by members.MemberID 
   having count(*) >= 1
)
v on ( members.MemberID = v.MemberID)
where members.ASSCID = 15

All records from members table where each member has at least one subscription:

select members.*
from members join
(
   select
   members.MemberID, count(*)
   from members join subscriptions on (members.MemberID = subscriptions.MemberID)
   where subscriptions.Year = 2009
   and subscriptions.Untildate between '2009-01-01' and '2009-12-31'
   group by members.MemberID 
   having count(*) >= 1
)
v on ( members.MemberID = v.MemberID)
where members.ASSCID = 15

回复收藏 0 原文

~没有更多了~