与其他数据一起获取最大值

发布于 2024-11-09 17:23:56 字数 834 浏览 0 评论 0原文

我必须来问这个，因为我已经生气、哭泣等等，因为这真的要了我的命。我讨厌 SQL。但这是必须要做的。网站使用 SQL。

我有这些表（缩短为必要的而不是真正的 SQL）：

CREATE TABLE deliverer(d_id INT PRIMARY KEY, name VARCHAR(80));
CREATE TABLE article(a_id INT PRIMATY KEY, brand VARCHAR(80));
CREATE TABLE delivers(d_id FOREIGN KEY(deliverer), a_id FOREIGN KEY(article));

对于每个品牌，我需要那些提供最多文章的交付者。经过仅仅两个小时的思考，我已经走到了这一步：

SELECT brand, MAX(cnt)
FROM
(SELECT COUNT(a.a_id) AS cnt, a.brand, d.d_id, d.name
    FROM derliverer d, delivers l, article a
    WHERE a.a_id = l.a_id AND l.d_id = d.d_id
    GROUP BY a.brand, d.d_id, d.name)
GROUP BY brand;

我真的、真的、真的不明白 SQL 是为什么它不允许我GROUP BY Brand 和 SELECT d.name、d.d_id。这确实没有任何意义。

在我跳出窗户之前请帮助我。

原文

I have to come and ask this because I'm already angry and crying and everything, because this really kills me. I hate SQL. But it has to be done. Website uses SQL.

I have these tables (shortened to the necessessary and not real SQL):

CREATE TABLE deliverer(d_id INT PRIMARY KEY, name VARCHAR(80));
CREATE TABLE article(a_id INT PRIMATY KEY, brand VARCHAR(80));
CREATE TABLE delivers(d_id FOREIGN KEY(deliverer), a_id FOREIGN KEY(article));

I need, for each brand those deliverers that deliver the most articles. I've come as far as this, after only two hours of mind twisting:

SELECT brand, MAX(cnt)
FROM
(SELECT COUNT(a.a_id) AS cnt, a.brand, d.d_id, d.name
    FROM derliverer d, delivers l, article a
    WHERE a.a_id = l.a_id AND l.d_id = d.d_id
    GROUP BY a.brand, d.d_id, d.name)
GROUP BY brand;

What I really, really, really don't get about SQL is why it does not let me GROUP BY brand and SELECT the d.name, d.d_id. It really makes no sense.

Please help me before I jump out of the window.

分享到QQ

分享到微博

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

发布评论

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

瞄了个咪的 2024-11-16 17:23:56

关于 SQL，我真的、真的、真的不明白的是为什么它不允许我按品牌进行分组并选择 d.name、d.d_id。实在是没有任何意义。

因为如果这样做，计数将针对每个不同的 d.name、d.d_id。这不是你想要的。

您肯定想阅读一些教程，了解分组依据的工作原理。

对于每个品牌，我需要那些交付最多文章的交付者。

在这种情况下，你需要类似的东西：

select brand_id, count(*) as top_deliverer, deliverer_id, deliverer.etc.
from some stuff
group by brand_id, deliverer_id, deliverere.etc
order by top_deliverer desc

What I really, really, really don't get about SQL is why it does not let me GROUP BY brand and SELECT the d.name, d.d_id. It really makes no sense.

Because if you do, the count will be for each distinct d.name, d.d_id. This is not what you want.

You definitely want to read some tutorials on how a group by works.

I need, for each brand those deliverers that deliver the most articles.

In this case you need something like:

select brand_id, count(*) as top_deliverer, deliverer_id, deliverer.etc.
from some stuff
group by brand_id, deliverer_id, deliverere.etc
order by top_deliverer desc

回复收藏 0 原文

~没有更多了~