今天应某人的要求,写一篇博客,有关我今天早上所遇到的问题。
首先提需求:写一个脚本,查出医生排班情况(要具体),四个字段关联四张表。
首先我想到的是这样的:
select
[ghlx]
,e.[sfxmmc]
,a.[ks]
,c.[ksmc]
,[ys]
, b.[gh] as gh
, b.[rymc] as rymc
,[zyi]
,[zer]
,[zsan]
,[zsi]
,[zwu]
,[zlv]
,[zri]
,[zhl]
,a.[ghzb]
, d.[ghzbmc] as ghzbmc
from mz_ghpb a
inner join xt_sfxm e on e.sfxm=a.ghlx and e.zt=1 and e.bgbz=0
inner join xt_ry b on b.ry=a.ys and b.zt=1 and b.bgbz=0
inner join xt_ks c on c.ks=a.ks and c.zt=1 and c.bgbz=0
inner join xt_ghzb d on d.ghzb=a.ghzb and d.zt=1 and d.bgbz=0
where a.zt=1 and a.bgbz=0
很简单就是通过inner join 关联四张表,取出相关字段如 e.[sfxmmc]、a.[ks]、c.[ksmc]、b.[gh]、b.[rymc]、d.[ghzbmc]等字段。
然而,现实就是这么无情
几万条数据居然只有两条显示。
对于ys字段和ghzb字段,有0值,而且这行需要显示出来。然而很明显xt_ry和xt_ghzb表中并没有与0相对应的记录行,根据inner join法则,未关联到的数据行会被删除,并不显示,so 最终,ys为0和ghzb为0的行都被删除。刚好,ys和ghzb是互斥的,ys不为0则ghzb为0,ys为0则ghzb不为0,或者两者都不为0。SO 对于每一行数据来说,都达不到四个inner join 都能关联到的程度,所以全被删除。(剩下那两条是我随意插的,ys和ghzb同时不为0)。
接下来,我想到了case when 和union
它可以是这样的:
select
[ghlx]
,a.[ks]
,c.[ksmc]
,a.[ys]
, case
when ys='0' then '0'
else (select gh from xt_ry b where zt = '1' and bgbz='0' and ry= a.[ys])
end as gh
,case
when ys='0' then '0'
else (select rymc from xt_ry b where zt = '1' and bgbz='0' and ry= a.[ys])
end as rymc
,[zyi]
,[zer]
,[zsan]
,[zsi]
,[zwu]
,[zlv]
,[zri]
,[zhl]
,a.[ghzb]
,case
when [ghzb]='0' then '0'
else (select ghzbbh from xt_ghzb d where zt='1' and bgbz='0' and ghzb=a.[ghzb] )
end as ghzbmc
from mz_ghpb a
inner join xt_ks c on c.ks=a.ks and c.zt=1 and c.bgbz=0
where a.zt=1 and a.bgbz=0
然而,这个语法是错误的,子查询中并不能用 a表,如a.[ghzb]等。
关于我中间尝试的union并没有保存代码,思路是(ys!=0 &&ghzb=0)+(ys=0&&ghzb!=0)+(ys=0&&ghzb=0),是在太逗比了。
最后隆重介绍left join ,对于关联不到b表的a表数据行还是会保留下来显示,(inner join法则,未关联到的数据行会被删除,并不显示)。
假如有3个inner join 1个left join,当一条数据满足三条inner join时,不管满不满足left join,都会显示出来,当然如果满足left,会显示该有的字段,不然为null。
就像现在这种情况,ys和ghzb不管关联到关联不到,都必须保证其他关联字段的正常显示。
写不下去了。。。现在15:58,无心向学,贴出代码:
select
[ghlx]
,e.[sfxmmc]
,a.[ks]
,c.[ksmc]
,[ys]
, b.[gh] as gh
, b.[rymc] as rymc
,[zyi]
,[zer]
,[zsan]
,[zsi]
,[zwu]
,[zlv]
,[zri]
,[zhl]
,a.[ghzb]
, d.[ghzbmc] as ghzbmc
from mz_ghpb a
inner join xt_sfxm e on e.sfxm=a.ghlx and e.zt=1 and e.bgbz=0
left join xt_ry b on b.ry=a.ys and b.zt=1 and b.bgbz=0
inner join xt_ks c on c.ks=a.ks and c.zt=1 and c.bgbz=0
left join xt_ghzb d on d.ghzb=a.ghzb and d.zt=1 and d.bgbz=0
where a.zt=1 and a.bgbz=0
最后:TO MS
我就问一句,当礼物行不行。。。。