按顺序选择Mysql条件组的问题 [英] Problem with Select Mysql Condition Group By Order

问题描述

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

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.

我有这个查询:

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

推荐答案

如果添加

SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY';

然后，您将看到为什么其他RDBMS不允许使用此语法的原因:

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

• SELECT中3个未聚合的列，而GROUP BY中1个
• ORDER BY列不在GROUP BY/SELECT中，并且未聚合

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

如果要分组而不是DISTINCT，则需要对SELECT中的所有列进行分组

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

但是，无论使用GROUP BY还是DISTINCT，您都没有chk.order可以进行排序

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

或者将此命令按3x services列的最早顺序排序

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)

这篇关于按顺序选择Mysql条件组的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！