select staff_name,w_sum from t_staff,t_wage where id=s_id group by depart,staff_name,w_sum having age>avg(age);
你需求是什么样子的嘛 贴个SQL出来 鬼才看得懂 给点数据
--把表结构以及代表性的记录贴出来 select staff_name,w_sum from t_staff,t_wage where id=s_id group by depart,staff_name,w_sum having age>avg(age)
INSERT INTO t_DEPARTMENT VALUES (1,'0000001','人力资源部','北京是海淀区101大道'); INSERT INTO t_DEPARTMENT VALUES (2,'0000011','市场拓展部','北京是海淀区102大道'); INSERT INTO t_DEPARTMENT VALUES (3,'0000401','销售部','北京是海淀区103大道'); INSERT INTO t_DEPARTMENT VALUES (4,'0000003','财务处','北京是海淀区101大道'); INSERT INTO t_DEPARTMENT VALUES (5,'0000004','后勤部','北京是海淀区101大道'); INSERT INTO t_DEPARTMENT VALUES (6,'0000005','党政工作部','北京是海淀区101大道'); insert into t_workrecord values(1,'00000001'); insert into t_workrecord values(2,'00000002'); insert into t_workrecord values(3,'00000003'); insert into t_workrecord values(4,'00000004'); insert into t_workrecord values(5,'00000005'); insert into t_workrecord values(6,'00000006'); insert into t_workrecord values(7,'00000007'); insert into t_workrecord values(8,'00000008'); insert into t_workrecord values(9,'00000009'); insert into t_staff values(2,'杨安平','0023452',1,1,23); insert into t_staff values(6,'杨小平','0123452',2,5,23); insert into t_staff values(7,'王安小','0133452',2,6,23); insert into t_staff values(9,'杨小','0133451',3,7,23); insert into t_staff values(8,'小二','0133453',3,8,23); insert into t_staff values(10,'无小二','0133454',1,9,23);insert into t_staff values(3,'王龙','0023452',1,2,25); insert into t_staff values(4,'廖凯','0033452',3,3,26); insert into t_staff values(1,'孙倩','0053452',2,4,29); insert into t_wage values(1,5000,2); insert into t_wage values(2,6000,3); insert into t_wage values(3,5050,4); insert into t_wage values(4,5010,9); insert into t_post values(1,'销售部经理','00345'); insert into t_post values(2,'人力资源总监','00335'); insert into t_post values(3,'集团业务经理','00385'); insert into t_post values(4,'集团常委会委员','00395'); insert into t_post values(5,'党委书记','00397'); insert into t_post values(6,'集团总经理','00398'); insert into t_post values(7,'集团业务经理','00399'); insert into t_sp values(2,2,1); insert into t_sp values(3,2,2); insert into t_sp values(5,3,1); insert into t_sp values(4,2,3); insert into t_dp values(3,1,1); insert into t_dp values(4,1,2); insert into t_dp values(1,1,3); insert into t_dp values(2,1,4); insert into t_dp values(5,2,1); insert into t_dp values(6,2,2); insert into t_dp values(7,3,3); insert into t_dp values(8,3,4);select staff_name,p_name from t_staff,t_post,t_sp where staff_name='杨安平'and id=s_id and t_post.p_id=t_sp.p_id ; select dep_name,p_name from t_department,t_post,t_dp where dep_name='人力资源部'and t_department.d_id=t_dp.d_id and t_post.p_id=t_dp.p_id ; select p_name,staff_name from t_post,t_staff,t_sp where p_name='销售部经理' and t_post.p_id=t_sp.p_id and id=s_id ; select staff_name,dep_name from t_staff,t_department where staff_name='杨安平'and depart=d_id; select dep_name,staff_name from t_department,t_staff where dep_name='人力资源部'and depart=d_id; select staff_name,dep_name from t_staff,t_department where depart=d_id; select staff_name from t_staff where staff_name like'%小%'; select staff_name from t_staff where staff_name like'小%'; select staff_name from t_staff where staff_name like'%小';select staff_name from t_staff where staff_name like'_小_'; select dep_name from t_department order by d_id ; select dep_name from t_department order by d_id desc; select staff_name,p_id from t_staff ,t_sp where id=s_id ; select staff_name,p_id from t_staff full outer join t_sp on id=s_id ; select dep_name,p_name from t_department d left outer join t_dp dp on d.d_id=dp.d_id right outer join t_post on dp.p_id=t_post.p_id; select dep_name,p_name from t_department d full outer join t_dp dp on d.d_id=dp.d_id full outer join t_post on dp.p_id=t_post.p_id; select dep_name,p_id from t_department d full outer join t_dp dp on d.d_id=dp.d_id; select count(*) from t_department; select staff_name,w_sum from t_staff,t_wage where id=s_id and w_sum in(select max(w_sum) from t_wage); select avg(age) from t_staff where id is not null;select staff_name,w_sum,age from t_staff,t_wage where id=s_id group by staff_name,w_sum,age having age<avg(age);
staff_name,w_sum
不是分组表达式
from t_staff,t_wage
where id=s_id
group by depart,staff_name,w_sum
having age>avg(age);
给点数据
--把表结构以及代表性的记录贴出来
select staff_name,w_sum
from t_staff,t_wage
where id=s_id
group by depart,staff_name,w_sum
having age>avg(age)
INSERT INTO t_DEPARTMENT VALUES (1,'0000001','人力资源部','北京是海淀区101大道');
INSERT INTO t_DEPARTMENT VALUES (2,'0000011','市场拓展部','北京是海淀区102大道');
INSERT INTO t_DEPARTMENT VALUES (3,'0000401','销售部','北京是海淀区103大道');
INSERT INTO t_DEPARTMENT VALUES (4,'0000003','财务处','北京是海淀区101大道');
INSERT INTO t_DEPARTMENT VALUES (5,'0000004','后勤部','北京是海淀区101大道');
INSERT INTO t_DEPARTMENT VALUES (6,'0000005','党政工作部','北京是海淀区101大道');
insert into t_workrecord values(1,'00000001');
insert into t_workrecord values(2,'00000002');
insert into t_workrecord values(3,'00000003');
insert into t_workrecord values(4,'00000004');
insert into t_workrecord values(5,'00000005');
insert into t_workrecord values(6,'00000006');
insert into t_workrecord values(7,'00000007');
insert into t_workrecord values(8,'00000008');
insert into t_workrecord values(9,'00000009');
insert into t_staff values(2,'杨安平','0023452',1,1,23);
insert into t_staff values(6,'杨小平','0123452',2,5,23);
insert into t_staff values(7,'王安小','0133452',2,6,23);
insert into t_staff values(9,'杨小','0133451',3,7,23);
insert into t_staff values(8,'小二','0133453',3,8,23);
insert into t_staff values(10,'无小二','0133454',1,9,23);insert into t_staff values(3,'王龙','0023452',1,2,25);
insert into t_staff values(4,'廖凯','0033452',3,3,26);
insert into t_staff values(1,'孙倩','0053452',2,4,29);
insert into t_wage values(1,5000,2);
insert into t_wage values(2,6000,3);
insert into t_wage values(3,5050,4);
insert into t_wage values(4,5010,9);
insert into t_post values(1,'销售部经理','00345');
insert into t_post values(2,'人力资源总监','00335');
insert into t_post values(3,'集团业务经理','00385');
insert into t_post values(4,'集团常委会委员','00395');
insert into t_post values(5,'党委书记','00397');
insert into t_post values(6,'集团总经理','00398');
insert into t_post values(7,'集团业务经理','00399');
insert into t_sp values(2,2,1);
insert into t_sp values(3,2,2);
insert into t_sp values(5,3,1);
insert into t_sp values(4,2,3);
insert into t_dp values(3,1,1);
insert into t_dp values(4,1,2);
insert into t_dp values(1,1,3);
insert into t_dp values(2,1,4);
insert into t_dp values(5,2,1);
insert into t_dp values(6,2,2);
insert into t_dp values(7,3,3);
insert into t_dp values(8,3,4);select staff_name,p_name
from t_staff,t_post,t_sp
where staff_name='杨安平'and id=s_id and t_post.p_id=t_sp.p_id ;
select dep_name,p_name
from t_department,t_post,t_dp
where dep_name='人力资源部'and t_department.d_id=t_dp.d_id and t_post.p_id=t_dp.p_id ;
select p_name,staff_name
from t_post,t_staff,t_sp
where p_name='销售部经理' and t_post.p_id=t_sp.p_id and id=s_id ;
select staff_name,dep_name
from t_staff,t_department
where staff_name='杨安平'and depart=d_id;
select dep_name,staff_name
from t_department,t_staff
where dep_name='人力资源部'and depart=d_id;
select staff_name,dep_name
from t_staff,t_department
where depart=d_id;
select staff_name
from t_staff
where staff_name like'%小%';
select staff_name
from t_staff
where staff_name like'小%';
select staff_name
from t_staff
where staff_name like'%小';select staff_name
from t_staff
where staff_name like'_小_';
select dep_name
from t_department
order by d_id ;
select dep_name
from t_department
order by d_id desc;
select staff_name,p_id
from t_staff ,t_sp
where id=s_id ;
select staff_name,p_id
from t_staff full outer join t_sp
on id=s_id ;
select dep_name,p_name
from t_department d left outer join t_dp dp
on d.d_id=dp.d_id
right outer join t_post
on dp.p_id=t_post.p_id;
select dep_name,p_name
from t_department d full outer join t_dp dp
on d.d_id=dp.d_id
full outer join t_post
on dp.p_id=t_post.p_id;
select dep_name,p_id
from t_department d full outer join t_dp dp
on d.d_id=dp.d_id;
select count(*)
from t_department;
select staff_name,w_sum
from t_staff,t_wage
where id=s_id and w_sum in(select max(w_sum) from t_wage);
select avg(age)
from t_staff
where id is not null;select staff_name,w_sum,age
from t_staff,t_wage
where id=s_id
group by staff_name,w_sum,age
having age<avg(age);
数据和值
说下你想干嘛啊?你想获得什么样的数据,需求说清楚,哎。
反正你这样给出一连串的SQL语句,需求又不给说,如果是我忙的时候,我才懒得给你看哦。
自个想办法 sorry
group by 的select是这么写的。
select 字段列表,表达式列表,聚组函数
from 表
where 条件
group by 字段列表,表达式列表
from t_staff,t_wage
where id=s_id
group by depart
having age>avg(age);
试试看