left join问题,解决立马结帖
我添加一条未读信息数据如下:
http://img.my.csdn.net/uploads/201707/14/1500015730_9240.png
下面SQL查询查询出来结果如下:
SELECT distinct *
FROM ( SELECT e.patient_Name as patientName,e.patient_Sex as patientSex,e.patient_Tel as patientTel, es.EMR_Code , H.isRead, 'Process' as RecordType,H.CreateByPlatform,
es.message ,
es.createDate ,
CASE WHEN d.userRealName IS NULL
OR d.userRealName = ''
THEN CASE WHEN f.factoryName IS NULL
OR f.factoryName = ''
THEN CASE WHEN der.userRealName IS NULL
OR der.userRealName = ''
THEN '作者'
END
ELSE f.factoryName
END
ELSE d.userRealName
END AS doctorName
FROM dbo.EMR_stream es
LEFT JOIN dbo.doctors d ON d.Id = es.createId
AND d.flag = 1
LEFT JOIN dbo.factory f ON f.Id = es.createId
AND f.flag = 1
LEFT JOIN dbo.designers der ON der.Id = es.createId
AND der.flag = 1
LEFT join dbo.simple_EMR E on es.EMR_Code=E.EMR_Code
Left Join EMR_ProcessUReadMessage H on H.EMR_Code=es.EMR_Code
WHERE es.EMR_Code = '201707130001' and H.CreateByPlatform='Doctor'
AND es.flag = 1
UNION
SELECT e.patient_Name,E.patient_Sex,e.patient_Tel, dess.EMR_Code , H.isRead, 'Remark' as RecordType,H.CreateByPlatform,
dess.doctorRemark AS 'message' ,
dess.createDate ,
CASE WHEN d.userRealName IS NULL
OR d.userRealName = ''
THEN CASE WHEN der.userRealName IS NULL
OR der.userRealName = '' THEN '作者'
ELSE der.userRealName
END
ELSE d.userRealName
END AS doctorName
FROM dbo.doctor_essays dess
LEFT JOIN dbo.doctors d ON d.Id = dess.createId
AND d.flag = 1
LEFT JOIN dbo.designers der ON der.Id = dess.createId
AND der.flag = 1
LEFT join dbo.simple_EMR E on dess.EMR_Code=E.EMR_Code
LEFT join EMR_ProcessUReadMessage H on H.EMR_Code=dess.EMR_Code
WHERE dess.EMR_Code = '201707130001' and H.CreateByPlatform='Doctor'
AND dess.flag = 1
) a
ORDER BY a.createDate ASC
查询出来的结果如下:
http://img.my.csdn.net/uploads/201707/14/1500015648_4895.png
最终想得到结果:
http://img.my.csdn.net/uploads/201707/14/1500015915_6062.png
请问各位高手以上SQL语句怎么修改?
...全文
相关推荐
SQL中的left join right join
数据库常见的join方式有三种:inner join, left outter join, right outter join(还有一种full join,因不常用,本文不讨论)。这三种连接方式都是将两个以上的表通过on条件语句,拼成一个大表。
SQL语句优化之JOIN和LEFT JOIN 和 RIGHT JOIN语句的优化
在数据库的应用中,我们经常需要对数据库进行多表查询,然而当数据量非常大时多表查询会对执行效率产生非常大的影响,因此我们在使用JOIN和LEFT JOIN 和 RIGHT JOIN语句时要特别注意
SQL之left join、right join、inner join的区别浅析
主要介绍了SQL之left join、right join、inner join的区别浅析,本文讲解了它们的区别并给出了实例,需要的朋友可以参考下
inner join、 left join 、right join、 outer join之间的区别
inner join、 left join 、right join、 outer join之间的区别
Sql语句用left join解决多表关联问题(关联套关联,例子和源码)
Sql语句用left join解决多表关联问题(关联套关联,例子和源码)一看就明白多表关联如何写SQl语句


