如何对特定频率的表行进行排序 [英] How do I sort a table row in a particular frequence

问题描述

我的表格包含以下列：

ex_id：PRIMARY KEY INT

名称：VARCHAR

编辑：CHAR



'编辑'列只能有'Y'和'N'作为值。我想以这样的方式订购行，即每隔2'N'，显示1'Y'。



例如

EX_ID | NAME |编辑

-----------------------------------

1 | | N

2 |天空| N

3 |所以| Y

4 |蓝色| N

5 |在| N

6 | Malibu | Y



我试图通过实现一个虚拟列来模仿这个，这个列可以扭转周围的ID以达到预期的结果但是没有多大用处。非常欢迎你的任何答案，要求或建议，谢谢。



我尝试过：



SELECT ex_id，name，edit，

CASE WHEN edit ='Y'AND ex_id<>（SELECT MAX（ex_id）FROM exercise）那么ex_id-3

WHEN edit ='N'和ex_id<>（SELECT MIN（ex_id）FROM exercise）那么ex_id + 3

ELSE ex_id END

来自锻炼

ORDER BY 4 ASC

My table has the following columns:
ex_id : PRIMARY KEY INT
name : VARCHAR
edit: CHAR

'Edit' column can only have 'Y' and 'N' as values. I would like to order the row in such a way that after every 2 'N', 1 'Y' is shown.

For example
EX_ID |NAME |EDIT
-----------------------------------
1 | The |N
2 | Sky |N
3 | So |Y
4 | Blue |N
5 | In |N
6 | Malibu |Y

I tried to mimic this by implementing a virtual column which would twist the ids around to reach the desired result but without much avail. Any of your answers, requests or suggestions would be greatly welcomed, thanks.

What I have tried:

SELECT ex_id,name,edit,
CASE WHEN edit='Y' AND ex_id<>(SELECT MAX(ex_id) FROM exercise) THEN ex_id-3
WHEN edit='N' AND ex_id<>(SELECT MIN(ex_id) FROM exercise) THEN ex_id+3
ELSE ex_id END
FROM exercise
ORDER BY 4 ASC

推荐答案

SELECT Ex_Id,
       Name,
       case when (EX_ID%3)<>0 then 'N' else 'Y' end Edit 
 from Exercise; 

EX_ID |NAME |EDIT
-----------------------------------
1 | The |N
2 | Sky |N
3 | So |Y
4 | Blue |N
5 | In |N
6 | Malibu |Y

这篇关于如何对特定频率的表行进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！