SELECT A.OrderId FROM tb_FuturesOrder A 得到结果表甲OrderId
-----------
A
B
C
D
ESELECT OrderID from tb_FuturesOrder F,tb_Product P
where F.model=P.productModel and F.Batch=P.Batch 得到表乙OrderId
---------
B
D
E如果得到以下效果是一个新的表甲,如果他的项在表乙中存在,后面就带个1,没有的就带个0 如下:OrderId Exists
-----------------
A 0
B 1
C 0
D 1
E 1
-----------
A
B
C
D
ESELECT OrderID from tb_FuturesOrder F,tb_Product P
where F.model=P.productModel and F.Batch=P.Batch 得到表乙OrderId
---------
B
D
E如果得到以下效果是一个新的表甲,如果他的项在表乙中存在,后面就带个1,没有的就带个0 如下:OrderId Exists
-----------------
A 0
B 1
C 0
D 1
E 1
FROM tb_FuturesOrder F LEFT JOIN tb_Product P
ON F.model=P.productModel and F.Batch=P.Batch
FROM tb_FuturesOrder F LEFT JOIN tb_Product P
ON F.model=P.productModel and F.Batch=P.Batch
SELECT A.OrderID,[EXISTS]=(CASE WHEN ISNULL(b.ORDERID,0)=0 THEN 0 ElSE 1 END)
FROM tb_FuturesOrder A LEFT JOIN (SELECT OrderID from tb_FuturesOrder F,tb_Product P
where F.model=P.productModel and F.Batch=P.Batch) B
ON A.OrderID=B.OrderID
-- Author: happyflystone
-- Version:V1.001
-- Date:2008-09-13 15:24:29
-------------------------------------- Test Data: tb_FuturesOrder
If object_id('tb_FuturesOrder') is not null
Drop table tb_FuturesOrder
Go
Create table tb_FuturesOrder(OrderId nvarchar(11),Model int,Batch int)
Go
Insert into tb_FuturesOrder
select 'A',1,1 union all
select 'B',1,1 union all
select 'C',1,1 union all
select 'D',1,1 union all
select 'E',1,1
Go-- Test Data: tb_Product
If object_id('tb_Product') is not null
Drop table tb_Product
Go
Create table tb_Product(productModel int,Batch int,OrderID nvarchar(1))
Go
Insert into tb_Product
select 1,1,'B' union all
select 1,1,'D' union all
select 1,1,'E'
Go
--Start
SELECT A.OrderId ,
[exists]=case when exists( SELECT 1 from tb_Product P
where a.model=P.productModel and a.Batch=P.Batch
and a.orderid = p.orderid) then 1 else 0 end
FROM tb_FuturesOrder A SELECT f. OrderID,
[exists]=case when p.orderid = f.orderid then 1 else 0 end
from tb_FuturesOrder F
full join tb_Product P
on F.model=P.productModel and F.Batch=P.Batch and f.orderid = p.orderid
--Result:
/*OrderId exists
----------- -----------
A 0
B 1
C 0
D 1
E 1(所影响的行数为 5 行)OrderId exists
----------- -----------
A 0
B 1
C 0
D 1
E 1(所影响的行数为 5 行)
*/
--End