34,575
社区成员
发帖
与我相关
我的任务
分享
--如果项目表,设备表已经存在。
create table tb(Project varchar(10),Description varchar(10),Type varchar(10),Customer varchar(10),DeviceType varchar(10),Device varchar(10))
insert into tb values('A', 'ddd', 'ee', 'aaa', 'Type1', 'Device1')
insert into tb values('A', 'ddd', 'ee', 'aaa', 'Type1', 'Device2')
insert into tb values('A', 'ddd', 'ee', 'aaa', 'Type2', 'Device3')
insert into tb values('B', 'dd' , 'aaa', 'ea' , 'Type3', 'Device5')
insert into tb values('C', 'ee' , 'e' , 'aaa', 'Type2', 'Device3')
insert into tb values('C', 'ee' , 'e' , 'aaa', 'Type2', 'Device4')
create table 项目表(ProjectID int, Project varchar(10), Description varchar(10),Type varchar(10), Customer varchar(10))
create table 设备表(id int, project varchar(10), DeviceType varchar(10) , Device varchar(10))
go
insert into 项目表(Project , Description , Type , CusTomer , ProjectID)
SELECT * , ProjectID=(SELECT COUNT(project) FROM (select distinct Project,Description,Type,Customer from tb) t WHERE Project < a.Project) + 1 FROM (select distinct Project,Description,Type,Customer from tb) a
select * from 项目表
/*
ProjectID Project Description Type Customer
----------- ---------- ----------- ---------- ----------
1 A ddd ee aaa
2 B dd aaa ea
3 C ee e aaa
(所影响的行数为 3 行)
*/
insert into 设备表(project,DeviceType,Device,id)
select * , id = (select count(1) from (select a.project , b.DeviceType , b.Device from 项目表 a , tb b where a.project = b.project) t where (project < m.project) or (project = m.project and devicetype < m.devicetype) or (project = m.project and devicetype = m.devicetype and device < m.device)) + 1 from (select a.project , b.DeviceType , b.Device from 项目表 a , tb b where a.project = b.project) m
select * from 设备表
/*
id project DeviceType Device
----------- ---------- ---------- ----------
1 A Type1 Device1
2 A Type1 Device2
3 A Type2 Device3
4 B Type3 Device5
5 C Type2 Device3
6 C Type2 Device4
(所影响的行数为 6 行)
*/
drop table tb,项目表,设备表
create table tb(Project varchar(10),Description varchar(10),Type varchar(10),Customer varchar(10),DeviceType varchar(10),Device varchar(10))
insert into tb values('A', 'ddd', 'ee', 'aaa', 'Type1', 'Device1')
insert into tb values('A', 'ddd', 'ee', 'aaa', 'Type1', 'Device2')
insert into tb values('A', 'ddd', 'ee', 'aaa', 'Type2', 'Device3')
insert into tb values('B', 'dd' , 'aaa', 'ea' , 'Type3', 'Device5')
insert into tb values('C', 'ee' , 'e' , 'aaa', 'Type2', 'Device3')
insert into tb values('C', 'ee' , 'e' , 'aaa', 'Type2', 'Device4')
go
select ProjectID = identity(int,1,1) , * into 项目表 from (select distinct Project,Description,Type,Customer from tb) t order by project
select * from 项目表
/*
ProjectID Project Description Type Customer
----------- ---------- ----------- ---------- ----------
1 A ddd ee aaa
2 B dd aaa ea
3 C ee e aaa
(所影响的行数为 3 行)
*/
select id = identity(int,1,1) , a.project , b.DeviceType , b.Device into 设备表 from 项目表 a , tb b where a.project = b.project order by a.project , b.DeviceType , b.Device
select * from 设备表
/*
id project DeviceType Device
----------- ---------- ---------- ----------
1 A Type1 Device1
2 A Type1 Device2
3 A Type2 Device3
4 B Type3 Device5
5 C Type2 Device3
6 C Type2 Device4
(所影响的行数为 6 行)
*/
drop table tb,项目表,设备表