问题:
Let's say I have following table:
id | name | type
1 | test1 | value1
2 | test2 | value1
3 | test3 | value1
4 | test4 | value2
5 | test5 | v...
可以将文章内容翻译成中文,广告屏蔽插件会导致该功能失效:
问题:
Let's say I have following table:
id | name | type
1 | test1 | value1
2 | test2 | value1
3 | test3 | value1
4 | test4 | value2
5 | test5 | value2
6 | test6 | value3
How I can write an query to return records, but first needs to be with,
- First records of type value1, then value2, then value3 and so on.. (1 record of each type)
- Again records of type value1, value 2 and so on for remaining in same above manner...
回答1:
If you are able to use window functions then use ROW_NUMBER() OVER (PARTITION BY type)
. It'll assign row numbers for each type:
TableName.find_by_sql(
select *, row_number() over (partition by type order by (select null)) rn
from table_name
order by rn, type
)
Output:
| id | name | type | rn |
|----|-------|--------|----|
| 1 | test1 | value1 | 1 |
| 4 | test4 | value2 | 1 |
| 6 | test6 | value3 | 1 |
| 2 | test2 | value1 | 2 |
| 5 | test5 | value2 | 2 |
| 3 | test3 | value1 | 3 |
回答2:
If i understood correctly you need query like following.
SELECT t.name,
t1.type
FROM (SELECT DISTINCT name
FROM yourtable) t
CROSS JOIN (SELECT DISTINCT type
FROM yourtable)t1