Making query to return records in specific format

问题: 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,

  1. First records of type value1, then value2, then value3 and so on.. (1 record of each type)
  2. 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 
  • 发表于 2019-03-05 00:56
  • 阅读 ( 161 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除