表ACARDCODE NUM PRICE
1111
2222
3000 5 100
4444
5555
表BCARDCODE NUM PRICE
1000 2 200
2000 3 200
3000
4000 1 300
5000 2 400
我想取到的是 SELECT CARDCODE,(NUM*PRICE) FROM B
当CARDCODE = 3000的时候,取表A里的PRICE。这个要怎么写???
1111
2222
3000 5 100
4444
5555
表BCARDCODE NUM PRICE
1000 2 200
2000 3 200
3000
4000 1 300
5000 2 400
我想取到的是 SELECT CARDCODE,(NUM*PRICE) FROM B
当CARDCODE = 3000的时候,取表A里的PRICE。这个要怎么写???
from 表a inner join 表b b on a.CARDCODE=b.CARDCODE or select b.CARDCODE ,case when b.CARDCODE=3000 then a.price else b.price end * b.num
from 表a inner join 表b b on a.CARDCODE=b.CARDCODE
from tbB b
(NUM*PRICE) as a FROM B where num is not null
union all
select CARDCODE,
(NUM*PRICE) from a
where CARDCODE in (select CARDCODE from b where num is null)
from b left jion a
on b.CARDCODE = a.CARDCODE
insert @a select
1111 , null,null union all select
2222 , null,null union all select
3000 , 5 , 100 union all select
4444 , null,null union all select
5555 , null,null declare @b table(CARDCODE int, NUM int, PRICE int )
insert @b select
1000 , 2 , 200 union all select
2000 , 3, 200 union all select
3000, null ,null union all select
4000 , 1, 300 union all select
5000 , 2, 400
select isnull(a.cardcode,b.cardcode)as cardcode,isnull(a.num,b.num)*isnull(a.price,b.price) as zz from @b b
left join @a a
on a.cardcode=b.cardcodecardcode zz
----------- -----------
1000 400
2000 600
3000 500
4000 300
5000 800(5 行受影响)
SELECT CARDCODE,
(NUM*PRICE) as a FROM B where num is not null
union all
select CARDCODE,
(NUM*PRICE)
from a
--> Title:生成測試數據
-->Author:wufeng4552【水族杰纶】
-->Environment: MSSQL2005
-->Date:2009-08-22
-->==============================================
if not object_id('ta') is null
drop table ta
Go
Create table ta([CARDCODE] int,[NUM] int,[PRICE] int)
Insert ta
select 1111,null,null union all
select 2222,null,null union all
select 3000,5,100 union all
select 4444,null,null union all
select 5555,null,null
Go
if not object_id('tb') is null
drop table tb
Go
Create table tb([CARDCODE] int,[NUM] int,[PRICE] int)
Insert tb
select 1000,2,200 union all
select 2000,3,200 union all
select 3000,null,null union all
select 4000,1,300 union all
select 5000,2,400
Go
select isnull(a.[CARDCODE],b.[CARDCODE]),
isnull(b.[NUM]*b.[PRICE],a.[NUM]*a.[PRICE])
from tb b
full join ta a
on a.[CARDCODE]=b.[CARDCODE]
/*
----------- -----------
1000 400
2000 600
3000 500
4000 300
5000 800
1111 NULL
2222 NULL
4444 NULL
5555 NULL(9 行受影响)*/
insert @a select
1111 , null,null union all select
2222 , null,null union all select
3000 , 5 , 100 union all select
4444 , null,null union all select
5555 , null,null declare @b table(CARDCODE int, NUM int, PRICE int )
insert @b select
1000 , 2 , 200 union all select
2000 , 3, 200 union all select
3000, null ,null union all select
4000 , 1, 300 union all select
5000 , 2, 400
SELECT CARDCODE,SUM(NUM)NUM FROM
(
SELECT CARDCODE,ISNULL(NUM,0)*ISNULL(PRICE,0)AS NUM FROM @A
UNION ALL
SELECT CARDCODE,ISNULL(NUM,0)*ISNULL(PRICE,0)AS NUM FROM @B
)AS T
GROUP BY CARDCODE (所影响的行数为 5 行)
(所影响的行数为 5 行)CARDCODE NUM
----------- -----------
1000 400
1111 0
2000 600
2222 0
3000 500
4000 300
4444 0
5000 800
5555 0(所影响的行数为 9 行)