对特定列进行排序/分组，通过SQL查询重复其所有值

I want query should return all rows of table according to sorting of dept not grouping. I've tried GROUP_CONCAT() function of mysql, i also want same kind of result but it should return as rows, not only comma separated values..

我想查询应该根据dept的分类返回表的所有行而不是分组。我已经尝试过mysql的GROUP_CONCAT()函数,我也想要同样的结果,但它应该返回为行,而不仅仅是逗号分隔值。

Table Data

       
Emp_Id  Emp_Name    Dept
1       aa          111
2       bb          222
3       cc          333
4       dd          222
5       ee          111
6       ff          333
7       gg          222
8       hh          111
9       ii          333
10      jj          222

Expected Output Sort Result

预期的输出排序结果

       
Emp_Id  Emp_Name    Dept
1           aa          111
2           bb          222
3           cc          333
5           ee          111
4           dd          222
6           ff          333
8           hh          111
7           gg          222
9           ii          333
10          jj          222

Query result should fetch all rows of table, but sorting should be according to dept column, that too without grouping.

查询结果应该获取表的所有行,但是排序应该根据dept列,也没有分组。

Please find attached image for specification -

请查看附图以了解规格 -

Input Data and Expected Sorting Result

输入数据和预期排序结果

Thanks in Advance..

提前致谢..

1 个解决方案

#1

SQL Fiddle Demo

SQL小提琴演示

SELECT *
FROM 
    (
    SELECT `Emp_Id`, `Emp_Name`, `Dept`,
           CASE `Dept`
              WHEN @curDept THEN @curRow := @curRow + 1 
              ELSE @curRow := 1 AND @curDept := Dept 
           END as RANK
    FROM Table1
    CROSS JOIN (SELECT @curRow := 0, @curDept := '') r
    ORDER BY `Dept`, `Emp_Name`
    ) t
ORDER BY RANK, `Dept`

OUTPUT

| Emp_Id | Emp_Name | Dept | RANK |
|--------|----------|------|------|
|      1 |       aa |  111 |    1 |
|      2 |       bb |  222 |    1 |
|      3 |       cc |  333 |    1 |
|--------|----------|------|------|
|      5 |       ee |  111 |    2 |
|      4 |       dd |  222 |    2 |
|      6 |       ff |  333 |    2 |
|--------|----------|------|------|
|      8 |       hh |  111 |    3 |
|      7 |       gg |  222 |    3 |
|      9 |       ii |  333 |    3 |
|--------|----------|------|------|
|     10 |       jj |  222 |    4 |