求表A
ID VALUE
1 名1
2 名2
3 名3
1 名1"
2 名2"
3 名3"
实现从上表中选择ID为1和2的数据插入到新表B的SQL语句,谢谢!表B
ID_1 VALUE_1 ID_2 VALUE_2
1 名1 2 名2
1 名1" 2 名2"
ID VALUE
1 名1
2 名2
3 名3
1 名1"
2 名2"
3 名3"
实现从上表中选择ID为1和2的数据插入到新表B的SQL语句,谢谢!表B
ID_1 VALUE_1 ID_2 VALUE_2
1 名1 2 名2
1 名1" 2 名2"
ID_1 VALUE_1 ID_2 VALUE_2
1 名1 2 名2
1 名1" 2 名2"
insert @t select
1,'名1' union select
2,'名2' union select
3,'名3' union select
1,'名1"' union select
2,'名2"' union select
3,'名3"'
select id,value,px = identity(int,1,1) into #1
from @t
where id = 1
select id,value,px = identity(int,1,1) into #2
from @t
where id = 2 select a.id,a.value,b.id,b.value
from #1 a,#2 b
where a.px = b.px/*
id value id value
----------- ---------- ----------- ----------
1 名1 2 名2
1 名1" 2 名2"(所影响的行数为 2 行)
*/drop table #1,#2
declare @a table(id int,value varchar(10))
insert into @a select 1,'名1'
insert into @a select 2,'名2'
insert into @a select 3,'名3'
insert into @a select 1,'名1"'
insert into @a select 2,'名2"'
insert into @a select 3,'名3"'
declare @b table(id_1 int,value_1 varchar(10),id_2 int,value_2 varchar(10))
insert into @b select * from @a a,@a b
where a.id=1 and b.id=2 and charindex('"',a.value)=charindex('"',b.value)select * from @b
declare @a char(10),@b char(10)set @a=(select top 1 value from A where id=1)
set @b=(select top 1 value from A where id=2)insert into B values(1, @a, 2, @b)
insert into B values(1, @a, 2, @b)
insert into A values(1, '名1')
insert into A values(2, '名2')
insert into A values(3, '名3')
insert into A values(1, '名1"')
insert into A values(2, '名2"')
insert into A values(3, '名3"')
create table B(ID_1 int, VALUE_1 varchar(10), ID_2 int, VALUE_2 varchar(10))
goinsert into B
select m.id , m.value , n.id , n.value from
(select * , px = (select count(1) from A where id = 1 and value < t.value) + 1 from A t where id = 1) m
full join
(select * , px = (select count(1) from A where id = 2 and value < t.value) + 1 from A t where id = 2) n
on m.px = n.pxselect * from Bdrop table A,B/*
ID_1 VALUE_1 ID_2 VALUE_2
----------- ---------- ----------- ----------
1 名1 2 名2
1 名1" 2 名2"
(所影响的行数为 2 行)
*/
INSERT INTO B(ID_1,VALUE_1,ID_2,VALUE_2)
SELECT ID, VALUE FROM B WHERE ID=1 ,SELECT ID, VALUE FROM B WHERE ID=2
插入子查询结果中子查询能连接吗,从而实现同时插和入多项子查询结果,以上语句如何调整后能实现该功能。
SELECT ID, VALUE FROM B WHERE ID=1 ,SELECT ID, VALUE FROM B WHERE ID=2
-----------------不能这样写的
如果没有可用子查询生成顺序号关联
如果有重复在2000下能自增列的中间表,在05可用row_number()