create table t_department
(
dept_id varchar(50),
dept_name varchar(50)
)
insert into t_department select '01','部门1'
insert into t_department select '02','部门2'create table t_employee
(
emp_id varchar(50),
dept_id varchar(50),
dept_name varchar(50)
)insert into t_employee select '001','01',null
insert into t_employee select '002','02',null
drop table t_department
drop table t_employee
/*
要把t_employee中dept_name列的NULL值替换为dept_id列所对应的部门1,部门2,该怎样写SQL
*/
(
dept_id varchar(50),
dept_name varchar(50)
)
insert into t_department select '01','部门1'
insert into t_department select '02','部门2'create table t_employee
(
emp_id varchar(50),
dept_id varchar(50),
dept_name varchar(50)
)insert into t_employee select '001','01',null
insert into t_employee select '002','02',null
drop table t_department
drop table t_employee
/*
要把t_employee中dept_name列的NULL值替换为dept_id列所对应的部门1,部门2,该怎样写SQL
*/
insert into t_employee
select
'001',
'01',
(select top 1 dept_name from t_department where dept_id = a.dept_id)
from t_department a
--下面同理
--不好意思写错insert into t_employee
select
'001',
'01',
dept_name
from t_department
where dept_id = '01'
create table t_department
(
dept_id varchar(50),
dept_name varchar(50)
)
insert into t_department select '01','部门1'
insert into t_department select '02','部门2' create table t_employee
(
emp_id varchar(50),
dept_id varchar(50),
dept_name varchar(50)
) insert into t_employee select '001','01',null
insert into t_employee select '002','02',null
update e set dept_name=d.dept_name from t_employee e inner join t_department d on e.dept_id=d.dept_id where e.dept_name is null
select * from t_employee
drop table t_department
drop table t_employee
/*
emp_id dept_id dept_name
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
001 01 部门1
002 02 部门2*/[/code]