两表数据整合

oohhss · 发表于 2020-1-31 11:20:01

if object_id('a') is not null drop table a
go
CREATE TABLE a (
   [teacherid] [varchar] (50)
   [courseinfo] [varchar] (50),
   [coursename] [varchar] (50),
   [point]    [float] ,

)

insert into a(courseinfo,coursename,point,bjjh) values('0001','0101011002_1_1','岩浆岩及变质岩石学_理论',80.05)
insert into a(courseinfo,coursename,point,bjjh) values('0002','0101011003_1_1','岩浆学_理论',90.50)
insert into a(courseinfo,coursename,point,bjjh) values('0003','0101011006_1_1','变质岩石学_理论',90.50)
insert into a(courseinfo,coursename,point,bjjh) values('0004','0101011007_1_1','地理信息学_理论',90.50)
go

if object_id('b') is not null drop table a
go
CREATE TABLE b (
   [teacherid] [varchar] (50)
   [courseinfo] [varchar] (50),
   [coursename] [varchar] (50),
   [point]    [float] ,

)

insert into a(courseinfo,coursename,point,bjjh) values('0001','0101011002_1_1','岩浆岩及变质岩石学_理论',83.05)
insert into a(courseinfo,coursename,point,bjjh) values('0003','0101011006_1_1','变质岩石学_理论',95.50)
insert into a(courseinfo,coursename,point,bjjh) values('0005','0101011008_1_1','地理信息学_理论',90.50)
go

以上两个表里的数据，想整合到一个表里，a表里有教师及其得分而b表没有的，b表自动按a表增加一条，但是该教师的point项为0分，同样b表有a表没有的等同处理，望高手赐教

sinexe · 发表于 2020-3-28 20:15:01

不明白

八宝斋 · 发表于 2020-3-30 05:15:01

insert into a
select * from b where courseinfo not in(select courseinfo from a)

hiqwolfzzh · 发表于 2020-3-31 15:45:01

lz提供的sql是不是应该修改成：

CREATE TABLE a (
   [teacherid] [varchar] (50),
   [courseinfo] [varchar] (50),
   [coursename] [varchar] (50),
   [point]    [float]

)

insert into a(teacherid,courseinfo,coursename,point) values('0001','0101011002_1_1','岩浆岩及变质岩石学_理论',80.05)
insert into a(teacherid,courseinfo,coursename,point) values('0002','0101011003_1_1','岩浆学_理论',90.50)
insert into a(teacherid,courseinfo,coursename,point) values('0003','0101011006_1_1','变质岩石学_理论',90.50)
insert into a(teacherid,courseinfo,coursename,point) values('0004','0101011007_1_1','地理信息学_理论',90.50)
go

CREATE TABLE b (
   [teacherid] [varchar] (50),
   [courseinfo] [varchar] (50),
   [coursename] [varchar] (50),
   [point]    [float]

)
insert into b(teacherid,courseinfo,coursename,point) values('0001','0101011002_1_1','岩浆岩及变质岩石学_理论',83.05)
insert into b(teacherid,courseinfo,coursename,point) values('0003','0101011006_1_1','变质岩石学_理论',95.50)
insert into b(teacherid,courseinfo,coursename,point) values('0005','0101011008_1_1','地理信息学_理论',90.50)
go

hiqwolfzzh · 发表于 2020-3-31 20:45:01

insert into a
select teacherid,courseinfo,coursename,point 0
from b
where teacherid not in(select distinct teacherid from a)

hiqwolfzzh · 发表于 2020-4-1 09:00:02

刚写错了，这样即可
insert into a
select teacherid,courseinfo,coursename,0
from b
where teacherid not in(select distinct teacherid from a)

oohhss · 发表于 2020-7-22 14:00:01

if object_id('a') is not null drop table a
go
CREATE TABLE a (
   [teacherid] [varchar] (50),
   [courseinfo] [varchar] (50),
   [coursename] [varchar] (50),
   [point]    [float] ,

)

insert into a(teacherid,courseinfo,coursename,point) values('0001','0101011002_1_1','岩浆岩及变质岩石学_理论',80.05)
insert into a(teacherid,courseinfo,coursename,point) values('0002','0101011003_1_1','岩浆学_理论',90.50)
insert into a(teacherid,courseinfo,coursename,point) values('0003','0101011006_1_1','变质岩石学_理论',90.50)
insert into a(teacherid,courseinfo,coursename,point) values('0004','0101011007_1_1','地理信息学_理论',90.50)
go

if object_id('b') is not null drop table a
go
CREATE TABLE b (
   [teacherid] [varchar] (50),
   [courseinfo] [varchar] (50),
   [coursename] [varchar] (50),
   [point]    [float] ,

)

insert into a(teacherid,courseinfo,coursename,point) values('0001','0101011002_1_1','岩浆岩及变质岩石学_理论',83.05)
insert into a(teacherid,courseinfo,coursename,point) values('0003','0101011006_1_1','变质岩石学_理论',95.50)
insert into a(teacherid,courseinfo,coursename,point) values('0005','0101011008_1_1','地理信息学_理论',90.50)
go

生成表的语句是这样，那天写得急，都没来得及验证，实在对不起！

oohhss · 发表于 2020-7-22 15:45:01

if object_id('a') is not null drop table a
go
CREATE TABLE a (
   [teacherid] [varchar] (50),
   [courseinfo] [varchar] (50),
   [coursename] [varchar] (50),
   [point]    [float] ,

)

insert into a(teacherid,courseinfo,coursename,point) values('0001','0101011002_1_1','岩浆岩及变质岩石学_理论',80.05)
insert into a(teacherid,courseinfo,coursename,point) values('0002','0101011003_1_1','岩浆学_理论',90.50)
insert into a(teacherid,courseinfo,coursename,point) values('0003','0101011006_1_1','变质岩石学_理论',90.50)
insert into a(teacherid,courseinfo,coursename,point) values('0004','0101011007_1_1','地理信息学_理论',90.50)
go

if object_id('b') is not null drop table b
go
CREATE TABLE b (
   [teacherid] [varchar] (50),
   [courseinfo] [varchar] (50),
   [coursename] [varchar] (50),
   [point]    [float] ,

)

insert into b(teacherid,courseinfo,coursename,point) values('0001','0101011002_1_1','岩浆岩及变质岩石学_理论',83.05)
insert into b(teacherid,courseinfo,coursename,point) values('0003','0101011006_1_1','变质岩石学_理论',95.50)
insert into b(teacherid,courseinfo,coursename,point) values('0005','0101011008_1_1','地理信息学_理论',90.50)
go

select * from a
select * from b
----上面这段生成表
---这个答案有眉目，但是还没有完全回答我的问题
insert into a
select teacherid,courseinfo,coursename,0
from b
where teacherid not in(select distinct teacherid from a

		自动登录	找回密码
密码			立即注册