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
1
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 |
#1
1
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 |