create table user (
id int primary key,
name varchar(11) ,
depid int
);
create table dept(
id int primary key,
name varchar(11)
);
insert into user(id,name,depid) values(1,'zhangsan',1);
insert into user(id,name,depid) values(2,'lisi',1);
insert into user(id,name,depid) values(3,'wangwu',2);
insert into user(id,name,depid) values(4,'zhaosi',2);
insert into user(id,name,depid) values(6,'yy3',1);
insert into user(id,name,depid) values(7,'yy1',1);
insert into user(id,name,depid) values(8,'yy2',4);
insert into user(id,name,depid) values(9,'yy4',3);
insert into dept(id,name) values(1,'开发');
insert into dept(id,name) values(2,'测试');
insert into dept(id,name) values(3,'销售');
insert into dept(id,name) values(4,'前台');
insert into dept(id,name) values(5,'工程');
select u.id,u.name,u.depid,d.name "部门名称" from user u,dept d where u.depid=d.id group by d.id desc;
select u.id,u.name,u.depid,d.name "部门名称" from user u,dept d where u.depid=d.id group by d.id desc,u.id desc ;
group by后面必须有唯一性字段。