2个表的数据合并成1个表

wangxiaofeiwuqiao 2011-07-15 11:54:49
我有2个要表或者说视图，一个tb1,一个tb2。他们的关系是：tb2中的ITEM_CODE都可以在tb1表中找到。
tb1表:


ITEM_CODE        LOCATION      STOCK_QTY      QTY_RATIO      CTM_QTY

61-78495-00080     CSP1         850              0.15          30

61-78495-00100     CSP1         620              0.25          125

61-78495-00150     CSP1         800              0.85          680

61-78495-00230     CSP1         700              0.75          525

61-78495-00410     CSP1         1000             0.65          650

tb2表：


ITEM_CODE        LOCATION       SUMQTY        INDATE

61-78495-00080      CSP1         450          2011-05-01

61-78495-00100      CSP1         100          2011-06-04

61-78495-00410      CSP1         -500         2011-07-06

我想要的合并成一个视图，结果是：


ITEM_CODE        LOCATION      STOCK_QTY      QTY_RATIO        CTM_QTY             INDATE

61-78495-00080     CSP1         400(850-450)     0.15          60（400*0.15）     2011-05-01

61-78495-00100     CSP1         520(620-100)     0.25          130(520*0.25)      2011-06-04

61-78495-00150     CSP1         800              0.85          680

61-78495-00230     CSP1         700              0.75          525

61-78495-00410     CSP1         1500(1000+500)   0.65          975(1500*0.65)     2011-07-06


数据库环境：SQL 2000
...全文
172 17 打赏 收藏 转发到动态 举报
写回复
用AI写文章
17 条回复
切换为时间正序
请发表友善的回复…
发表回复
-晴天 2011-07-15
  • 打赏
  • 举报
 回复 
select a.item_code,a.location,a.stock_qtyu-isnull(b.stock_qty,0) as stock_qty,a.qty_ratio,(a.stock_qtyu-isnull(b.stock_qty,0))*qty_ratio as ctm_qty,b.indate 

from t1 a left join t2 b on a.item_code=b.item_code
AcHerat 元老 2011-07-15
  • 打赏
  • 举报
 回复 


create table tb1(ITEM_CODE varchar(50),LOCATION varchar(50),STOCK_QTY float,

QTY_RATIO float,CTM_QTY float)



insert into tb1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 

values('61-78495-00080','CSP1','200','0.15','30')

insert into tb1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 

values('61-78495-00100','CSP1','500','0.25','125')

insert into tb1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 

values('61-78495-00150','CSP1','800','0.85','680')

insert into tb1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 

values('61-78495-00230','CSP1','700','0.75','525')

insert into tb1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 

values('61-78495-00410','CSP1','1000','0.65','650')



create table tb2(ITEM_CODE varchar(50),LOCATION varchar(50),

SUMQTY float,INDATE datetime)



insert into tb2(ITEM_CODE,LOCATION,SUMQTY,INDATE) 

values('61-78495-00080','CSP1','450','2011-05-01')

insert into tb2(ITEM_CODE,LOCATION,SUMQTY,INDATE) 

values('61-78495-00100','CSP1','100','2011-06-04')

insert into tb2(ITEM_CODE,LOCATION,SUMQTY,INDATE) 

values('61-78495-00410','CSP1','-500','2011-07-06')

go



select a.ITEM_CODE,a.LOCATION,a.STOCK_QTY-ISNULL(B.SUMQTY,0) STOCK_QTY,A.QTY_RATIO,

    (a.STOCK_QTY-ISNULL(B.SUMQTY,0))*a.QTY_RATIO CTM_QTY,B.INDATE

from tb1 a left join tb2 b on a.ITEM_CODE = b.ITEM_CODE



drop table tb1,tb2



/***************



ITEM_CODE                                          LOCATION                                           STOCK_QTY              QTY_RATIO              CTM_QTY                INDATE

-------------------------------------------------- -------------------------------------------------- ---------------------- ---------------------- ---------------------- -----------------------

61-78495-00080                                     CSP1                                               -250                   0.15                   -37.5                  2011-05-01 00:00:00.000

61-78495-00100                                     CSP1                                               400                    0.25                   100                    2011-06-04 00:00:00.000

61-78495-00150                                     CSP1                                               800                    0.85                   680                    NULL

61-78495-00230                                     CSP1                                               700                    0.75                   525                    NULL

61-78495-00410                                     CSP1                                               1500                   0.65                   975                    2011-07-06 00:00:00.000



(5 行受影响)
wangxiaofeiwuqiao 2011-07-15
  • 打赏
  • 举报
 回复
搞错了一个东西，要去掉tb2中的INDATE和合并后的INDATE.
randee_luo 2011-07-15
  • 打赏
  • 举报
 回复 
select tb1.*,tb2.INDATE from tb1 left join tb2 on tb1.ITEM_CODE=tb2.ITEM_CODE
AcHerat 元老 2011-07-15
  • 打赏
  • 举报
 回复 


select a.ITEM_CODE,a.LOCATION,a.STOCK_QTY-ISNULL(B.SUMQTY,0) STOCK_QTY,A.QTY_RATIO,

    (a.STOCK_QTY-ISNULL(B.SUMQTY,0))*a.QTY_RATIO CTM_QTY,B.INDATE

from tb1 a left join tb2 b on a.ITEM_CODE = b.ITEM_CODE
wangxiaofeiwuqiao 2011-07-15
  • 打赏
  • 举报
 回复
测试数据：


create table tb1(ITEM_CODE varchar(50),LOCATION varchar(50),STOCK_QTY float,

QTY_RATIO float,CTM_QTY float)



insert into tb1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 

values('61-78495-00080','CSP1','200','0.15','30')

insert into tb1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 

values('61-78495-00100','CSP1','500','0.25','125')

insert into tb1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 

values('61-78495-00150','CSP1','800','0.85','680')

insert into tb1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 

values('61-78495-00230','CSP1','700','0.75','525')

insert into tb1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 

values('61-78495-00410','CSP1','1000','0.65','650')



create table tb2(ITEM_CODE varchar(50),LOCATION varchar(50),

SUMQTY float,INDATE datetime)



insert into tb2(ITEM_CODE,LOCATION,SUMQTY,INDATE) 

values('61-78495-00080','CSP1','450','2011-05-01')

insert into tb2(ITEM_CODE,LOCATION,SUMQTY,INDATE) 

values('61-78495-00100','CSP1','100','2011-06-04')

insert into tb2(ITEM_CODE,LOCATION,SUMQTY,INDATE) 

values('61-78495-00410','CSP1','-500','2011-07-06')
mycodeis0000 2011-07-15
  • 打赏
  • 举报
 回复 


ta1

ITEM_CODE        LOCATION      STOCK_QTY      QTY_RATIO      CTM_QTY

61-78495-00080     CSP1         850              0.15          30



tb2表：

SQL code

ITEM_CODE        LOCATION       SUMQTY        INDATE



create view v_tb1tb2

as

select tb1.ITEM_CODE,tb1.LOCATION,tb1.STOCK_QTY,tb1.QTY_RATIO,

tb1.CTM_QTY,tb2.SUMQTY,tb2.INDATE

 from tb1 inner join tb2 on (tb1.ITEM_CODE=tb2.ITEM_CODE)
WXM8511 2011-07-15
  • 打赏
  • 举报
 回复 
 USE TEST

 

 create table tb1(ITEM_CODE varchar(50),LOCATION varchar(50),STOCK_QTY float,

QTY_RATIO float,CTM_QTY float)



insert into tb1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 

values('61-78495-00080','CSP1','200','0.15','30')

insert into tb1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 

values('61-78495-00100','CSP1','500','0.25','125')

insert into tb1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 

values('61-78495-00150','CSP1','800','0.85','680')

insert into tb1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 

values('61-78495-00230','CSP1','700','0.75','525')

insert into tb1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 

values('61-78495-00410','CSP1','1000','0.65','650')



create table tb2(ITEM_CODE varchar(50),LOCATION varchar(50),

SUMQTY float,INDATE datetime)



insert into tb2(ITEM_CODE,LOCATION,SUMQTY,INDATE) 

values('61-78495-00080','CSP1','450','2011-05-01')

insert into tb2(ITEM_CODE,LOCATION,SUMQTY,INDATE) 

values('61-78495-00100','CSP1','100','2011-06-04')

insert into tb2(ITEM_CODE,LOCATION,SUMQTY,INDATE) 

values('61-78495-00410','CSP1','-500','2011-07-06')



GO



SELECT * FROM tb1

SELECT * FROM tb2



 

SELECT A.ITEM_CODE,A.LOCATION,ISNULL(STOCK_QTY-SUMQTY,STOCK_QTY) AS STOCK_QTY,QTY_RATIO,(ISNULL(A.STOCK_QTY-SUMQTY,STOCK_QTY))*QTY_RATIO AS CTM_QTY,

INDATE FROM tb1 A LEFT JOIN TB2  B ON A.ITEM_CODE=B.ITEM_CODE AND A.LOCATION=B.LOCATION
oO寒枫Oo 2011-07-15
  • 打赏
  • 举报
 回复
呵呵 是的 CTM_QTY 这列注意一点 



select a.item_code,

a.location,

isnull(a.stock_qty,0)-isnull(b.SUMQTY,0) as stock_qty,

a.qty_ratio,

case when b.SUMQTY is null then b.CTM_QTY else

(isnull(a.stock_qty,0)-b.SUMQTY)*qty_ratio  end as ctm_qty

from tb1 a left join tb2 b 

on a.item_code=b.item_code
oO寒枫Oo 2011-07-15
  • 打赏
  • 举报
 回复
是不是 CTM_QTY 都算错了？


select a.item_code,

a.location,

isnull(a.stock_qty,0)-isnull(b.SUMQTY,0) as stock_qty,

a.qty_ratio,

case when b.SUMQTY is null then b.CTM_QTY else

(isnull(a.stock_qty,0)-b.SUMQTY)*qty_ratio as ctm_qty

from tb1 a left join tb2 b 

on a.item_code=b.item_code
wangxiaofeiwuqiao 2011-07-15
  • 打赏
  • 举报
 回复
我明白了，测试数据没对上
oO寒枫Oo 2011-07-15
  • 打赏
  • 举报
 回复
tb2表里面 没有 STOCK_QTY字段吧
wangxiaofeiwuqiao 2011-07-15
  • 打赏
  • 举报
 回复
为什么预料的结果跟实际结果不一样呢
oO寒枫Oo 2011-07-15
  • 打赏
  • 举报
 回复 



select a.item_code,

a.location,

isnull(a.stock_qty,0)-isnull(b.SUMQTY,0) as stock_qty,

a.qty_ratio,

(isnull(a.stock_qty,0)-isnull(b.SUMQTY,0))*qty_ratio as ctm_qty

from tb1 a left join tb2 b 

on a.item_code=b.item_code
wangxiaofeiwuqiao 2011-07-15
  • 打赏
  • 举报
 回复
tb1表里面的STOCK_QTY要减去tb2表里面对应ITEM_CODE的STOCK_QTY，结果跟我写出来的不一样啊
oO寒枫Oo 2011-07-15
  • 打赏
  • 举报
 回复 


select a.item_code,

a.location,

isnull(a.stock_qtyu,0)-isnull(b.stock_qty,0) as stock_qty,

a.qty_ratio,

(isnull(a.stock_qtyu,0)-isnull(b.stock_qty,0))*qty_ratio as ctm_qty

from tb1 a left join tb2 b 

on a.item_code=b.item_code
--小F-- 2011-07-15
  • 打赏
  • 举报
 回复 
select

 a.item_code,a.location,a.stock_qtyu-isnull(b.stock_qty,0) as stock_qty,

 a.qty_ratio,(a.stock_qtyu-isnull(b.stock_qty,0))*qty_ratio as ctm_qty,b.indate 

from

 t1 a left join t2 b

on

 a.item_code=b.item_code
PowerBI系列之Power Query专题
连接数据库：Sql server、 Mysql（直连但是必须先安装一个mysql插件） DirectQuery直连查询：Sql server ODBC方式获取数据 表关联或多个Sql或调用存储过程获取数据 SQL中动态传参和自定义函数: sql中使用参数或...
多个Excel表合并成一个Excel表
问题描述： 往往在进行数据统计分析前，会遇到数据分在不同的表里的情况，这时候就需要将多个表的数据先放到一个表的数据里面，然后再进行分析。比如班级需要汇总每个人的信息，几十人每个人都发了个人的信息表，一...
两个有序链表合并成一个有序的单链表
(1)定义一个合并后的指针pc指向La表的头结点。由于要求不占用新的存储空间，所有用La的头结点当做新表的的头结点。 (2)依次从La和Lb表中取出最小的的元素，然后将其链接到新表后，直到其中一个表为空。 (3)最后，将...
两个有序顺序表合并成一个有序顺序表
将两个有序顺序表合并为一个新的有序表，并由函数返回结果顺序表。实际过程中应该不断取下两个顺序表表头较小的结点存在新的顺序表中，然后，将其中某个表中的剩余数据直接加到新的顺序表后面。 二 代码实现 /*...
MySQL表的合并操作详解
上述代码中，我们使用UNION操作符将表1和表2中的数据合并为一个结果集，并根据表2中的address列和salary列分别作为name和age列的值。上述代码中，我们使用INNER JOIN操作根据表1和表2中id列的值进行连接，并返回合并...
MS-SQL Server

34,590

社区成员

254,589

社区内容

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

加载中

查看更多榜单
社区公告
暂无公告

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

+ 用AI写文章