SQL多重排序和分组 [英] SQL Multiple sorting and grouping

问题描述

编辑：我正在使用DQL

我正在寻找一个SQL查询的帮助。

我的表有一个电影列表，每个都有一个标题，seriesName和seriesNumber。可以对它们进行排序，以便标题列出AZ，但是当系列出现时，该系列被分组在一起，其中seriesName按字母顺序排列，就像在movieTitle列中一样，并且由serialNumber排序的一系列中的条目。 / p>

错误的解释，但基本上我想要的是：

  MovieTitle SeriesName系列号
刀片运行符NULL NULL 
区域9 NULL NULL 
 Hot Fuzz NULL NULL 
我是Legend NULL NULL 
戒指奖金1 
双塔，指环王2 
国王之王戒指3 
丢失翻译NULL NULL 
矩阵，矩阵1 
矩阵重新加载，矩阵2 
矩阵旋转离子，矩阵3 
安魂曲的空值NULL 
这是Spinal Tap NULL NULL 
 Zodiac NULL NULL 
  

提前感谢

解决方案

  SELECT * FROM x 
 ORDER BY CASE 
 WHEN SeriesName不为NULL THEN SeriesName ELSE MovieTitle END 
，SeriesNumber 
  

您可能需要这样做

  SELECT * FROM（
 SELECT *，CASE WHEN SeriesName不为空THEN SeriesName ELSE MovieTitle END SortOrder FROM x 
）
 ORDER BY SortOrder，SeriesNumber 
  

EDIT: I'm using DQL

I'm looking for help with an SQL query.

My table has a list of movies, each with a title, seriesName and seriesNumber. Is it possible to order them so the Titles are listed A-Z, but when a series occurs, that series is grouped together, with the seriesName being placed alphabetically as if it were inthe movieTitle column, and the entries in a series ordered by seriesNumber.

Bad explanation, but basically what I want is this:

MovieTitle                  SeriesName          SeriesNumber
Blade Runner                NULL                NULL
District 9                  NULL                NULL
Hot Fuzz                    NULL                NULL
I am Legend                 NULL                NULL
Fellowship of the Ring      Lord of the Rings   1
Two Towers, The             Lord of the Rings   2
Return of the King          Lord of the Rings   3
Lost in Translation         NULL                NULL
Matrix, The                 Matrix              1
Matrix Reloaded, The        Matrix              2
Matrix Revolutions, The     Matrix              3
Requiem for a Dream         NULL                NULL
This is Spinal Tap          NULL                NULL
Zodiac                      NULL                NULL

Thanks in advance.

解决方案

SELECT * FROM x
ORDER BY CASE 
   WHEN SeriesName is NOT NULL THEN SeriesName ELSE MovieTitle  END
   , SeriesNumber

You may have to do it this way

SELECT * FROM(
  SELECT *, CASE WHEN SeriesName is NOT NULL THEN SeriesName ELSE MovieTitle END SortOrder  FROM x
)
ORDER BY SortOrder,SeriesNumber

这篇关于SQL多重排序和分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！