mysql GROUP BY 如果此行 table.field > 0

Question

我如何按entry_category分组 if entry_category > 0

| entry_id | entry_title | entry_category |
-------------------------------
| 1        | xxx         | 6       |
| 2        | xxx         | 4       |
| 3        | xxx         | 6       |
| 4        | xxx         | 0       |
| 5        | xxx         | 0       |
| 6        | xxx         | 6       |
| 7        | xxx         | 4       |
| 8        | xxx         | 7       |

我希望设置条目类别，不显示重复:

| 1        | xxx         | 6       |
| 2        | xxx         | 4       |
| 4        | xxx         | 0       |
| 5        | xxx         | 0       |
| 8        | xxx         | 7       |

Answer 1

这是一种方法，但可能还有更好的方法。我将表名称设置为 test

select 
entry_id,
entry_title,
entry_category
from
(
  (
    select
    t1.entry_id,
    t1.entry_title,
    t1.entry_category
    from test t1
    left join test t2
    on t1.entry_category = t2.entry_category
    AND t1.entry_id < t2.entry_id
    where t1.entry_category <> 0 
    group by t1.entry_category
  )
  union all
  (
    select entry_id,entry_title,entry_category from test 
    where entry_category = 0
  )
)x
order by entry_id

<强> DEMO