27,579
社区成员
发帖
与我相关
我的任务
分享合并表字段
表1
Name1 Amount1 Weight1
N1 10 0.5
N2 7 0.3
表2
Name2 Amount2 Weight2
N6 2 0.4
N4 3 0.58
C2 5 1.2
D9 8 2.1
连接后为
Name1 Amount1 Weight1 Name2 Amount2 Weight2
N1 10 0.5 N6 2 0.4
N2 7 0.3 N4 3 0.58
C2 5 1.2
D9 8 2.1
什么方法最合适？
...全文
请发表友善的回复…
发表回复
sunhood 2008-11-11
- 打赏
- 举报
搞好了，谢谢小雄，我在前面加了个 id=identity(int,1,1)，就可以解决了，给分
等不到来世 2008-11-10
- 打赏
- 举报
if object_id('[表1]') is not null drop table [表1]
go
create table [表1]([code] varchar(1),[name1] varchar(2),[amount1] int,[weight1] numeric(3,2))
insert [表1]
select 'A','N1',10,0.5 union all
select 'A','N8',6,2.1 union all
select 'B','N2',7,0.3 union all
select 'B','N3',4,1.1 union all
select 'B','N5',3,2.03 union all
select 'C','N8',5,1.02
if object_id('[表2]') is not null drop table [表2]
go
create table [表2]([code] varchar(1),[name2] varchar(2),[amount2] int,[weight2] numeric(3,2))
insert [表2]
select 'A','N6',2,0.4 union all
select 'A','N4',3,0.58 union all
select 'A','C2',5,1.2 union all
select 'B','D9',8,2.1
select * from [表1]
select * from [表2]
select code=isnull(a.code,b.code),name1,amount1,weight1,name2,amount2,weight2
from
(
select *,rn=row_number() over(partition by code order by amount1 desc)
from [表1]
) a
full join
(
select *,rn=row_number() over(partition by code order by amount2)
from [表2]
) b
on a.code=b.code and a.rn=b.rn
--测试结果:
/*
code name1 amount1 weight1 name2 amount2 weight2
---- ----- ----------- --------------------------------------- ----- ----------- ---------------------------------------
A N1 10 0.50 N6 2 0.40
A N8 6 2.10 N4 3 0.58
A NULL NULL NULL C2 5 1.20
B N2 7 0.30 D9 8 2.10
B N3 4 1.10 NULL NULL NULL
B N5 3 2.03 NULL NULL NULL
C N8 5 1.02 NULL NULL NULL
(7 row(s) affected)
*/
sunhood 2008-11-10
- 打赏
- 举报
这样可以，但是and amount1>t.amount1这个判断条件不好，数量相同的话就不行了
等不到来世 2008-11-10
- 打赏
- 举报
if object_id('[表1]') is not null drop table [表1]
go
create table [表1]([code] varchar(1),[name1] varchar(2),[amount1] int,[weight1] numeric(3,2))
insert [表1]
select 'A','N1',10,0.5 union all
select 'A','N8',6,2.1 union all
select 'B','N2',7,0.3 union all
select 'B','N3',4,1.1 union all
select 'B','N5',3,2.03 union all
select 'C','N8',5,1.02
if object_id('[表2]') is not null drop table [表2]
go
create table [表2]([code] varchar(1),[name2] varchar(2),[amount2] int,[weight2] numeric(3,2))
insert [表2]
select 'A','N6',2,0.4 union all
select 'A','N4',3,0.58 union all
select 'A','C2',5,1.2 union all
select 'B','D9',8,2.1
select * from [表1]
select * from [表2]
select code=isnull(a.code,b.code),name1,amount1,weight1,name2,amount2,weight2
from
(
select *,rn=(select count(*) from [表1] where code=t.code and amount1>t.amount1)
from [表1] t
) a
full join
(
select *,rn=(select count(*) from [表2] where code=t.code and amount2<t.amount2)
from [表2] t
) b
on a.code=b.code and a.rn=b.rn
order by code
--测试结果:
/*
code name1 amount1 weight1 name2 amount2 weight2
---- ----- ----------- --------------------------------------- ----- ----------- ---------------------------------------
A N1 10 0.50 N6 2 0.40
A N8 6 2.10 N4 3 0.58
A NULL NULL NULL C2 5 1.20
B N2 7 0.30 D9 8 2.10
B N3 4 1.10 NULL NULL NULL
B N5 3 2.03 NULL NULL NULL
C N8 5 1.02 NULL NULL NULL
(7 row(s) affected)
*/
sunhood 2008-11-10
- 打赏
- 举报
不好意思啊，要做成下面这种效果
表1
Code Name1 Amount1 Weight1
A N1 10 0.5
A N8 6 2.1
B N2 7 0.3
B N3 4 1.1
B N5 3 2.03
C N8 5 1.02
表2
Code Name2 Amount2 Weight2
A N6 2 0.4
A N4 3 0.58
A C2 5 1.2
B D9 8 2.1
连接后为
Code Name1 Amount1 Weight1 Name2 Amount2 Weight2
A N1 10 0.5 N6 2 0.4
A N8 6 2.1 N4 3 0.58
A <Null> <Null> <Null> C2 5 1.2
B N2 7 0.3 D9 8 2.1
B N3 4 1.1 <Null> <Null> <Null>
B N5 3 2.03 <Null> <Null> <Null>
C N8 5 1.02 <Null> <Null> <Null>
wer123q 2008-11-10
- 打赏
- 举报
DECLARE @TA TABLE(Code Varchar(2),Name1 VARCHAR(2), Amount1 INT, Weight1 DECIMAL(10,2))
INSERT @TA
SELECT 'A','N1', 10, 0.5 UNION ALL
SELECT 'B','N2', 7, 0.3
DECLARE @TB TABLE( Code Varchar(2),Name2 VARCHAR(2),Amount2 INT,Weight2 DECIMAL(10,2))
INSERT @TB
SELECT'A', 'N6', 2, 0.4 UNION ALL
SELECT 'A','N4', 3, 0.58 UNION ALL
SELECT 'A','C2', 5, 1.2 UNION ALL
SELECT 'B','D9', 8, 2.1
select Code= case when ((select top 1 Name2 from @TB where code = b.code)= b.Name2) then a.code else '' end,
Name1 = case when ((select top 1 Name2 from @TB where code = b.code)= b.Name2) then a.Name1 else ''end,
Amount1 = case when ((select top 1 Name2 from @TB where code = b.code)= b.Name2) then a.Amount1 else ''end,
Weight1 = case when ((select top 1 Name2 from @TB where code = b.code)= b.Name2) then cast(a.Weight1 as varchar(10))else ''end ,
b.* from @TA as a right join @TB as b
on a.code=b.code sunhood 2008-11-10
- 打赏
- 举报
没人知道么？
sunhood 2008-11-10
- 打赏
- 举报
服务器: 消息 195，级别 15，状态 10，行 14
'row_number' 不是可以识别的 函数名。
Dear SQL(燊) 2008-11-08
- 打赏
- 举报
表1
Code Name1 Amount1 Weight1
A N1 10 0.5
B N2 7 0.3
表2
Code Name2 Amount2 Weight2
A N6 2 0.4
A N4 3 0.58
A C2 5 1.2
B D9 8 2.1
连接后为
Code Name1 Amount1 Weight1 Name2 Amount2 Weight2
A N1 10 0.5 N6 2 0.4
N4 3 0.58
C2 5 1.2
B N2 7 0.3 D9 8 2.1
select ID=IDENTITY(INT,1,1),a.Code, Name1, Amount1, Weight1, Name2, Amount2, Weight2
into #A
from [表1] a inner join [表2] b on a.code=b.code
order by a.code
select case when a.code=b.code then a.code else '' end,
case when a.code=b.code then a.name1 else '' end
from #A inner join (select code,min(id) as id from #a group by code)on b
on a.code=b.code
sunhood 2008-11-08
- 打赏
- 举报
表1
Code Name1 Amount1 Weight1
A N1 10 0.5
A N8 6 2.1
B N2 7 0.3
B N3 4 1.1
B N5 3 2.03
C N8 5 1.02
表2
Code Name2 Amount2 Weight2
A N6 2 0.4
A N4 3 0.58
A C2 5 1.2
B D9 8 2.1
连接后为
Code Name1 Amount1 Weight1 Name2 Amount2 Weight2
A N1 10 0.5 N6 2 0.4
A N8 6 2.1 N4 3 0.58
A <Null> <Null> <Null> C2 5 1.2
B N2 7 0.3 D9 8 2.1
B N3 4 1.1 <Null> <Null> <Null>
B N5 3 2.03 <Null> <Null> <Null>
C N8 5 1.02 <Null> <Null> <Null>
就是上面那样。比如Code='A' ，表1有2条数据，则连接后，前半部分显示为2条；表2有3条，则从Code='A'的第一条往下，后半部分显示3条。对于前半部分没有3条的话，则第3条的前半部分为Null（或为空），后面的也是一样。
不知道我说清楚没，语言组织能力不强，包涵包涵
等不到来世 2008-11-08
- 打赏
- 举报
[Quote=引用 11 楼 sunhood 的回复:]
表1 的Code 也会重复的哦，跟表2一样， Code字段也会重复，比如类似
Code
A
A
B
B
B
C
小雄上面的代码好像是表1Code不能重复的才可以。
[/Quote]
如果表1的Code可以重复，那连接的结果就会有很多种。
你需要哪一种结果？给一个样例出来。
表1 的Code 也会重复的哦，跟表2一样， Code字段也会重复，比如类似
Code
A
A
B
B
B
C
小雄上面的代码好像是表1Code不能重复的才可以。
[/Quote]
如果表1的Code可以重复，那连接的结果就会有很多种。
你需要哪一种结果？给一个样例出来。
sunhood 2008-11-08
- 打赏
- 举报
表1 的Code 也会重复的哦，跟表2一样， Code字段也会重复，比如类似
Code
A
A
B
B
B
C
小雄上面的代码好像是表1Code不能重复的才可以。
Code
A
A
B
B
B
C
小雄上面的代码好像是表1Code不能重复的才可以。
sunhood 2008-11-08
- 打赏
- 举报
谢谢楼上，是要这种结果
连接后为
Code Name1 Amount1 Weight1 Name2 Amount2 Weight2
A N1 10 0.5 N6 2 0.4
A N8 6 2.1 N4 3 0.58
A <Null> <Null> <Null> C2 5 1.2
B N2 7 0.3 D9 8 2.1
B N3 4 1.1 <Null> <Null> <Null>
B N5 3 2.03 <Null> <Null> <Null>
C N8 5 1.02 <Null> <Null> <Null>
losfound 2008-11-07
- 打赏
- 举报
再等等，写对了大家再考虑
哈哈
哈哈
等不到来世 2008-11-07
- 打赏
- 举报
2000:
if object_id('[表1]') is not null drop table [表1]
go
create table [表1]([code] varchar(1),[name1] varchar(2),[amount1] int,[weight1] float)
insert [表1]
select 'a','n1',10,0.5 union all
select 'b','n2',7,0.3
if object_id('[表2]') is not null drop table [表2]
go
create table [表2]([code] varchar(1),[name2] varchar(2),[amount2] int,[weight2] float)
insert [表2]
select 'a','n6',2,0.4 union all
select 'a','n4',3,0.58 union all
select 'a','c2',5,1.2 union all
select 'b','d9',8,2.1
select * from [表1]
select * from [表2]
--测试结果:
/*
*/
select Code
,Name1=case rn when 0 then Name1 else null end
,Amount1=case rn when 0 then Amount1 else null end
,Weight1=case rn when 0 then Weight1 else null end
,Name2,Amount2,Weight2
from
(
select a.Code,a.Name1,a.Amount1,a.Weight1
,b.Name2,b.Amount2,b.Weight2
,rn=(select count(*) from 表2 where code=b.code and amount2<b.amount2)
from 表1 a join 表2 b
on a.code=b.code
) t
/*
Code Name1 Amount1 Weight1 Name2 Amount2 Weight2
---- ----- ----------- ---------------------- ----- ----------- ----------------------
a n1 10 0.5 n6 2 0.4
a NULL NULL NULL n4 3 0.58
a NULL NULL NULL c2 5 1.2
b n2 7 0.3 d9 8 2.1
(4 row(s) affected)
*/等不到来世 2008-11-07
- 打赏
- 举报
if object_id('[表1]') is not null drop table [表1]
go
create table [表1]([code] varchar(1),[name1] varchar(2),[amount1] int,[weight1] float)
insert [表1]
select 'a','n1',10,0.5 union all
select 'b','n2',7,0.3
if object_id('[表2]') is not null drop table [表2]
go
create table [表2]([code] varchar(1),[name2] varchar(2),[amount2] int,[weight2] float)
insert [表2]
select 'a','n6',2,0.4 union all
select 'a','n4',3,0.58 union all
select 'a','c2',5,1.2 union all
select 'b','d9',8,2.1
select * from [表1]
select * from [表2]
--测试结果:
/*
*/
select Code
,Name1=case rn when 1 then Name1 else null end
,Amount1=case rn when 1 then Amount1 else null end
,Weight1=case rn when 1 then Weight1 else null end
,Name2,Amount2,Weight2
from
(
select a.Code,a.Name1,a.Amount1,a.Weight1
,b.Name2,b.Amount2,b.Weight2
,rn=row_number() over(partition by b.code order by b.Amount2)
from 表1 a join 表2 b
on a.code=b.code
) t
/*
Code Name1 Amount1 Weight1 Name2 Amount2 Weight2
---- ----- ----------- ---------------------- ----- ----------- ----------------------
a n1 10 0.5 n6 2 0.4
a NULL NULL NULL n4 3 0.58
a NULL NULL NULL c2 5 1.2
b n2 7 0.3 d9 8 2.1
(4 row(s) affected)
*/
rucypli 2008-11-07
- 打赏
- 举报
A N1 10 0.5 N6 2 0.4
A N1 10 0.5 N4 3 0.58
A N1 10 0.5 C2 5 1.2
B N2 7 0.3 D9 8 2.1
这样的结果多简单
A N1 10 0.5 N4 3 0.58
A N1 10 0.5 C2 5 1.2
B N2 7 0.3 D9 8 2.1
这样的结果多简单
sunhood 2008-11-07
- 打赏
- 举报
还没写对，下面的才对，汗。。。。对不住各位的眼睛了。。
Code Name1 Amount1 Weight1 Name2 Amount2 Weight2
A N1 10 0.5 N6 2 0.4
A N4 3 0.58
A C2 5 1.2
B N2 7 0.3 D9 8 2.1
sunhood 2008-11-07
- 打赏
- 举报
不好意思，少说了条件
表1
Code Name1 Amount1 Weight1
A N1 10 0.5
B N2 7 0.3
表2
Code Name2 Amount2 Weight2
A N6 2 0.4
A N4 3 0.58
A C2 5 1.2
B D9 8 2.1
连接后为
Code Name1 Amount1 Weight1 Name2 Amount2 Weight2
A N1 10 0.5 N6 2 0.4
N4 3 0.58
C2 5 1.2
B N2 7 0.3 D9 8 2.1
律己修心 2008-11-07
- 打赏
- 举报
这都想得出来，厉害，哈哈
加载更多回复（3）
