left join问题,解决立马结帖

[happy664618843]

我添加一条未读信息数据如下:
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语句怎么修改?

[IEEE_China]

说说你的想法 你是想改你贴的代码，直接求出你要的数据， 还是 由你添加的信息 和 你查询的结果 来求出最终 你要的数据？

[二月十六]

可以试试把left join那个多条的用子查询读取一条来实现。 ps：建议楼主列出表结构，并提供测试数据

[OwenZeng_DBA]

@happy664618843 逻辑应该理一下，，

[IEEE_China]

检查你的各个表， 如果A表主键ID 在B表中为外键，且B表里有该ID的数据有多条，LEFT JOIN B表的时候，就会出现多条数据。

[二月十六]

楼主你给的这个代码的结果和上边那个图片的不一样，isread列图片的是1 0 1 0 ，楼主代码写的是 1 1 1 1 哪个对？如果1111那就直接加distinct就好了，如果1010哪个还得再考虑

[happy664618843]

@all EMR_ProcessUReadMessage表数据如下:一共6条 EMR_Code CreateByPlatform IsRead 201707130001 Doctor 1 201707130001 Desgin 0 201707130001 Medicine 1 201707130001 Doctor 1 201707130001 Desgin 0 201707130001 Medicine 1 doctor_essays 表 两条记录 EMR_Code doctorRemarkDate doctorRemark 201707130001 2017.07.14 14:19:00 添加一条未读信息 201707130001 2017.07.14 14:08:42 测试未读 simple_EMR表数据只有一条: EMR_Code patient_Name patient_Age patient_Sex patient_Tel [phone] 测试医学服务中心New 2017-08-16 1 15221105698 SQL语句如下

 SELECT    dess.EMR_Code , H.isRead, 'Remark' as RecordType,H.CreateByPlatform,
                                                dess.doctorRemark AS 'message' ,
                                                dess.createDate 
                                               
                                      FROM      dbo.doctor_essays dess
                                              
                                                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

查询出来的结果如下: EMR_Code isRead RecordType CreateByPlatform message createDate 201707130001 1 Remark Doctor 添加一条未读信息 2017-07-14 14:19:04.167 201707130001 1 Remark Doctor 添加一条未读信息 2017-07-14 14:19:04.167 201707130001 1 Remark Doctor 测试未读 2017-07-14 14:08:45.170 201707130001 1 Remark Doctor 测试未读 2017-07-14 14:08:45.170 最终我想得到结果如下:请问各位高手如何实现?? EMR_Code isRead RecordType CreateByPlatform message createDate 201707130001 1 Remark Doctor 添加一条未读信息 2017-07-14 14:19:04.167 201707130001 1 Remark Doctor 测试未读 2017-07-14 14:08:45.170

[OwenZeng_DBA]

已经在另外一个帖子回复你了

[happy664618843]

引用 7 楼 sinat_28984567 的回复:

楼主你给的这个代码的结果和上边那个图片的不一样，isread列图片的是1 0 1 0 ，楼主代码写的是 1 1 1 1 哪个对？如果1111那就直接加distinct就好了，如果1010哪个还得再考虑

对,是 1 0 10,我写错了。

[二月十六]

引用 10 楼 happy664618843 的回复:

[quote=引用 7 楼 sinat_28984567 的回复:] 楼主你给的这个代码的结果和上边那个图片的不一样，isread列图片的是1 0 1 0 ，楼主代码写的是 1 1 1 1 哪个对？如果1111那就直接加distinct就好了，如果1010哪个还得再考虑

对,是 1 0 10,我写错了。[/quote] 1010这个的话，测试数据是不是有点不太对……

[xiaoxiangqing]

给一些测试数据

[abuying]

表结构是一对多，最终查询以dess表主，应该以用子查询 SELECT dess.EMR_Code , (select top 1 isRead from EMR_ProcessUReadMessage H where H.EMR_Code=dess.EMR_Code and H.CreateByPlatform='Doctor' ) isRead, 'Remark' as RecordType, (select top 1 CreateByPlatform from EMR_ProcessUReadMessage H where H.EMR_Code=dess.EMR_Code and H.CreateByPlatform='Doctor' ) CreateByPlatform, dess.doctorRemark AS 'message' , dess.createDate FROM dbo.doctor_essays dess WHERE dess.EMR_Code = '201707130001' AND dess.flag = 1

[happy664618843]

@sinat_28984567 高手 人呢？

[二月十六]

引用 12 楼 happy664618843 的回复:

@sinat_28984567 高手 人呢？

这个只能按照楼主给的例子来写，然后楼主按照例子改自己的代码；因为大家不知道楼主的实际表结构什么样的，所以改楼主的代码也是蒙着改，改不对的可能性比较大，而且楼主代码也挺长的不好看……