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

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

解决方案

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)