求SQL语句

ming_Y 2009-07-24 05:29:10
有两个表:
表1
key field1 field2 customer_name
1 100 50 A
2 90 20 B
3 70 25 C
4 65 80 D
5 120 75 C

表2

rkey field3 customer_name
1 41 A
2 15 B
8 10 C
7 100 D
9 60 B
表3
key field1 field2 field3 customer_name
1 100 50 41 A
2 90 20 15 B
3 70 25 0 C
4 65 80 0 D
5 120 75 0 C
8 0 0 10 C
7 0 0 100 D
9 0 0 60 B

将两个表的内容合并为表3,求SQL语句
...全文
147 6 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
izbox 2009-07-24
  • 打赏
  • 举报
回复
full join 没用过,学习下!
izbox 2009-07-24
  • 打赏
  • 举报
回复

select key1,Sum(Field1),Sum(Field2),Sum(Field3),customer_name from
(
select key1,Field1,Field2,0 as Field3,customer_name from tb1
union all
select key1,0 as Field1,0 as Field2,Field3,customer_name from tb2)
a
group by key1,customer_name

---------结果------------
1 100 50 41 a
2 90 20 15 b
9 0 0 60 b
3 70 25 0 c
5 120 75 0 c
8 0 0 10 c
4 65 80 0 d
7 0 0 100 d
izbox 2009-07-24
  • 打赏
  • 举报
回复

select key1,Sum(Field1),Sum(Field2),Sum(Field3),customer_name from (select key1,Field1,Field2,0 as Field3,customer_name from tb1
union all
select key1,0 as Field1,0 as Field2,Field3,customer_name from tb2) a
group by key1,customer_name
--小F-- 2009-07-24
  • 打赏
  • 举报
回复
[Quote=引用 2 楼 htl258 的回复:]
SQL code---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-24 17:27:53
---------------------------------
--> 生成测试数据表:t1Ifnotobject_id('[t1]')isnullDroptable[t1]GoCreatetabl¡­
[/Quote]

...
速度真快
htl258_Tony 2009-07-24
  • 打赏
  • 举报
回复

---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-24 17:27:53
---------------------------------
--> 生成测试数据表:t1

If not object_id('[t1]') is null
Drop table [t1]
Go
Create table [t1]([key] int,[field1] int,[field2] int,[customer_name] nvarchar(1))
Insert t1
Select 1,100,50,'A' union all
Select 2,90,20,'B' union all
Select 3,70,25,'C' union all
Select 4,65,80,'D' union all
Select 5,120,75,'C'
Go
--Select * from t1

--> 生成测试数据表:t2

If not object_id('[t2]') is null
Drop table [t2]
Go
Create table [t2]([rkey] int,[field3] int,[customer_name] nvarchar(1))
Insert t2
Select 1,41,'A' union all
Select 2,15,'B' union all
Select 8,10,'C' union all
Select 7,100,'D' union all
Select 9,60,'B'
Go
--Select * from t2

-->SQL查询如下:
select isnull(a.[key],b.[rkey]) as [key],isnull([field1],0) [field1],isnull([field2],0) [field2],
isnull(b.[field3],0) [field3],isnull(a.[customer_name],b.[customer_name]) [customer_name]
from t1 a
full join t2 b
on a.[key]=b.rkey
/*
key field1 field2 field3 customer_name
----------- ----------- ----------- ----------- -------------
1 100 50 41 A
2 90 20 15 B
3 70 25 0 C
4 65 80 0 D
5 120 75 0 C
8 0 0 10 C
7 0 0 100 D
9 0 0 60 B

(8 行受影响)
*/
ming_Y 2009-07-24
  • 打赏
  • 举报
回复
第二个表rkey错了,为key,和其它两个一样的.

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧