mysql数据库多表查询⽰例_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 < n.date)
实例
ospf和静态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>
parentelementchild
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 < SOME
indexof和includes4> (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
mysql语句多表查询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')
本⽂原创发布php中⽂⽹，转载请注明出处，感谢您的尊重！

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。