（案例来自医药采购）
1.1 内链接关联查询：（一对一：只能查询到一条数据）
如果表A和表B有一个外键关联 （由于有外键可以通过内链接），可以通过外键进行内链接查询
select dictinfo.*,dicttype.typename
from dictinfo, dicttype
where dictinfo.typecode = dicttype.typecode
--不通过外键，通过groupid查询用户类型的代码结果集，只能查询出一条记录，可以使用内链接
selectsysuser.*, dictinfo.info
from sysuser,
(select dictcode, typecode, infofrom dictinfowhere typecode = 's01') dictinfo
where sysuser.groupid = dictinfo.dictcode
小结：如果主查询表字段从关联表只查询出一条记录，这个字段就可以作为内链接关联字段
--内链接的错误的例子，通过关联查询出重复记录
--使用groupid从select dictcode, typecode, info fromdictinfo可以找到多个记录，不能使用内链接，可能会出现重复记录
selectsysuser.*
from sysuser, (select dictcode, typecode, infofrom dictinfo) dictinfo
where sysuser.groupid = dictinfo.dictcode
注意：如果使用内链接查询出现重复记录，首先去思考是否是sql写错了，不能直接去使用distinct去除重复记录。
有一些特殊情况下还是需要使用distinct去除重复记录，比如复杂的统计分析sql。
1.2 外链接关联查询：
表A，表B中只有一部分数据和表A匹配，不能使用内链接。
主查询是表A，只能使用外链接。
--查询用户所属单位,sysid对应三张表的id
左外链接 left左边的为主查询表全部显示 右边为关联查询表（部分字段） on后边为要关联的字段
selectsysuser.*,useryy.mcfrom sysuserleftjoin useryyon sysuser.sysid =useryy.id
右外链接right右边主查询表全部显示左边为关联查询表（部分字段） on后边为要关联的字段
select *from useryyrightjoin sysuseron sysuser.sysid =useryy.id
--以上的需要不能使用内链接
selectsysuser.*,useryy.mcfrom sysuser,useryywhere sysuser.sysid = useryy.id
小结：
表A中从表B中只能关联查询一部分数据，只能使用外链接
left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
INNER JOIN 语法：
INNER JOIN 连接两个数据表的用法：
SELECT * FROM 表1 INNER JOIN 表2ON 表1.字段号=表2.字段号
inner join(等值连接) 只返回两个表中联结字段相等的行
1.3 子查询
selectsysuser.*,
(select * from useryywhereid = sysuser.sysid)
from sysuser
子查询只能返回一列，否则：报错
子查询只允许返回一行，否则：
正确的sql:
--子查询
--根据sysid取出单位名称
--根据groupid查询用户类型代码对应的名称
selectsysuser.*,
(selectmcfrom useryywhereid =sysuser.sysid)sysmc,
(select infofrom dictinfowhere dictcode = sysuser.groupidand typecode ='s01')groupname
from sysuser
1.4 嵌套表
可以将一个sql查询结果组成一个虚表，查询方式和查询一个实体表相同的。
组成的虚拟表字段是不允许重复的，否则 ：
2 notin 关键字
select
ypxx.id,
ypxx.bm,
ypxx.mc,
ypxx.jx,
ypxx.gg,
ypxx.zhxs,
ypxx.scqymc,
ypxx.spmc,
ypxx.zbjg,
ypxx.jyzt,
(select info
from dictinfo
where ypxx.jyzt = dictcode
and typecode = '003') jyztmc
from ypxx
-- notin将供货商药品目录已经存在药品过虑掉not后括号里的查询结果过滤掉
--ypxx表中记录，根据ypxxid关联，不在select ypxxid from gysypml whereusergysid='5197cdd2-08cf-11e3-8a4f-60a44cea4388'结果集中
where ypxx.idnotin(
--某个供货商药品目录结果集
select ypxxidfrom gysypmlwhere usergysid='5197cdd2-08cf-11e3-8a4f-60a44cea4388'
)
3 not exists 关键字
--
select ypxx.id,
ypxx.bm,
ypxx.mc,
ypxx.jx,
ypxx.gg,
ypxx.zhxs,
ypxx.scqymc,
ypxx.spmc,
ypxx.zbjg,
ypxx.jyzt,
(select info
from dictinfo
where ypxx.jyzt = dictcode
and typecode = '003') jyztmc
--子查询，关联查询到说明此药品id在供货商药品目录存在
--(select id from gysypml whereusergysid='5197cdd2-08cf-11e3-8a4f-60a44cea4388' and ypxx.id = gysypml.ypxxid)gysypmlid
from ypxx
--查询子查询不为空的值
wherenotexists (selectid
from gysypml
where usergysid = '5197cdd2-08cf-11e3-8a4f-60a44cea4388'
and ypxx.id = gysypml.ypxxid)
--每个记录查询时，都需要执行exists中的子查询
--建议子查询条件根据主键或索引查询，可以提高效率，推荐使用exists，因为exists比in或not in速度要高
4 in 关键字
select
gysypml.id gysypmlid,
gysypml.ypxxid,
gysypml.usergysid,
usergys.mc usergysmc,
gysypml_control.control,
(select info
from dictinfo
where typecode = '008'
and dictcode = gysypml_control.control)controlmc,
ypxx.id,
ypxx.bm,
ypxx.mc,
ypxx.jx,
ypxx.gg,
ypxx.zhxs,
ypxx.scqymc,
ypxx.spmc,
ypxx.zbjg,
ypxx.jyzt,
(select info
from dictinfo
where ypxx.jyzt = dictcode
and typecode = '003') jyztmc
from gysypml, usergys, gysypml_control,ypxx
where gysypml.usergysid = usergys.id
and gysypml.ypxxid =gysypml_control.ypxxid
and gysypml.usergysid =gysypml_control.usergysid
and gysypml.ypxxid = ypxx.id
--限制只查询医院本区域供货商的药品目录
--1.1.16.是医院所在区域
and gysypml.usergysid in (
select usergysarea.usergysidfrom usergysareawhere'1.1.16.'like usergysarea.areaid||'%'
)
--将采购单中药品过虑掉
--2014101040就是采购单id
and gysypml.ypxxid notin(
select yycgdmx.ypxxid from yycgdmx2014 yycgdmxwhere yycgdmx.yycgdid ='2014101040'
)
--分析所需要的子查询
--采购单下药品id列表
select yycgdmx.ypxxid from yycgdmx2014 yycgdmx where yycgdmx.yycgdid ='2014101040'
--查询出医院所在区域供货商的信息
select usergysarea.usergysid from usergysarea where '1.1.16.' likeusergysarea.areaid || '%'
--查询医院的区域(三级区域1.1.16.)
select dq from useryy where id='1f8b098b-067e-11e3-8a3c-0019d2ce5116'
5 sum 关键字
sum 求和 ，nvl 如果 该字段为空则按0 计算
select sum(nvl(yycgdmx.cgl,0)) cgl,
sum(nvl(yycgdmx.cgje,0)) cgje
from yycgdmx2015 yycgdmx,
yycgd2015 yycgd, useryy,
ypxx, usergys
where yycgdmx.yycgdid
= yycgd.id
and yycgd.useryyid = useryy.id
and yycgdmx.ypxxid = ypxx.id
and yycgdmx.usergysid = usergys.id
6 group by 关键字
groupby 按照什么统计
group by business.id,business.bm,business.mc 先按照id 统计
如果business.bm列有重复按照business.bm合并统计
如果business.mc列有重复然后按照business.mc统计
having count(*)>1一般用在group by 后边 下句中就是查 表中a 列重复出现的记录
按a 进行分组后，进行组筛选，统计每一组行数大于1的纪录
select a,count(*) from table group by a having count(*)>1
GROUP_CONCAT(id)
按照tool_name合并重复数据，GROUP_CONCAT合并重复列的数据 以 ，号分割
Select tool_name,GROUP_CONCAT(id),GROUP_CONCAT(DISTINCT owner),GROUP_CONCAT(DISTINCT big_type)
From cmb_tools_list where tool_name<>"" Group By tool_name
结果
tool_name id owner big_type
KANBAN 5,52 吴陶 71,74 7 nvl 实现（用例）
nvl 查询出所有镇区域内的采购金额没有采购的以0 计算
select areainfo.areaname,
nvl(yybusiness.cgje,0)cgje
from (select areaname,0 cgje
from bss_sys_area
where bss_sys_area.arealevel ='2') areainfo
leftjoin (
select yybusiness.areaname,sum(yybusiness.cgje) cgje
from (select yybusiness.*,
(select bss_sys_area.areaname
from bss_sys_area
where areaid = yybusiness.parentid) areaname
from (select yybusiness.*,
(select parentid
from bss_sys_area
where areaid = yybusiness.useryydq) parentid
from (select yybusiness.useryyid,
(select dq
from useryy
whereid = yybusiness.useryyid) useryydq,
yybusiness.cgje
from (
select useryy.id useryyid,
useryy.mc useryymc,
yycgd.bm yycgdbm,
yycgd.id yycgdid,
usergys.id usergysid,
usergys.mc usergysmc,
yycgdmx.ypxxid,
ypxx.id,
ypxx.bm,
ypxx.mc,
ypxx.jx,
ypxx.gg,
ypxx.zhxs,
ypxx.scqymc,
ypxx.spmc,
ypxx.jyzt,
(select info
from dictinfo
where ypxx.jyzt = dictcode
and typecode ='003') jyztmc,
(select info
from dictinfo
where typecode ='011'
and dictcode = yycgdmx.cgzt)cgztmc,
yycgdmx.cgl,
yycgdmx.cgje,
yycgdmx.rkl,
yycgdmx.rkje,
yycgdmx.thl,
yycgdmx.thje,
yycgdmx.jsl,
yycgdmx.jsje
from yybusiness2015 yycgdmx,
yycgd2015 yycgd,
useryy,
usergys,
ypxx
where yycgdmx.yycgdid = yycgd.id
and yycgd.useryyid = useryy.id
and yycgdmx.usergysid = usergys.id
and yycgdmx.ypxxid = ypxx.id
)yybusiness) yybusiness) yybusiness) yybusiness
groupby yybusiness.areaname) yybusinesson yybusiness.areaname =
areainfo.areaname
8 批量插入后取出每条新插入记录的id
<mapper namespace="com.xkeshi.shop.dao.AccountDAO">
<insert id="batchInsert" useGeneratedKeys="true" keyProperty="id">
INSERT INTO account
(username, password)
VALUES
<foreach collection="list" item="account" index="index" separator="," >
(#{account.username},#{account.password})
</foreach>
</insert>
</mapper>
这两个函数是差不多的，但由于优化方案不同，通常NOT Exists要比NOT IN要快，因为NOT EXISTS可以使用结合算法二NOT IN就不行了，而EXISTS则不如IN快，因为这时候IN可能更多的使用结合算法。
EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False
Select * from tableA Where exists(Select * From tableB Where tableB.ID=tableA.ID)
这句相当于：Select * from tableA Where id in (Select ID From tableB)
对于表tableA的每一条数据，都执行Select * From tableB Where tableB.ID=tableA.ID的存在性判断，如果表tableB中存在表tableA当前行相同的ID，则Exists为真，该行显示，否则不显示。
小结：
IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况
In确定给定的值是否与子查询或列表中的值相匹配
Exists指定一个子查询，检测行的存在
初始化某表 的所有数据
update［表名］ set icon=CONCAT('tools_icon_',id%25+1,'.png') where icon is null or icon='' ;
COMMIT;
sql 里 符号<> 于 != 的区别 <> 与!=都是不等于的意思，但是一般都是用<>来代表不等于因为<>在任何SQL中都起作用但是!=在sql2000中用到，则是语法错误，不兼容的
扫一扫
332
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
