1.单句SQL语句，从视图中查询结果
select c.product_no, c.product_flag
from
( select a.product_flag,b.product_no from product_category as a join product as b on (a.product_category_no=b.product_type)) as c
where c.product_no=8
( select a.product_flag,b.product_no from product_category as a join product as b on (a.product_category_no=b.product_type)) as c
where c.product_no=8
2.根据“年费”查询多个字段
select product_no,product_property_no,product_property_detail_content
from product_property_detail
where product_property_name="年费" and (product_property_detail_content between 360 and 500)
where product_property_name="年费" and (product_property_detail_content between 360 and 500)
3.子查询转化“编号”为“名字”
select product_no,product_name,creditcard_rating,
( select bank.bank_name from bank where bank.bank_no=product.company_no) as company_no,product_desc
from product where product_type=12 and company_no= 2
( select bank.bank_name from bank where bank.bank_no=product.company_no) as company_no,product_desc
from product where product_type=12 and company_no= 2
4.联合查询得详细信息
SELECT
( select product_property_detail_content from product_property_detail where product_no=8 and product_property_no=30) as dhsx,
product_function as jbgn,
(( select c.product_flag from ( select a.product_flag,b.product_no from product_category as a join product as b on (a.product_category_no=b.product_type)) as c where c.product_no=8 ) ) as cplb
FROM product where product_no=8
( select product_property_detail_content from product_property_detail where product_no=8 and product_property_no=30) as dhsx,
product_function as jbgn,
(( select c.product_flag from ( select a.product_flag,b.product_no from product_category as a join product as b on (a.product_category_no=b.product_type)) as c where c.product_no=8 ) ) as cplb
FROM product where product_no=8
5.union操作
select product_no
from product_property_detail
where product_property_no=3 and (product_property_detail_content between 360 and 500)
union all
select product_no from product where product_type=12 and company_no= 2
order by product_no
where product_property_no=3 and (product_property_detail_content between 360 and 500)
union all
select product_no from product where product_type=12 and company_no= 2
order by product_no
6.多条件查询
select (
select product_property_name
from product_property
as a
where a.product_property_no=product_property_detail.product_property_no)
as name,product_property_detail_content as jbgn
from product_property_detail where product_no=8 and
(product_property_no=1 or product_property_no=13 or product_property_no=14 or product_property_no=15 )
as name,product_property_detail_content as jbgn
from product_property_detail where product_no=8 and
(product_property_no=1 or product_property_no=13 or product_property_no=14 or product_property_no=15 )
7.in语句
select product_no
from product
where product_no
in
( select product_no from product where creditcard_rating="金卡")
in
( select product_no from product where creditcard_rating="金卡")
8.求合集
select *
from (
select product_no
from product_property_detail
where product_property_no=3 and (product_property_detail_content between 360 and 500) ) as a
inner join
( select product_no from product where product_type=12 and company_no= 2 ) as b
on a.product_no=b.product_no
where product_property_no=3 and (product_property_detail_content between 360 and 500) ) as a
inner join
( select product_no from product where product_type=12 and company_no= 2 ) as b
on a.product_no=b.product_no
9.Exists better than In
背景介绍：product_property_detail表中 product_property_no为种类， product_no为商品标号，他们为多对多关系，主键为： product_property_detail_no，该表只有种类和商品号确定才能找到该唯一的记录号！
测试：找每种种类中商品标号最大的那个商品
方案一：In方式
select product_property_no,product_no,product_name
from product_property_detail a
where product_no in (
select max(product_no) from product_property_detail
where product_property_no = a.product_property_no
)
where product_no in (
select max(product_no) from product_property_detail
where product_property_no = a.product_property_no
)
结果：
42 rows
in
set (40.10 sec)
方案二：Exists方式
select product_property_no,product_no,product_name
from product_property_detail a
where not exists (
select 1 from product_property_detail
where product_property_no = a.product_property_no and
product_no > a.product_no
)
where not exists (
select 1 from product_property_detail
where product_property_no = a.product_property_no and
product_no > a.product_no
)
结果：
42 rows
in
set (14.69 sec)
小结：
在查找复杂关系的数据时候Exists会有更好的效率！
本文转自danni505 51CTO博客，原文链接：http://blog.51cto.com/danni505/105616，如需转载请自行联系原作者
