之前的问题解决了,现在又遇到了新的麻烦:
现在有两张表
table1(销售商品表)
DECL_NO GOODS_NO GOODS_CODE BASE_PRICE
32001 1 090101 1500
32001 2 070401 2000
32002 1 010101 1000
32003 1 090101 1500
32004 1 050401 500
32005 1 070401 2000
32005 2 010101 1000
32005 3 030101 300table2(特惠商品表)
GOODS_CODE RATE
010101 50
030101 70
070401 50现在我想要的是
DECL_NO GOODS_NO GOODS_CODE BASE_PRICE ACT_RATE
32001 1 090101 1500 100
32001 2 070401 2000 50
32002 1 010101 1000 50
32003 1 090101 1500 100
32004 1 050401 500 100
32005 1 070401 2000 50
32005 2 010101 1000 100
32005 3 030101 300 70也就是说要确定实际折扣率。如果GOODS_CODE能在table2查到的话,ACT_RATE就取table2中的RATE值;如果GOODS_CODE在table2中查不到的话,ACT_RATE就填入100。
那么SQL语句应该怎么写?
现在有两张表
table1(销售商品表)
DECL_NO GOODS_NO GOODS_CODE BASE_PRICE
32001 1 090101 1500
32001 2 070401 2000
32002 1 010101 1000
32003 1 090101 1500
32004 1 050401 500
32005 1 070401 2000
32005 2 010101 1000
32005 3 030101 300table2(特惠商品表)
GOODS_CODE RATE
010101 50
030101 70
070401 50现在我想要的是
DECL_NO GOODS_NO GOODS_CODE BASE_PRICE ACT_RATE
32001 1 090101 1500 100
32001 2 070401 2000 50
32002 1 010101 1000 50
32003 1 090101 1500 100
32004 1 050401 500 100
32005 1 070401 2000 50
32005 2 010101 1000 100
32005 3 030101 300 70也就是说要确定实际折扣率。如果GOODS_CODE能在table2查到的话,ACT_RATE就取table2中的RATE值;如果GOODS_CODE在table2中查不到的话,ACT_RATE就填入100。
那么SQL语句应该怎么写?
from table1 a
left join table2 b
on a.goods_code=b.goods_code
go
create table [table1]([DECL_NO] int,[GOODS_NO] int,[GOODS_CODE] varchar(6),[BASE_PRICE] int)
insert [table1]
select 32001,1,'090101',1500 union all
select 32001,2,'070401',2000 union all
select 32002,1,'010101',1000 union all
select 32003,1,'090101',1500 union all
select 32004,1,'050401',500 union all
select 32005,1,'070401',2000 union all
select 32005,2,'010101',1000 union all
select 32005,3,'030101',300
if object_id('[table2]') is not null drop table [table2]
go
create table [table2]([GOODS_CODE] varchar(6),[RATE] int)
insert [table2]
select '010101',50 union all
select '030101',70 union all
select '070401',50--select * from [table1]
--select * from [table2]select a.*,isnull(b.rate,100) ACT_RATE
from table1 a left join table2 b
on a.GOODS_CODE=b.GOODS_CODE
--测试结果:
/*
DECL_NO GOODS_NO GOODS_CODE BASE_PRICE ACT_RATE
----------- ----------- ---------- ----------- -----------
32001 1 090101 1500 100
32001 2 070401 2000 50
32002 1 010101 1000 50
32003 1 090101 1500 100
32004 1 050401 500 100
32005 1 070401 2000 50
32005 2 010101 1000 50
32005 3 030101 300 70(8 行受影响)*/
left join table2 b on a.goods_code=b.goods_code
from table1 m left join table2 n
on m.GOODS_CODE = n.GOODS_CODE
from table1 a
left join table2 b
on a.goods_code=b.goods_code
insert [table1]
select 32001,1,'090101',1500 union all
select 32001,2,'070401',2000 union all
select 32002,1,'010101',1000 union all
select 32003,1,'090101',1500 union all
select 32004,1,'050401',500 union all
select 32005,1,'070401',2000 union all
select 32005,2,'010101',1000 union all
select 32005,3,'030101',300
create table [table2]([GOODS_CODE] varchar(6),[RATE] int)
insert [table2]
select '010101',50 union all
select '030101',70 union all
select '070401',50
goselect m.* , isnull(n.RATE,100) ACT_RATE
from table1 m left join table2 n
on m.GOODS_CODE = n.GOODS_CODEdrop table table1 , table2/*
DECL_NO GOODS_NO GOODS_CODE BASE_PRICE ACT_RATE
----------- ----------- ---------- ----------- -----------
32001 1 090101 1500 100
32001 2 070401 2000 50
32002 1 010101 1000 50
32003 1 090101 1500 100
32004 1 050401 500 100
32005 1 070401 2000 50
32005 2 010101 1000 50
32005 3 030101 300 70(所影响的行数为 8 行)*/
from table1 A left outer join table2 B
on (A.GOODS_CODE =b.GOODS_CODE )
insert [table1]
select 32001,1,'090101',1500 union all
select 32001,2,'070401',2000 union all
select 32002,1,'010101',1000 union all
select 32003,1,'090101',1500 union all
select 32004,1,'050401',500 union all
select 32005,1,'070401',2000 union all
select 32005,2,'010101',1000 union all
select 32005,3,'030101',300
create table [table2]([GOODS_CODE] varchar(6),[RATE] int)
insert [table2]
select '010101',50 union all
select '030101',70 union all
select '070401',50
go
select a.*,ACT_RATE=isnull(b.rate,100)
from table1 a
left join table2 b
on a.goods_code=b.goods_code
----------- ----------- ---------- ----------- -----------
32001 1 090101 1500 100
32001 2 070401 2000 50
32002 1 010101 1000 50
32003 1 090101 1500 100
32004 1 050401 500 100
32005 1 070401 2000 50
32005 2 010101 1000 50
32005 3 030101 300 70(所影响的行数为 8 行)
,ACT_RATE = isnull(b.GOODS_CODE,100)
from table1 a
left join table2 b on a.GOODS_CODE = b.GOODS_CODE
select a.*
,ACT_RATE = isnull(b.rate,100)
from table1 a
left join table2 b on a.GOODS_CODE = b.GOODS_CODE/*
DECL_NO GOODS_NO GOODS_CODE BASE_PRICE ACT_RATE
----------- ----------- ---------- ----------- -----------
32001 1 090101 1500 100
32001 2 070401 2000 50
32002 1 010101 1000 50
32003 1 090101 1500 100
32004 1 050401 500 100
32005 1 070401 2000 50
32005 2 010101 1000 50
32005 3 030101 300 70(8 row(s) affected)*/
insert tb
select 32001,1,'090101',1500 union all
select 32001,2,'070401',2000 union all
select 32002,1,'010101',1000 union all
select 32003,1,'090101',1500 union all
select 32004,1,'050401',500 union all
select 32005,1,'070401',2000 union all
select 32005,2,'010101',1000 union all
select 32005,3,'030101',300
create table tb1 ([GOODS_CODE] varchar(6),[RATE] int)
insert tb1
select '010101',50 union all
select '030101',70 union all
select '070401',50
go
select m.* , isnull(n.RATE,100) ACT_RATE
from tb m left join tb1 n
on m.GOODS_CODE = n.GOODS_CODE
insert tb
select 32001,1,'090101',1500 union all
select 32001,2,'070401',2000 union all
select 32002,1,'010101',1000 union all
select 32003,1,'090101',1500 union all
select 32004,1,'050401',500 union all
select 32005,1,'070401',2000 union all
select 32005,2,'010101',1000 union all
select 32005,3,'030101',300
create table tb2([GOODS_CODE] varchar(6),[RATE] int)
insert tb2
select '010101',50 union all
select '030101',70 union all
select '070401',50
goselect m.* , isnull(n.RATE,100) ACT_RATE
from tb m left join tb2 n
on m.GOODS_CODE = n.GOODS_CODE