select * from order where id in(select orderid from product where id in(select ProduceID from Invoice where date>='2013-01-01' and date<='2013-01-05' and arrival='1' ) )
是这样吗: select * from order o where exists(select 1 from product p where o.id = p.id and exists(select 1 from Invoice i where date>='2013-01-01' and date<='2013-01-05' and arrival='1' and i.ProduceID = p.ProduceID ) )
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2013-11-14 14:44:57 -- Verstion: -- Microsoft SQL Server 2012 - 11.0.2100.60 (X64) -- Feb 10 2012 19:39:15 -- Copyright (c) Microsoft Corporation -- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[Order] if object_id('[Order]') is not null drop table [Order] go create table [Order]([ID] int,[OrderNo] varchar(6)) insert [Order] select 1,'O-0101' union all select 2,'O-0102' union all select 3,'O-0103' union all select 4,'O-0104' --> 测试数据:[Product] if object_id('[Product]') is not null drop table [Product] go create table [Product]([ID] int,[OrderID] int,[ProductName] varchar(4)) insert [Product] select 1,1,'苹果' union all select 2,1,'香蕉' union all select 3,1,'梨' union all select 4,2,'菠萝' union all select 5,2,'榴莲' --> 测试数据:[Invoice] if object_id('[Invoice]') is not null drop table [Invoice] go create table [Invoice]([ID] int,[ProduceID] int,[Date] datetime,[Arrival] int) insert [Invoice] select 1,1,'2013-01-01',1 union all select 2,1,'2013-01-02',1 union all select 3,1,'2013-01-03',1 union all select 4,2,'2013-01-04',0 union all select 5,2,'2013-01-05',1 --------------开始查询-------------------------- SELECT DISTINCT a.OrderNo FROM [Order] a INNER JOIN [Product] b ON a.id=b.OrderID INNER JOIN Invoice AS c ON b.id=c.ProduceID WHERE NOT EXISTS ( SELECT 1 FROM Invoice WHERE Arrival=1 AND id=c.id ) ----------------结果---------------------------- /* OrderNo ------- O-0101(1 行受影响) */
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2013-11-14 14:44:57 -- Verstion: -- Microsoft SQL Server 2012 - 11.0.2100.60 (X64) -- Feb 10 2012 19:39:15 -- Copyright (c) Microsoft Corporation -- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[Order] if object_id('[Order]') is not null drop table [Order] go create table [Order]([ID] int,[OrderNo] varchar(6)) insert [Order] select 1,'O-0101' union all select 2,'O-0102' union all select 3,'O-0103' union all select 4,'O-0104' --> 测试数据:[Product] if object_id('[Product]') is not null drop table [Product] go create table [Product]([ID] int,[OrderID] int,[ProductName] varchar(4)) insert [Product] select 1,1,'苹果' union all select 2,1,'香蕉' union all select 3,1,'梨' union all select 4,2,'菠萝' union all select 5,2,'榴莲' --> 测试数据:[Invoice] if object_id('[Invoice]') is not null drop table [Invoice] go create table [Invoice]([ID] int,[ProduceID] int,[Date] datetime,[Arrival] int) insert [Invoice] select 1,1,'2013-01-01',1 union all select 2,1,'2013-01-02',1 union all select 3,1,'2013-01-03',1 union all select 4,2,'2013-01-04',0 union all select 5,2,'2013-01-05',1 --------------开始查询-------------------------- SELECT DISTINCT a.OrderNo FROM [Order] a INNER JOIN [Product] b ON a.id=b.OrderID INNER JOIN Invoice AS c ON b.id=c.ProduceID WHERE NOT EXISTS ( SELECT 1 FROM Invoice WHERE Arrival=1 AND id=c.id AND [Date]>='2013-01-01' and [Date]<='2013-01-05') ----------------结果---------------------------- /* OrderNo ------- O-0101(1 行受影响) */
select * from [Order] where ID IN( select OrderID from Product where ID IN( select distinct ProduceID from Invoice p where Date>='2013-1-1' and Date<='2013-1-5' and not exists(select 1 from Invoice where ProduceID=p.ProduceID and Arrival=0)))
select * from [order] a join product b on a.id=b.orderid join (select SUM(flag) flag,produceID from (select case when arrival=0 then 1 else 0 end flag, produceID from invoice where [Date]>='2013-01-01' and [Date]<='2013-01-05') a group by produceID) c on b.id=c.produceID where flag=0
select * from [order] a join product b on a.id=b.orderid join (select SUM(flag) flag,produceID from (select case when arrival=0 then 1 else 0 end flag, produceID from invoice where [Date]>='2013-01-01' and [Date]<='2013-01-05') a group by produceID) c on b.id=c.produceID where flag=0
select a.* from [Order] a ,( select b.OrderID from [Product] b where b.ID in ( select ProduceID from Invoice where Date >= '2013-01-01' and Date <= '2013-01-05' and Arrival =1 ) group by b.OrderID ) tb where a.id=tb.OrderID
--大家没有留意楼主的: 只要Invoice里面有一条数据不满足条件,Order表的相应数据就被筛选掉! select * from [Order] a inner join (select OrderID,min(Arrival) as Arrival from Product b inner join Invoice c on b.id=c.ProduceID where AND [Date]>='2013-01-01' and [Date]<='2013-01-05') group by OrderID ) d on a.id=d.OrderID where d.Arrival=1
where id in(select orderid from product
where id in(select ProduceID from Invoice
where date>='2013-01-01'
and date<='2013-01-05'
and arrival='1'
)
)
select * from order o
where exists(select 1 from product p
where o.id = p.id
and exists(select 1 from Invoice i
where date>='2013-01-01'
and date<='2013-01-05'
and arrival='1'
and i.ProduceID = p.ProduceID
)
)
-- Author :fredrickhu(小F,向高手学习)
-- Date :2013-11-14 14:44:57
-- Verstion:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
-- Feb 10 2012 19:39:15
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[Order]
if object_id('[Order]') is not null drop table [Order]
go
create table [Order]([ID] int,[OrderNo] varchar(6))
insert [Order]
select 1,'O-0101' union all
select 2,'O-0102' union all
select 3,'O-0103' union all
select 4,'O-0104'
--> 测试数据:[Product]
if object_id('[Product]') is not null drop table [Product]
go
create table [Product]([ID] int,[OrderID] int,[ProductName] varchar(4))
insert [Product]
select 1,1,'苹果' union all
select 2,1,'香蕉' union all
select 3,1,'梨' union all
select 4,2,'菠萝' union all
select 5,2,'榴莲'
--> 测试数据:[Invoice]
if object_id('[Invoice]') is not null drop table [Invoice]
go
create table [Invoice]([ID] int,[ProduceID] int,[Date] datetime,[Arrival] int)
insert [Invoice]
select 1,1,'2013-01-01',1 union all
select 2,1,'2013-01-02',1 union all
select 3,1,'2013-01-03',1 union all
select 4,2,'2013-01-04',0 union all
select 5,2,'2013-01-05',1
--------------开始查询--------------------------
SELECT DISTINCT
a.OrderNo
FROM
[Order] a
INNER JOIN [Product] b ON a.id=b.OrderID
INNER JOIN Invoice AS c ON b.id=c.ProduceID
WHERE
NOT EXISTS ( SELECT
1
FROM
Invoice
WHERE
Arrival=1
AND id=c.id )
----------------结果----------------------------
/* OrderNo
-------
O-0101(1 行受影响)
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2013-11-14 14:44:57
-- Verstion:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
-- Feb 10 2012 19:39:15
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[Order]
if object_id('[Order]') is not null drop table [Order]
go
create table [Order]([ID] int,[OrderNo] varchar(6))
insert [Order]
select 1,'O-0101' union all
select 2,'O-0102' union all
select 3,'O-0103' union all
select 4,'O-0104'
--> 测试数据:[Product]
if object_id('[Product]') is not null drop table [Product]
go
create table [Product]([ID] int,[OrderID] int,[ProductName] varchar(4))
insert [Product]
select 1,1,'苹果' union all
select 2,1,'香蕉' union all
select 3,1,'梨' union all
select 4,2,'菠萝' union all
select 5,2,'榴莲'
--> 测试数据:[Invoice]
if object_id('[Invoice]') is not null drop table [Invoice]
go
create table [Invoice]([ID] int,[ProduceID] int,[Date] datetime,[Arrival] int)
insert [Invoice]
select 1,1,'2013-01-01',1 union all
select 2,1,'2013-01-02',1 union all
select 3,1,'2013-01-03',1 union all
select 4,2,'2013-01-04',0 union all
select 5,2,'2013-01-05',1
--------------开始查询--------------------------
SELECT DISTINCT
a.OrderNo
FROM
[Order] a
INNER JOIN [Product] b ON a.id=b.OrderID
INNER JOIN Invoice AS c ON b.id=c.ProduceID
WHERE
NOT EXISTS ( SELECT
1
FROM
Invoice
WHERE
Arrival=1
AND id=c.id AND [Date]>='2013-01-01'
and [Date]<='2013-01-05')
----------------结果----------------------------
/* OrderNo
-------
O-0101(1 行受影响)
*/
where ID IN(
select OrderID from Product where ID IN(
select distinct ProduceID from Invoice p where Date>='2013-1-1'
and Date<='2013-1-5' and not exists(select 1 from Invoice
where ProduceID=p.ProduceID and Arrival=0)))
from [order] a
join product b on a.id=b.orderid
join (select SUM(flag) flag,produceID
from (select case when arrival=0 then 1
else 0 end flag,
produceID
from invoice
where [Date]>='2013-01-01' and
[Date]<='2013-01-05') a
group by produceID) c on b.id=c.produceID
where flag=0
select *
from [order] a
join product b on a.id=b.orderid
join (select SUM(flag) flag,produceID
from (select case when arrival=0 then 1
else 0 end flag,
produceID
from invoice
where [Date]>='2013-01-01' and
[Date]<='2013-01-05') a
group by produceID) c on b.id=c.produceID
where flag=0
select b.OrderID from [Product] b
where b.ID in
(
select ProduceID from
Invoice
where Date >= '2013-01-01' and Date <= '2013-01-05' and Arrival =1
)
group by b.OrderID
) tb
where a.id=tb.OrderID
--大家没有留意楼主的: 只要Invoice里面有一条数据不满足条件,Order表的相应数据就被筛选掉!
select * from [Order] a
inner join (select OrderID,min(Arrival) as Arrival
from Product b inner join Invoice c on b.id=c.ProduceID
where AND [Date]>='2013-01-01' and [Date]<='2013-01-05')
group by OrderID ) d on a.id=d.OrderID
where d.Arrival=1