SQL相关子查询的例子

select * from ra_provide order by productid,providerid

--ra_provide表
002    0001    150.00
001    0002    185.00
002    0002    100.00
003    0002    145.00
002    0005    230.00
001    0010    370.00
001    0014    87.00
002    0014    90.00
002    0016    300.00
001    0025    200.00
003    0025    213.00
002    0028    210.00
003    0028    185.00
003    0029    0.00

select * from jc_provider

001    厂商1    zyd
002    厂商2    zyd
003    厂商3   

--查询所有供应商都能提供的商品
select distinct productid from ra_provide a
where not exists
(
    select providerid from jc_provider b
    where not exists
        (select * from ra_provide c where a.productid=c.productid and b.providerid=c.providerid
        )
)

--选出各ID中的h_id最大的一个！
Create Table TEST(ID    Int Identity(1,1), h_id    Int)
Insert TEST Select 100 Union All Select 100 Union All Select 100
Union All Select 107
Union All Select 101
Union All Select 109
Union All Select 108
select * from test order by h_id
--GO--方法一：
Select * From TEST A Where Id In(Select TOP 3 ID From TEST Where h_id=A.h_id) order by 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 BInner Join (Select Max(Rq) as Rq,HH From @t Group By hh) AOn A.Rq=B.Rq--用双主键标识啊！Drop Table TESTGO/*ID    h_id1    1002    1003    1004    1015    1016    101*/
-------------------------------------

create table xx
(name char(10),
class char(10),
chengji float,
primary key (name,class)
)
insert into xx
select 'zhang','wuli',90.5 Union All
select 'li','shuxue',60 Union All
select 'li','zhengzhi',70
select * from xx

select name,class,chengji from xx
select name,class,chengji from xx a where
chengji in
(select max(chengji) cj from xx where name=a.name)

select * from xx
select * from xx a
where exists
(select avg(chengji) from xx        --76.36
having avg(chengji)<=a.chengji        --针对聚合函数的条件一定要用having
)

转载于:https://www.cnblogs.com/edong/archive/2011/01/19/1939262.html