表Y job_message 职位(position_name) 职位ID 总人数 空缺数 A 1 10 B 2 7 这样怎么能统计出来表X的职位数再和表Y的总数相减把值给空缺数 update job_message,employee_info, (select position,count(position) as employeed from employee_info where del=1 group by position) as p set job_message.space=(job_message.total-p.employeed ) where job_message.position_name = employee_info.position and job_message.del=1我这么写出来以后结果 空缺数 A 8 B 5
计算出空缺数space 上面的那个正确结果应该是 A 10 B 5
因为有些职位没有人,所以必须要用右连接的,不然行数对不齐,减下来不对。 select position,count(position) as employeed ,position_name from employee_info right join job_message on job_message.position_name = employee_info.position where job_message.del=1 and job_message.del=1 group by position 这个能搜索出来所有的,但是 update job_message,employee_info, (select position,count(position) as employeed ,position_name from employee_info right join job_message on job_message.position_name = employee_info.position where job_message.del=1 and job_message.del=1 group by position ) as p set job_message.space=job_message.total-p.employeed where job_message.position_name = employee_info.position and job_message.del=1这个插入进去空缺数没变化。。我哪写错了吗?del是设置的假删除。
请直接贴出你的CREATE TABLE语句和INSERT语句,这样别人可以直接测试。
希望下面的代码对楼主有帮助。PS:第二个表中的总人数,我理解为这个职位一共需要的人数。 drop table if exists stuff; create table stuff( name varchar(10) primary key, job varchar(10) not null, jobid int(4)not null);insert into stuff values('www','sales',5); insert into stuff values('sss','sales',5);drop table if exists pos; create table pos( job varchar(10) primary key, jobid int(4) not null, alls int(4) not null, lack int(4));insert into pos values('sales',5,5); update pos set lack= (pos.alls - (select count(*) from stuff where pos.jobid=stuff.jobid));
不好意思,刚才有个测试的。忘了贴了。drop table if exists stuff; create table stuff( name varchar(10) primary key, job varchar(10) not null, jobid int(4)not null);insert into stuff values('www','sales',5); insert into stuff values('sss','sales',5); insert into stuff values('ddd','programmer',4);drop table if exists pos; create table pos( job varchar(10) primary key, jobid int(4) not null, alls int(4) not null, lack int(4));insert into pos values('sales',5,5,null); insert into pos values('programmer',4,4,null);update pos set lack= (pos.alls - (select count(*) from stuff where pos.jobid=stuff.jobid));
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
表X employee_info
人员ID 职位( position)
1 B
2 B
表Y job_message
职位(position_name) 职位ID 总人数 空缺数
A 1 10
B 2 7
这样怎么能统计出来表X的职位数再和表Y的总数相减把值给空缺数
update job_message,employee_info, (select position,count(position) as employeed from employee_info where del=1 group by position) as p set job_message.space=(job_message.total-p.employeed ) where job_message.position_name = employee_info.position and job_message.del=1我这么写出来以后结果
空缺数
A 8
B 5
上面的那个正确结果应该是
A 10
B 5
select position,count(position) as employeed ,position_name from employee_info
right join job_message on job_message.position_name = employee_info.position where job_message.del=1
and job_message.del=1
group by position
这个能搜索出来所有的,但是
update job_message,employee_info, (select position,count(position) as employeed ,position_name from employee_info
right join job_message on job_message.position_name = employee_info.position where job_message.del=1
and job_message.del=1
group by position ) as p set job_message.space=job_message.total-p.employeed where job_message.position_name = employee_info.position and job_message.del=1这个插入进去空缺数没变化。。我哪写错了吗?del是设置的假删除。
drop table if exists stuff;
create table stuff(
name varchar(10) primary key,
job varchar(10) not null,
jobid int(4)not null);insert into stuff values('www','sales',5);
insert into stuff values('sss','sales',5);drop table if exists pos;
create table pos(
job varchar(10) primary key,
jobid int(4) not null,
alls int(4) not null,
lack int(4));insert into pos values('sales',5,5);
update pos set lack=
(pos.alls - (select count(*) from stuff where pos.jobid=stuff.jobid));
create table stuff(
name varchar(10) primary key,
job varchar(10) not null,
jobid int(4)not null);insert into stuff values('www','sales',5);
insert into stuff values('sss','sales',5);
insert into stuff values('ddd','programmer',4);drop table if exists pos;
create table pos(
job varchar(10) primary key,
jobid int(4) not null,
alls int(4) not null,
lack int(4));insert into pos values('sales',5,5,null);
insert into pos values('programmer',4,4,null);update pos set lack=
(pos.alls - (select count(*) from stuff where pos.jobid=stuff.jobid));