求SQL语句，表A一条数据对表B多条数据，根据表B的条件筛选出表A

lcl736797997 2013-11-13 04:57:50

有表Order订单表
ID OrderNo
1 O-0101
2 O-0102
3 O-0103
4 O-0104

表Product 产品表
ID OrderID(Order表外键) ProductName
1 1 苹果
2 1 香蕉
3 1 梨
4 2 菠萝
5 2 榴莲

表Invoice 发票表（一个产品对应一条发票记录）
ID ProduceID(Product表ID) Date（日期） Arrival(是否到货)
1 1 2013-01-01 1
2 1 2013-01-02 1
3 1 2013-01-03 1
4 2 2013-01-04 0
5 2 2013-01-05 1

现在要筛选出Order订单表的数据，根据Invoice 发票表的时间在 2013-01-01-2013-01-05之间并且已经到货（Arrival为1）的Order订单数据，只要Invoice里面有一条数据不满足条件，Order表的相应数据就被筛选掉！
求写Sql语句

...全文

673 11 打赏收藏转发到动态举报

写回复

用AI写文章

11 条回复

切换为时间正序

请发表友善的回复…

发表回复

ycj80 2013-11-19

打赏
举报


--大家没有留意楼主的: 只要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

guduyishuai 2013-11-19

打赏
举报

引用 10 楼 changjiu_yang 的回复:


--大家没有留意楼主的: 只要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

当然注意到了

秃驴子 2013-11-18

打赏
举报

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

guduyishuai 2013-11-15

打赏
举报


  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

guduyishuai 2013-11-15

打赏
举报

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

kavaye 2013-11-14

打赏
举报

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)))

--小F-- 2013-11-14

打赏
举报

----------------------------------------------------------------
-- 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 行受影响)
*/

--小F-- 2013-11-14

打赏
举报

----------------------------------------------------------------
-- 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 行受影响)
*/

treemo 2013-11-14

打赏
举报

解决了？帮顶下

LongRui888 2013-11-13

打赏
举报

是这样吗:

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
                              )
            )

好基友一被子 2013-11-13

打赏
举报

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