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

Question

我有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          650tb2表：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

Answer 1

测试数据：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')

Answer 2

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

Answer 3

select tb1.*,tb2.INDATE from tb1 left join tb2 on tb1.ITEM_CODE=tb2.ITEM_CODE

Answer 4

搞错了一个东西，要去掉tb2中的INDATE和合并后的INDATE.

Answer 5

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')
goselect 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_CODEdrop 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 行受影响)

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

tb1表里面的STOCK_QTY要减去tb2表里面对应ITEM_CODE的STOCK_QTY，结果跟我写出来的不一样啊

Answer 10

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

Answer 11

tb2表里面 没有 STOCK_QTY字段吧

Answer 12

是不是 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

Answer 13

呵呵  是的 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

Answer 14

 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')GOSELECT * 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

Answer 15

ta1
ITEM_CODE        LOCATION      STOCK_QTY      QTY_RATIO      CTM_QTY
61-78495-00080     CSP1         850              0.15          30tb2表：
SQL code
ITEM_CODE        LOCATION       SUMQTY        INDATEcreate 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)