insert into 表C (id) select id from 表adeclare @i int set @i=0 while @i<5 beginupdate 表C set name=(select min(name) from 表b) where id=(select min(id) from 表a)delete from 表b where name=(select min(name) from 表b) delete from 表a where id=(select min(id) from 表a)set @i=@i+1 endselect * from 表C
---测试数据--- if object_id('[表a]') is not null drop table [表a] go create table [表a]([id] int) insert [表a] select 1 union all select 2 union all select 3 union all select 4 union all select 5 if object_id('[表b]') is not null drop table [表b] go create table [表b]([name] varchar(1)) insert [表b] select 'a' union all select 'b' union all select 'c' union all select 'd' union all select 'E'
---查询--- select a.id,b.name from [表a] a,[表b] b where (a.id=ascii(b.name)-96) --小写字母 or (a.id=ascii(b.name)-64) --大小字母---结果--- id name ----------- ---- 1 a 2 b 3 c 4 d 5 E(所影响的行数为 5 行)
declare @a table (id int) insert into @a select 1 union all select 2 union all select 3 union all select 4 union all select 5 declare @b table (name nvarchar(10)) insert into @b select 'a' union all select 'b' union all select 'c' union all select 'd' union all select 'e' select a.*,b.name from @a a join( select row_number()over(order by name) as id,name from @b) b on a.id=b.id1 a 2 b 3 c 4 d 5 e
SELECT ID=IDENTITY(INT,1,1),* INTO #1 FROM tb1 SELECT ID=IDENTITY(INT,1,1),* INTO #2 FROM tb2SELECT A.userid, B.money FROM #1 AS A JOIN #2 AS B ON A.ID=B.ID
insert into 表C (id)
select id from 表adeclare @i int
set @i=0
while @i<5
beginupdate 表C
set name=(select min(name) from 表b)
where id=(select min(id) from 表a)delete from 表b where name=(select min(name) from 表b)
delete from 表a where id=(select min(id) from 表a)set @i=@i+1
endselect * from 表C
if object_id('[表a]') is not null drop table [表a]
go
create table [表a]([id] int)
insert [表a]
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5
if object_id('[表b]') is not null drop table [表b]
go
create table [表b]([name] varchar(1))
insert [表b]
select 'a' union all
select 'b' union all
select 'c' union all
select 'd' union all
select 'E'
---查询---
select a.id,b.name
from [表a] a,[表b] b
where (a.id=ascii(b.name)-96) --小写字母
or (a.id=ascii(b.name)-64) --大小字母---结果---
id name
----------- ----
1 a
2 b
3 c
4 d
5 E(所影响的行数为 5 行)
declare @a table (id int)
insert into @a select 1
union all select 2
union all select 3
union all select 4
union all select 5
declare @b table (name nvarchar(10))
insert into @b select 'a'
union all select 'b'
union all select 'c'
union all select 'd'
union all select 'e'
select a.*,b.name from @a a join(
select row_number()over(order by name) as id,name from @b) b
on a.id=b.id1 a
2 b
3 c
4 d
5 e
最后结果row_number 必须要 order by 我的记录就是自然的,如果用order by 就错了,可能我上面的例子有些问题效果如下表 userid
31
2
56
77
15表money
20.5
30.4
70.6
0.5
21.00表c
userid money我想达到的效果表C
userid money
31 20.5
2 30.4
56 70.6
77 0.5
15 21两个表记录其实都是对应的,就是需要对接一下,组成一个新的表
SELECT ID=IDENTITY(INT,1,1),* INTO #2 FROM tb2SELECT
A.userid,
B.money
FROM #1 AS A
JOIN #2 AS B
ON A.ID=B.ID