选择等于查询中最大值的行

发布于 2025-01-12 00:33:41 字数 1352 浏览 0 评论 0 原文

我想知道谁在我拥有的应用程序中拥有最多的朋友（交易），这意味着他可以得到报酬，也可以自己向许多其他用户付费。

我无法进行查询来显示仅那些拥有最大好友数量的人（可以是 1 个或多个，并且可以更改，因此我不能使用限制）。

;with relationships as 
(
    select
      paid as 'auser',
      Member_No as 'afriend'
    from Payments$
    union all
    select
      member_no as 'auser',
      paid as 'afriend'
    from Payments$
),
DistinctRelationships AS (
    SELECT DISTINCT *
    FROM relationships
)
select
  afriend,
  count(*) cnt
from DistinctRelationShips
GROUP BY
  afriend
order by
  count(*) desc

我只是想不通，我尝试过 count, max(count), where = max，但没有任何效果。

这是一个两列表——“Member_No”和“Paid”——会员付钱，而付钱的人就是收到钱的人。

Member_No	Paid
14	18
17	1
12	20
12	11
20	8
6	3
2	4
9	20
8	10
5	20
14	16
5	2
12	1
14	10

它来自Excel，但我将其加载到sql-server中。
这只是一个示例，还有 1000 行

原文

I want to know who has the most friends from the app I own(transactions), which means it can be either he got paid, or paid himself to many other users.

I can't make the query to show me only those who have the max friends number (it can be 1 or many, and it can be changed so I can't use limit).

;with relationships as 
(
    select
      paid as 'auser',
      Member_No as 'afriend'
    from Payments$
    union all
    select
      member_no as 'auser',
      paid as 'afriend'
    from Payments$
),
DistinctRelationships AS (
    SELECT DISTINCT *
    FROM relationships
)
select
  afriend,
  count(*) cnt
from DistinctRelationShips
GROUP BY
  afriend
order by
  count(*) desc

I just can't figure it out, I've tried count, max(count), where = max, nothing worked.

It's a two columns table - "Member_No" and "Paid" - member pays the money, and the paid is the one who got the money.

Member_No	Paid
14	18
17	1
12	20
12	11
20	8
6	3
2	4
9	20
8	10
5	20
14	16
5	2
12	1
14	10

It's from Excel, but I loaded it into sql-server.
It's just a sample, there are 1000 more rows

分享到QQ

分享到微博

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

发布评论

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

这个俗人 2025-01-19 00:33:41

看来你把这件事过于复杂化了。无需自行加入。

只需对每一行进行逆透视，即可获得关系的两侧，然后将其按一侧分组并计算另一侧的不同

SELECT
-- for just the first then SELECT TOP (1)
-- for all that tie for the top place use SELECT TOP (1) WITH TIES
  v.Id,
  Relationships = COUNT(DISTINCT v.Other),
  TotalTransactions = COUNT(*)
FROM Payments$ p
CROSS APPLY (VALUES
    (p.Member_No, p.Paid),
    (p.Paid, p.Member_No)
) v(Id, Other)
GROUP BY
  v.Id
ORDER BY
  COUNT(DISTINCT v.Other) DESC;

db<>fiddle

It seems like you are massively over-complicating this. There is no need for self-joining.

Just unpivot each row so you have both sides of the relationship, then group it up by one side and count distinct of the other side

SELECT
-- for just the first then SELECT TOP (1)
-- for all that tie for the top place use SELECT TOP (1) WITH TIES
  v.Id,
  Relationships = COUNT(DISTINCT v.Other),
  TotalTransactions = COUNT(*)
FROM Payments$ p
CROSS APPLY (VALUES
    (p.Member_No, p.Paid),
    (p.Paid, p.Member_No)
) v(Id, Other)
GROUP BY
  v.Id
ORDER BY
  COUNT(DISTINCT v.Other) DESC;

db<>fiddle

回复收藏 0 原文

~没有更多了~