当前位置: 动力学知识库 > 问答 > 编程问答 >

group by - Grouping results in MySQL

问题描述:

My brain is turning into tapioca trying to figure this one out. I have a table of subordinates and supervisors. Every employee has a number of course. Here's an example.

I have three fields:

employee_id, name, supervisor_id

Fred Wilkie is a supervisor and his record is ....

employee_id: 1

name: Fred Wilkie

supervisor_id: NULL

Ted Wilkie is a lowly worker and Fred is his boss. His entry looks like this....

employee_id: 2

name: Ted Wilkie

supervisor_id: 1

What I would like my query to look like is employee_id, name and supervisor_id but the supervisor_id should be equal to the employee_id if the supervisor_id is NULL.

This kinda works... ( I think I just need to refine it somehow)

select employee_id, name, supervisor_id case when supervisor_id is NULL then employee_id else supervisor_id end from employees order by supervisor_id;

Problem with this is that it orders all of the records first where the employee_id is equal to the supervisor_id and then it just spits out the subordinates that are left....

employee_id, name, supervisor_id

1, Fred Wilkie, 1

4, Gail Winston, 4

2, Ted Wilkie, 1

3, Lisa Wilkie, 1

5, Russ Cablas, 4

6, Ben Reynolds, 4

etc, etc, etc...

what I would like is this......

employee_id, name, supervisor_id

1, Fred Wilkie, 1

2, Ted Wilkie, 1

3, Lisa Wilkie, 1

4, Gail Winston, 4

5, Russ Cablas, 4

6, Ben Reynolds, 4

etc, etc, etc...

In the example above I have the the first supervisor (Fred) listed (employee_id = supervisor_id) and then all of his subordinates. After that is Gail, and all of her subordinates, etc. I think my group-fu is weak.

We run a large company (250 employees) so we'd like a way to keep this in the MySQL logic. Does anyone have an idea?

Many thanks!

Janie

网友答案:

The easiest solution on this is to use COALESCE

SELECT  employee_ID,
        name,
        COALESCE(supervisor_id, employee_id) AS supervisor_id
FROM    employees
ORDER BY supervisor_id, employee_ID

SQLFiddle Demo

additional query (if you want to get their supervisor name instead of id)

SELECT  a.employee_ID,
        a.name,
        COALESCE(b.name, a.name) AS Supervisor_Name
FROM    employees a
        LEFT JOIN employees b
          ON a.supervisor_ID = b.employee_ID
ORDER BY COALESCE(b.supervisor_id, a.employee_id), employee_ID

SQLFiddle Demo

网友答案:
select employee_id, name, supervisor_id case when supervisor_id is NULL then employee_id else supervisor_id end from employees order by supervisor_id ASC;

should do the trick...

网友答案:

i think you are missing the sequence to get records you are using order by but not ponting the sequence ASC or DESC

select employee_id, name, 
supervisor_id case when supervisor_id is NULL 
then employee_id else supervisor_id end 
from employees 
order by supervisor_id
ASC;

hope this works

分享给朋友:
您可能感兴趣的文章:
随机阅读: