如何使用 GROUP BY 按 FIELD 排序 [英] How to order by FIELD with GROUP BY

问题描述

我有一个这样的查询:

SELECT
  t.type,
  SUM( t.external_account )
FROM
  u_contracts c,
  u_data u,
  u_transactions t
WHERE
  c.user_id = u.id
  AND t.contract_id = c.id
  AND t.nulled =0
  AND DATE (c.init_date) < DATE (u.dead)
  AND u.dead IS NOT NULL
  AND t.type != 'repay'
GROUP BY
  t.type;

现在这给了我想要的结果，但现在我想向它添加 ORDER BY，但是通过特定的字段名称，如:

Now this gives me the desired result, but now I want to add ORDER BY to it, but by specific field name like:

ORDER BY FIELD( t.type, 'type1', 'type2', ... )

但我无法将其插入查询中.当我这样尝试时:

But I can't insert that into the query. When I try like this:

SELECT
  t.type,
  SUM( t.external_account )
FROM
  u_contracts c,
  u_data u,
  u_transactions t
WHERE
  c.user_id = u.id
  AND t.contract_id = c.id
  AND t.nulled =0
  AND DATE (c.init_date) < DATE (u.dead)
  AND u.dead IS NOT NULL
  AND t.type != 'repay'
ORDER BY
  FIELD( t.type, 'initial', 'commision', 'overpay', 'penalty', 'penalty2' )
GROUP BY
  t.type;

它给了我的 sql 错误:

It gives my sql error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY t.type' at line 17

这样做的正确方法是什么?

What is the proper way in doing this?

推荐答案

group by 和 order by 的顺序错误；

...
ORDER BY
  FIELD( t.type, 'initial', 'commision', 'overpay', 'penalty', 'penalty2' )
GROUP BY
  t.type;

应该是:

...
GROUP BY
  t.type
ORDER BY
  FIELD( t.type, 'initial', 'commision', 'overpay', 'penalty', 'penalty2' );

这篇关于如何使用 GROUP BY 按 FIELD 排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！