按顺序选择Mysql条件组的问题

发布于 2024-12-05 02:50:40 字数 439 浏览 0 评论 0原文

每项服务都有不同的 KPI。这些 KPI 按照表 checklist_has_kpi 中的字段顺序排序。我想要一份按分配给 KPI 的顺序排序的单独服务的列表。

我有这个查询：

SELECT s.serviceid, s.servicenameit, s.servicenameen 
FROM services s, kpi k, checklist_has_kpi chk 
WHERE s.serviceid=k.serviceid AND k.kpiid=chk.kpiid AND k.inreport='yes' AND chk.checklistid=61 
GROUP BY s.serviceid ORDER BY chk.order ASC

但它没有产生我期望的结果，而且我不明白我编写的查询出了什么问题。帮我一下吗？如果有什么不清楚的地方就问吧！谢谢

原文

Each service has different KPIs. These KPIs are ordered with the field order in the table checklist_has_kpi.
I want a list of separate services ordered by the order assigned to the KPI.

I have this query:

SELECT s.serviceid, s.servicenameit, s.servicenameen 
FROM services s, kpi k, checklist_has_kpi chk 
WHERE s.serviceid=k.serviceid AND k.kpiid=chk.kpiid AND k.inreport='yes' AND chk.checklistid=61 
GROUP BY s.serviceid ORDER BY chk.order ASC

But it does not produce the result that I expect and I do not understand what's wrong in the query written by me.
Give me a hand?
If something is unclear just ask!
thanks

分享到QQ

分享到微博

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

发布评论

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

澜川若宁 2024-12-12 02:50:40

如果您添加

SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY';

然后您将看到为什么其他 RDBMS 不允许使用此语法：

SELECT 中的 3 个未聚合列，但 GROUP BY
ORDER BY 列中的一个不在 GROUP BY/SELECT 中且未聚合

如果您想 GROUP BY而不是 DISTINCT，那么您需要对 SELECT 中的所有列进行 GROUP BY

SELECT s.serviceid, s.servicenameit, s.servicenameen 
FROM services s, kpi k, checklist_has_kpi chk 
WHERE s.serviceid=k.serviceid AND k.kpiid=chk.kpiid AND k.inreport='yes' AND chk.checklistid=61 
GROUP BY s.serviceid, s.servicenameit, s.servicenameen

但是，无论使用 GROUP BY 或DISTINCT

那么完全忽略重复项又怎么样呢？

SELECT s.serviceid, s.servicenameit, s.servicenameen 
FROM services s, kpi k, checklist_has_kpi chk 
WHERE s.serviceid=k.serviceid AND k.kpiid=chk.kpiid AND k.inreport='yes' AND chk.checklistid=61 
ORDER BY chk.order ASC

或者按每 3x services 列的最早订单排序

SELECT s.serviceid, s.servicenameit, s.servicenameen 
FROM services s, kpi k, checklist_has_kpi chk 
WHERE s.serviceid=k.serviceid AND k.kpiid=chk.kpiid AND k.inreport='yes' AND chk.checklistid=61 
GROUP BY s.serviceid, s.servicenameit, s.servicenameen
ORDER BY MIN(chk.order)

If you add

SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY';

Then you'll see the why other RDBMS won't allow this syntax:

3 un-aggregated columns in SELECT but one in GROUP BY
ORDER BY column is not in GROUP BY/SELECT and not aggregated

If you want to GROUP BY rather then DISTINCT, then you need to GROUP BY all column in the SELECT

SELECT s.serviceid, s.servicenameit, s.servicenameen 
FROM services s, kpi k, checklist_has_kpi chk 
WHERE s.serviceid=k.serviceid AND k.kpiid=chk.kpiid AND k.inreport='yes' AND chk.checklistid=61 
GROUP BY s.serviceid, s.servicenameit, s.servicenameen

But then you have no chk.order to order by, whether using GROUP BY or DISTINCT

So what about this, ignoring duplicates completely?

SELECT s.serviceid, s.servicenameit, s.servicenameen 
FROM services s, kpi k, checklist_has_kpi chk 
WHERE s.serviceid=k.serviceid AND k.kpiid=chk.kpiid AND k.inreport='yes' AND chk.checklistid=61 
ORDER BY chk.order ASC

Or this to ORDER BY the earliest order per 3x services columns

SELECT s.serviceid, s.servicenameit, s.servicenameen 
FROM services s, kpi k, checklist_has_kpi chk 
WHERE s.serviceid=k.serviceid AND k.kpiid=chk.kpiid AND k.inreport='yes' AND chk.checklistid=61 
GROUP BY s.serviceid, s.servicenameit, s.servicenameen
ORDER BY MIN(chk.order)

回复收藏 0 原文

~没有更多了~