sql多表联合查询二个查询实例

sql多表联合查询二个查询实例

先看常用的查询

两表结构不一样
m.* , n.* from t1 m, t2 n where m.id = n.id
and n.date = (select max(date) from t2 where id = n.id)

select m.* , n.* from t1 m, t2 n where m.id = n.id
and not exists (select 1 from t2 where id = n.id and date > n.date)

select m.* , n.* from t1 m, t2 n where m.id = n.id
and n.date = (select min(date) from t2 where id = n.id)

select m.* , n.* from t1 m, t2 n where m.id = n.id
and not exists (select 1 from t2 where id = n.id and date

实例

2>
3> CREATE TABLE stores(
4>    stor_id        char(4)           NOT NULL,
5>    stor_name      varchar(40)           NULL,
6>    stor_address   varchar(40)           NULL,
7>    city           varchar(20)           NULL,
8>    state          char(2)               NULL,
9>    zip            char(5)               NULL
10> )
11> GO
1> insert stores values('1','B','567 Ave.','Tustin',   'CA','92789')
2> insert stores values('2','N','577 St.', 'Los Gatos','CA','96745')
3> insert stores values('3','T','679 St.', 'Portland', 'OR','89076')
4> insert stores values('4','F','89  St.', 'Fremont',  'CA','90019')
5> GO

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
1>
2>
3> CREATE TABLE discounts(
4>    discounttype   varchar(40)       NOT NULL,
5>    stor_id        char(4) NULL              ,
6>    lowqty         smallint              NULL,
7>    highqty        smallint              NULL,
8>    discount       dec(4,2)          NOT NULL
9> )
10> GO
1>
2> insert discounts values('Initial Customer',  NULL,   NULL, NULL, 10.5)
3> insert discounts values('Volume Discount',   NULL,   100,  1000, 6.7)
4> insert discounts values('Customer Discount', '8042', NULL, NULL, 5.0)
5> GO

(1 rows affected)

(1 rows affected)

(1 rows affected)
1>    SELECT stor_id AS "Store ID", stor_name AS "Store Name"
2>    FROM stores
3>    WHERE stor_id 4>    (SELECT stor_id FROM discounts WHERE stor_id IS NOT NULL)
5> GO
Store ID Store Name
-------- ----------------------------------------
1        B
2        N
3        T
4        F

(4 rows affected)
1>
2> drop table stores;
3> drop table discounts;
4> GO
1>

some语法

4>    SELECT stor_id AS "Store ID", stor_name AS "Store Name"
5>    FROM stores
6>    WHERE stor_id != SOME
7>       (SELECT stor_id FROM discounts WHERE stor_id IS NOT NULL)
8> GO

. create table #A(id int) go insert into #A s(1) insert into #A s(2) insert into #A s(3) insert into #A s(4) go --All：
对所有数据都满足条件，整个条件才成立，
例如：5大于所有返回的id
select * from #A where 5>All(select id from #A) go --Any：
只要有一条数据满足条件，整个条件成立，
例如：3大于1，2 select * from #A where 3>any(select id from #A) go

--Some和Any一样

详细

--分组取其中某字段最小，去重复
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([EID] varchar(2),[OID] varchar(2),[Value] int)
insert [tb]
select 'E1','O1',4 union all
select 'E2','O2',16 union all
select 'E3','O1',5 union all
select 'E4','O2',8 union all
select 'E5','O1',3 union all
select 'E6','O3',9

select t1.* from tb t1
where  EID  = (
    select top 1 t2. EID  from tb t2
    where t2.Value = (
        select min(t3.Value) from tb t3
        where t2.EID=t3.EID 
    ) and t1.OID=t2.OID
)
and  t1.EID in ('E1','E2','E4')