SQL Left Join问题 解决立马结帖

happy664618843 2017-07-16 12:26:07
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
201707130001 测试医学服务中心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
...全文
378 11 打赏 收藏 转发到动态 举报
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
happy664618843 2017-07-19
  • 打赏
  • 举报
 回复
顶 有没有高手来啊
OwenZeng_DBA 2017-07-18
  • 打赏
  • 举报
 回复
你把这个SQL 拆分下吧，，join太多表，逻辑也复杂，如果数据量变大，性能也会存在隐患
happy664618843 2017-07-17
  • 打赏
  • 举报
 回复
引用 8 楼 sinat_28984567 的回复:
随便改了一个doctor是1的改成0 了 
--测试数据
if not object_id(N'Tempdb..#EMR_ProcessUReadMessage') is null
    drop table #EMR_ProcessUReadMessage
Go
Create table #EMR_ProcessUReadMessage([EMR_Code] bigint,[CreateByPlatform] nvarchar(28),[IsRead] int)
Insert #EMR_ProcessUReadMessage
select 201707130001,N'Doctor',1 union all
select 201707130001,N'Desgin',0 union all
select 201707130001,N'Medicine',1 union all
select 201707130001,N'Doctor',0 union all
select 201707130001,N'Desgin',0 union all
select 201707130001,N'Medicine',1
GO
if not object_id(N'Tempdb..#doctor_essays') is null
    drop table #doctor_essays
Go
 CREATE TABLE #doctor_essays([EMR_Code] bigint,doctorRemarkDate DATETIME,doctorRemark NVARCHAR(100))
Insert #doctor_essays
select 201707130001,'2017.07.14 14:19:00',N'添加一条未读信息' union all
select 201707130001,'2017.07.14 14:08:42',N'测试未读'
GO
if not object_id(N'Tempdb..#simple_EMR') is null
    drop table #simple_EMR
Go
Create table #simple_EMR([EMR_Code] bigint,[patient_Name] nvarchar(31),[patient_Age] Date,[patient_Sex] bigint,[patient_Tel] bigint)
Insert #simple_EMR
select 201707130001,N'测试医学服务中心New','2017-08-16',1,15221105698
Go
--测试数据结束
;WITH ctea AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY [EMR_Code],[CreateByPlatform] ORDER BY (SELECT 1)) AS num FROM #EMR_ProcessUReadMessage
),cteb AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY [EMR_Code] ORDER BY doctorRemarkDate) AS num  FROM #doctor_essays
)
SELECT  dess.EMR_Code ,
        H.isRead ,
        'Remark' AS RecordType ,
        H.CreateByPlatform ,
        dess.doctorRemark AS 'message'
FROM    cteb dess
        LEFT JOIN #simple_EMR E ON dess.EMR_Code = E.EMR_Code
        LEFT JOIN ctea H ON H.EMR_Code = dess.EMR_Code AND H.num = dess.num
WHERE   dess.EMR_Code = '201707130001'
        AND H.CreateByPlatform = 'Doctor'
亲 你好，谢谢，结果是对，我发帖的时候，SQL精简了，以下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  es.Designer
                                                                    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   isnull(dess.Designer,'作者') 
                                                               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
二月十六 版主 2017-07-17
  • 打赏
  • 举报
 回复
随便改了一个doctor是1的改成0 了
--测试数据

if not object_id(N'Tempdb..#EMR_ProcessUReadMessage') is null

    drop table #EMR_ProcessUReadMessage

Go

Create table #EMR_ProcessUReadMessage([EMR_Code] bigint,[CreateByPlatform] nvarchar(28),[IsRead] int)

Insert #EMR_ProcessUReadMessage

select 201707130001,N'Doctor',1 union all

select 201707130001,N'Desgin',0 union all

select 201707130001,N'Medicine',1 union all

select 201707130001,N'Doctor',0 union all

select 201707130001,N'Desgin',0 union all

select 201707130001,N'Medicine',1

GO

if not object_id(N'Tempdb..#doctor_essays') is null

    drop table #doctor_essays

Go

 CREATE TABLE #doctor_essays([EMR_Code] bigint,doctorRemarkDate DATETIME,doctorRemark NVARCHAR(100))

Insert #doctor_essays

select 201707130001,'2017.07.14 14:19:00',N'添加一条未读信息' union all

select 201707130001,'2017.07.14 14:08:42',N'测试未读'

GO

if not object_id(N'Tempdb..#simple_EMR') is null

    drop table #simple_EMR

Go

Create table #simple_EMR([EMR_Code] bigint,[patient_Name] nvarchar(31),[patient_Age] Date,[patient_Sex] bigint,[patient_Tel] bigint)

Insert #simple_EMR

select 201707130001,N'测试医学服务中心New','2017-08-16',1,15221105698

Go

--测试数据结束

;WITH ctea AS (

SELECT *,ROW_NUMBER()OVER(PARTITION BY [EMR_Code],[CreateByPlatform] ORDER BY (SELECT 1)) AS num FROM #EMR_ProcessUReadMessage

),cteb AS (

SELECT *,ROW_NUMBER()OVER(PARTITION BY [EMR_Code] ORDER BY doctorRemarkDate) AS num  FROM #doctor_essays

)

SELECT  dess.EMR_Code ,

        H.isRead ,

        'Remark' AS RecordType ,

        H.CreateByPlatform ,

        dess.doctorRemark AS 'message'

FROM    cteb dess

        LEFT JOIN #simple_EMR E ON dess.EMR_Code = E.EMR_Code

        LEFT JOIN ctea H ON H.EMR_Code = dess.EMR_Code AND H.num = dess.num

WHERE   dess.EMR_Code = '201707130001'

        AND H.CreateByPlatform = 'Doctor'


happy664618843 2017-07-17
  • 打赏
  • 举报
 回复
引用 6 楼 sinat_28984567 的回复:
[quote=引用 5 楼 happy664618843 的回复:] 不是去重的问题，是我没有描述清楚，数据查询出来是这样的： 查询出来的结果如下: EMR_Code isRead RecordType CreateByPlatform message createDate 201707130001 0 Remark Doctor 添加一条未读信息 2017-07-14 14:19:04.167 201707130001 1 Remark Doctor 添加一条未读信息 2017-07-14 14:19:04.167 201707130001 0 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
楼主我用你的测试数据和sql语句查询出来的美誉 10 10 这样的就是1111； EMR_ProcessUReadMessage 里边doctor的isread全是1没有0的[/quote] 你好，EMR_ProcessUReadMessage doctor的isRead字段有1 和0的，若全是1，我可以用distinct.之前是因为数据搞错了。
二月十六 版主 2017-07-17
  • 打赏
  • 举报
 回复
引用 5 楼 happy664618843 的回复:
不是去重的问题，是我没有描述清楚，数据查询出来是这样的： 查询出来的结果如下: EMR_Code isRead RecordType CreateByPlatform message createDate 201707130001 0 Remark Doctor 添加一条未读信息 2017-07-14 14:19:04.167 201707130001 1 Remark Doctor 添加一条未读信息 2017-07-14 14:19:04.167 201707130001 0 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
楼主我用你的测试数据和sql语句查询出来的美誉 10 10 这样的就是1111； EMR_ProcessUReadMessage 里边doctor的isread全是1没有0的
happy664618843 2017-07-17
  • 打赏
  • 举报
 回复
引用 3 楼 sinat_28984567 的回复:
--测试数据
if not object_id(N'Tempdb..#EMR_ProcessUReadMessage') is null
	drop table #EMR_ProcessUReadMessage
Go
Create table #EMR_ProcessUReadMessage([EMR_Code] bigint,[CreateByPlatform] nvarchar(28),[IsRead] int)
Insert #EMR_ProcessUReadMessage
select 201707130001,N'Doctor',1 union all
select 201707130001,N'Desgin',0 union all
select 201707130001,N'Medicine',1 union all
select 201707130001,N'Doctor',1 union all
select 201707130001,N'Desgin',0 union all
select 201707130001,N'Medicine',1
GO
if not object_id(N'Tempdb..#doctor_essays') is null
	drop table #doctor_essays
Go
 CREATE TABLE #doctor_essays([EMR_Code] bigint,doctorRemarkDate DATETIME,doctorRemark NVARCHAR(100))
Insert #doctor_essays
select 201707130001,'2017.07.14 14:19:00',N'添加一条未读信息' union all
select 201707130001,'2017.07.14 14:08:42',N'测试未读'
GO
if not object_id(N'Tempdb..#simple_EMR') is null
	drop table #simple_EMR
Go
Create table #simple_EMR([EMR_Code] bigint,[patient_Name] nvarchar(31),[patient_Age] Date,[patient_Sex] bigint,[patient_Tel] bigint)
Insert #simple_EMR
select 201707130001,N'测试医学服务中心New','2017-08-16',1,15221105698
Go
--测试数据结束
SELECT DISTINCT dess.EMR_Code ,
        H.IsRead ,
        'Remark' AS RecordType ,
        H.CreateByPlatform ,
        dess.doctorRemark AS 'message'
FROM    #doctor_essays dess
        LEFT JOIN #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';
不是去重的问题，是我没有描述清楚，数据查询出来是这样的： 查询出来的结果如下: EMR_Code isRead RecordType CreateByPlatform message createDate 201707130001 0 Remark Doctor 添加一条未读信息 2017-07-14 14:19:04.167 201707130001 1 Remark Doctor 添加一条未读信息 2017-07-14 14:19:04.167 201707130001 0 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
happy664618843 2017-07-17
  • 打赏
  • 举报
 回复
引用 楼主 happy664618843 的回复:
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 201707130001 测试医学服务中心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
[/quote] 不是去重的问题，是我没有描述清楚，数据查询出来是这样的： 查询出来的结果如下: EMR_Code isRead RecordType CreateByPlatform message createDate 201707130001 0 Remark Doctor 添加一条未读信息 2017-07-14 14:19:04.167 201707130001 1 Remark Doctor 添加一条未读信息 2017-07-14 14:19:04.167 201707130001 0 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
二月十六 版主 2017-07-16
  • 打赏
  • 举报
 回复 
SELECT  DISTINCT
        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;
OwenZeng_DBA 2017-07-16
  • 打赏
  • 举报
 回复
引用 楼主 happy664618843 的回复:
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 201707130001 测试医学服务中心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
前面加个去重就可以 SELECT distinct 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
二月十六 版主 2017-07-16
  • 打赏
  • 举报
 回复 
--测试数据

if not object_id(N'Tempdb..#EMR_ProcessUReadMessage') is null

	drop table #EMR_ProcessUReadMessage

Go

Create table #EMR_ProcessUReadMessage([EMR_Code] bigint,[CreateByPlatform] nvarchar(28),[IsRead] int)

Insert #EMR_ProcessUReadMessage

select 201707130001,N'Doctor',1 union all

select 201707130001,N'Desgin',0 union all

select 201707130001,N'Medicine',1 union all

select 201707130001,N'Doctor',1 union all

select 201707130001,N'Desgin',0 union all

select 201707130001,N'Medicine',1

GO

if not object_id(N'Tempdb..#doctor_essays') is null

	drop table #doctor_essays

Go

 CREATE TABLE #doctor_essays([EMR_Code] bigint,doctorRemarkDate DATETIME,doctorRemark NVARCHAR(100))

Insert #doctor_essays

select 201707130001,'2017.07.14 14:19:00',N'添加一条未读信息' union all

select 201707130001,'2017.07.14 14:08:42',N'测试未读'

GO

if not object_id(N'Tempdb..#simple_EMR') is null

	drop table #simple_EMR

Go

Create table #simple_EMR([EMR_Code] bigint,[patient_Name] nvarchar(31),[patient_Age] Date,[patient_Sex] bigint,[patient_Tel] bigint)

Insert #simple_EMR

select 201707130001,N'测试医学服务中心New','2017-08-16',1,15221105698

Go

--测试数据结束

SELECT DISTINCT dess.EMR_Code ,

        H.IsRead ,

        'Remark' AS RecordType ,

        H.CreateByPlatform ,

        dess.doctorRemark AS 'message'

FROM    #doctor_essays dess

        LEFT JOIN #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';


java+mysql实现的代码分享网（所有源码已开源，效果可看网址：www.admintwo.com）
为了巩固知识，做的一个网站。基于struts2+spring+springjdbc开发的代码分享网，所有源码已开源。 网站功能介绍： 1、邮件注册（采用阿里云企业邮箱），为了让大家体验一下邮箱注册功能。我已经在分享的源码中，为大家配置好了测试账户，大家可以在自己电脑上进行测试。 2、md5加密，注册用户，所有密码会以密文保存在数据库，可以保证安全。 3、代码分享功能（核心功能），该功能的主要特色是集成了优秀的文本编辑器，支持插入代码、插入链接、插入表情、插入图片、支持在线预览。同时也实现了文件上传（基于struts2的文件上传功能）。 4、代码下载，下载功能会判断用户是否下载过该代码，若下载过则不扣积分。下载功能也是基于struts2的下载模块实现的。 5、代码评论，该功能是我仿照qq空间评论功能实现的，在本站中，我是以时间倒叙方式显示的（也支持正序）。 6、代码收藏，用户可以收藏代码。 7、消息中心，分为了0系统消息、1评论消息、2兑换消息、3上传图片消息、4上传文件消息、5下载消息(用户扣除积分)、6下载消息。 8、代码中心，分为了分享代码、下载代码、评论代码、收藏代码。 9、设置功能，支持修改昵称、城市、性别、座右铭、密码、头像。 10、赞助兑换功能，支持1个赞助兑换10个积分，也支持用赞助升级称号。 11、其他功能包括：图片压缩处理功能（即使是几M的图片，压缩后也只有几十kb）。通用json处理功能（向方法中传递任何参数，int、string等，都会返回json数据，而且速度很快）。分词功能（点击某一个分享，进入详情页的时候，会对该分享名称进行分词，并且加入到head中，利于网站seo）。 可能还有一些其他功能，通过查看源码可了解。 网站技术介绍： 1、采用语言，java 2、后台框架，struts2+spring+spring JDBC 3、前台技术，layui+jquery+ajax 网站设计思路： 前台渲染是采用的jsp技术，为了保证网站的速度，我使用了几种方法： 1、我将重复的代码保存成单独的jsp文件然后引入（这样的好处就是重复的jsp文件只会加载一次，然后浏览器缓存，下次加载速度会提升）。比如，我将link和header单独提取出来，然后在其他页面进行引入： 2、所有的业务功能，我都放在了html加载完成之后，利用jquery+ajax获取数据后再渲染界面（这样的好处就是给用户的感觉是网站速度很快。因为用户打开后，立马渲染html代码，此时网站结构已经出现，接着用jqury+ajx去后台获取数据。由于我的sql语句严格控制在ms级别，所以只需要几百ms，数据即可拿到，此时渲染在页面上给用户的感觉很快） 3、sql语句的控制，本站的所有sql语句，均控制在1s以下。这块我花了很长时间进行sql优化，我举个例子：为了减少数据库的访问次数，我会想方设法通过一条语句获取所有信息，并且严格控制它的执行速度，绝对不可以超过1s。首页的下载榜、评论榜、收藏榜，这三个功能的数据就是通过一条sql语句获取的： #优化联合查询用户评论、下载、收藏的资源列表 select a.sort,a.id,r.name,a.nowtime,r.isjing,r.isyuan, ifnull(c.res_comments_num,0) as res_comments_num, ifnull(d.res_download_num,0) as res_download_num, ifnull(kp.res_keep_num,0) as res_keep_num from #sort为1代表用户评论的代码列表 (select 1 as sort,c.resources_id as id,c.nowtime as nowtime from comments c #需要指定用户 where c.user1_id = 1 group by c.resources_id union all #sort为2代表用户下载的代码列表 select 2 as sort,d.resources_id as id,d.nowtime as nowtime from download d #需要指定用户 where d.user_id = 1 group by d.resources_id union all #sort为3代表用户收藏的代码列表 select 3 as sort,k.resources_id as id,k.nowtime as nowtime from keep
关于Mysql使用left join写查询语句执行很慢的问题解决
这几天供应商在测试环境上使用MYSQL数据库做开发时遇到一个SQL性能问题，即在他开发环境本地跑SQL速度很快就一两秒时间，但是同样的SQL放在测试环境上死活跑了很久一直出不了结果。最后求助到我这边，以下正文是我解决这次问题的一个过程浅谈，供大家参考。
2019小白SQL入门（零基础60分钟含SQL语法问答精彩）
零基础60分钟含SQL语法问答精彩
查询优化（TTFB过长）left join索引未生效
查询优化（TTFB过长）left join索引未生效
SQL调优案例——多表查询(left jion)的优化
有关left join慢查询的优化
MS-SQL Server

34,594

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章