典型的相关子查询

最新推荐文章于 2023-06-20 12:00:56 发布

fk_baker

最新推荐文章于 2023-06-20 12:00:56 发布

阅读量1k

点赞数

文章标签： insert table date 产品 null join

本文链接：https://blog.csdn.net/fk_baker/article/details/1543691

版权

--相关子查询与普通子查询的区别在于：相关子查询引用了外部查询中的列！这种用外部查询来限制子查询的方法使

SQL查询变得更加强大和灵活。因为相关子查询能够引用外部查询，所以它们尤其适合编写复杂的where条件！

相关子查询不能自己单独运行，其执行顺序如下：

1.首先执行一次外部查询

2.对于外部查询中的每一行分别执行一次子查询，而且每次执行子查询时都会引用外部查询中当前行的值。

3.使用子查询的结果来确定外部查询的结果集。

如果外部查询返回100行，SQL 就将执行101次查询，一次执行外部查询，然后为外部查询返回的每一行执行一次子查询。但实际上，SQL的查询

优化器有可能会找到一种更好的方法来执行相关子查询，而不需要实际执行101次查询。

--选出各ID中的h_id最大的一个！

Create Table TEST(ID	Int Identity(1,1), h_id	Int)Insert TEST Select 100Union All Select 100Union All Select 100Union All Select 101Union All Select 101Union All Select 101Union All Select 100GO--方法一：Select * From TEST A Where Id In(Select TOP 3 ID From TEST Where h_id=A.h_id)--方法二：Select * From TEST A Where Not Exists (Select 1 From TEST Where h_id=A.h_id And ID<A.ID Having Count(*)>2)--方法三：Select * From TEST A Where (Select Count(*) From TEST Where h_id=A.h_id And ID<A.ID)<3GO

--方法四

Select * From @t B
Inner Join (Select Max(Rq) as Rq,HH From @t Group By hh) A
On A.Rq=B.Rq--用双主键标识啊！Drop Table TESTGO/*ID	h_id1	1002	1003	1004	1015	1016	101*/

-------------------------------------

declare @t table(inv_date varchar(10),inv_in int,inv_ou int,inv_stc int)
insert into @t select '2006-05-01',100, 50,null
insert into @t select '2006-05-02', 80, 30,null
insert into @t select '2006-05-03',100, 40,null
insert into @t select '2006-05-04',200,150,null

update a
set
    inv_stc=1000+(select sum(inv_in-inv_ou) from @t where inv_date<=a.inv_date)
from
    @t a

select * from @t
/*
inv_date   inv_in      inv_ou      inv_stc
---------- ----------- ----------- -----------
2006-05-01 100         50          1050
2006-05-02 80          30          1100
2006-05-03 100         40          1160
2006-05-04 200         150         1210
*/

-------------过滤重复记录

declare @t table(rq varchar(10),hh int,ye dec(6,2))
insert into @t select '2006-01-02'    ,1111    ,2.01
union all select '2006-01-05'    ,1111    ,3.51
union all select '2006-01-10'    ,1111    ,2.55
union all select '2006-01-02'    ,2222    ,3.00
union all select '2006-01-04'    ,2222    ,2.00
union all select '2006-01-05'    ,3333    ,6.54
union all select '2006-01-06'    ,3333    ,5.23
union all select '2006-01-07'    ,3333    ,8.55

select * from @t a where not exists(select 1 from @t where hh=a.hh and rq>a.rq)

另解：（用连接）

Select * From @t A
Inner Join (Select Max(rq) as rq,hh From @t Group by hh)B
On A.hh=B.hh and A.rq=B.rq

--另一实例

基础信息表(t1)
id varchar(3)   name varchar(10)
   01                冷钢
　 02                钢板
　 03                扁钢
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
入库表(t2)
   mc varchar(20)名称　　　sl int 数量　　　rq 日期 datetime
　　　　　冷板　　　　　　　　　20              2006-1-1
          冷板　　　　　　　　　10              2006-1-1
          冷板　　　　　　　　　30              2006-1-2
          钢板　　　　　　　　　50              2006-1-1
          扁钢　　　　　　　　　20              2006-1-2
-----------------------------------------------------------------
出库表(t3)
mc varchar(20)名称　　　 sl int 数量　　   rq 日期　datetime
         冷板　　　　　　　　　５　　　　　　　 2006-1-1
         冷板　　　　　　　　　３　　　　　　　 2006-1-2
         钢板　　　　　　　　　２　　　　　　　 2006-1-1
         钢板　　　　　　　　　１　　　　　　　 2006-1-2
         扁钢　　　　　　　　　 4               2006-1-1
         扁钢　　　　　　　　　３　　　　　　　 2006-1-2
-------------------------------------------------------------------
/*注意：每种板材都有100的期初库存数*/

生成的报表为：

　名称            入库数量　　　　出库数量　　　　结余数量　　　日期
　冷板　　　　　　　30               5               125        2006-1-1
钢板　　　　　　　50               2               148        2006-1-1
扁钢　　　　　　　０　　　　　　　 4                96        2006-1-1
-------------------------------------------------------------------------
以上为2006-1-1的数据，2006-1-2的数据与此类似，是通过日期来查询每一天的库存情况
---------------------
select
    名称    = name
    入库数量=isnull((select sum(sl) from t2 where mc=t1.name and rq<='2006-01-01'),0),
    出库数量=isnull((select sum(sl) from t2 where mc=t1.name and rq<='2006-01-01'),0),
    结余数量=100+isnull((select sum(sl) from t2 where mc=t1.name and rq<='2006-01-01'),0)
               -isnull((select sum(sl) from t2 where mc=t1.name and rq<='2006-01-01'),0),
    日期    ='2006-01-01'
from
    t1

以上清楚地可以看到相关子查询的执行顺序！以上例来说，先从T1表中找到3条数据，以这三条数据为条件，依次查询T2和T3表中的表的内容！
例 3：

一个出货表
产品    出货日期   数量
A       2006-2-5    3
A       2006-2-12   2
A       2006-3-1    5
A       2006-3-5    4
B       2006-6-5    6
B       2006-7-1    7
B       2006-8-25   8

一个价格表
产品    定价日期   价格
A       2006-2-1   150
A       2006-3-1   160
A       2006-4-1   170
B       2006-6-1   180
B       2006-7-1   190
B       2006-8-2   140

现在我想在出货表的基础上查询出每个产品出货时的价格，价格来源于价格表中与该产品对应的最近的定价日期的价格。

即我想得到如下的结果：
产品    出货日期   数量   价格
A       2006-2-5    3     150
A       2006-2-12   2     150
A       2006-3-1    5     160
A       2006-3-5    4     160
B       2006-6-5    6     180
B       2006-7-1    7     190
B       2006-8-25   8     140

Declare @t Table(Prod Char,Rq Datetime,Qty Int )
Insert @t Select 'A','2006-2-5',3
Union all Select 'A','2006-2-12',2
Union all Select 'A','2006-3-1',5
Union all Select 'A','2006-3-5',4
Union all Select 'B','2006-6-5',6
Union all Select 'B','2006-7-1',7
Union all Select 'B','2006-8-25',8

Declare @t1 Table(Prod Char,Rq Datetime,Price Int)
Insert @t1 Select 'A','2006-2-1',150
Union all Select 'A','2006-3-1',160
Union all Select 'A','2006-4-1',170
Union all Select 'B','2006-6-1',180
Union all Select 'B','2006-7-1',190
Union all Select 'B','2006-8-2',140

Select *,
Price=(Select Price From @t1 Where Prod=A.Prod and Rq=(Select Top 1 rq From @t1 order by abs(datediff(day,rq,a.rq))))
From @t A