sqlserver连表with(nolock)问题

[yccool]

我在mybatis里面用的sql语句

Select AUS.ScheduleID, AUS.SystemID, AUS.ClinicID, AUS.DoctorID, AUS.ScheduleDate, 
    AUS.StartTime, AUS.EndTime, AUS.Status, AUS.BookBy, AUS.Note, AUS.Remark, AUS.SourceType, CM.CompanyName,
    AU.UserName As DoctorName, AU.UserNumber As DoctorNumber, CC.CodeDesc As ClinicName, CD.Lat, CD.Lng,
    CD.ContactTel, CD.Address, CR.ConsultationStatusID, CR.RegisterStatus,A1.CodeDesc as AreaLevel1, A2.CodeDesc as AreaLevel2
    From ACM_User_Schedule AUS with(nolock)
    Left Join Client_Register CR with(nolock) On AUS.BookBy=CR.ClientID And CR.SourceType='F' And AUS.ClientRegisterNum=CR.ClientRegisterNum 
    Inner Join ACM_User AU with(nolock) On AU.UserID = AUS.DoctorID 
    Inner Join Code_Clinic CC with(nolock) On AUS.ClinicID=CC.CodeID
    Inner Join Clinic_Detail CD with(nolock) On CC.CodeID = CD.ClinicID
    Inner Join Code_Area A1 with(nolock) On CD.AreaLevel1ID=A1.CodeID
    Inner Join Code_Area A2 with(nolock) On CD.AreaLevel2ID=A2.CodeID
    Inner Join Company_Master CM with(nolock) On CC.SystemID = CM.SystemID
    Where BookBy=#{cientId,jdbcType=INTEGER}

但是最后生成的sql: SELECT count(0) FROM ACM_User_Schedule AUS with(nolock) LEFT JOIN Client_Register CR with(nolock) INNER JOIN ACM_User AU with(nolock) INNER JOIN Code_Clinic CC with(nolock) INNER JOIN Clinic_Detail CD with(nolock) INNER JOIN Code_Area A1 with(nolock) INNER JOIN Code_Area A2 with(nolock) INNER JOIN Company_Master CM with(nolock) WHERE BookBy = 213361

没有了on条件，所以还是没有解决链表sqlserver的with(nolock)问题啊

[abel533]

生成的 SQL：

SELECT COUNT(0)
FROM ACM_User_Schedule AUS WITH (NOLOCK)
    LEFT JOIN Client_Register CR WITH (NOLOCK)
    ON AUS.BookBy = CR.ClientID
        AND CR.SourceType = 'F'
        AND AUS.ClientRegisterNum = CR.ClientRegisterNum
    INNER JOIN ACM_User AU WITH (NOLOCK) ON AU.UserID = AUS.DoctorID
    INNER JOIN Code_Clinic CC WITH (NOLOCK) ON AUS.ClinicID = CC.CodeID
    INNER JOIN Clinic_Detail CD WITH (NOLOCK) ON CC.CodeID = CD.ClinicID
    INNER JOIN Code_Area A1 WITH (NOLOCK) ON CD.AreaLevel1ID = A1.CodeID
    INNER JOIN Code_Area A2 WITH (NOLOCK) ON CD.AreaLevel2ID = A2.CodeID
    INNER JOIN Company_Master CM WITH (NOLOCK) ON CC.SystemID = CM.SystemID
WHERE BookBy = 1
------------------
SELECT TOP 10 ScheduleID, SystemID, ClinicID, DoctorID, ScheduleDate
    , StartTime, EndTime, Status, BookBy, Note
    , Remark, SourceType, CompanyName, DoctorName, DoctorNumber
    , ClinicName, Lat, Lng, ContactTel, Address
    , ConsultationStatusID, RegisterStatus, AreaLevel1, AreaLevel2
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY RAND()) AS PAGE_ROW_NUMBER, ScheduleID, SystemID, ClinicID, DoctorID
        , ScheduleDate, StartTime, EndTime, Status, BookBy
        , Note, Remark, SourceType, CompanyName, DoctorName
        , DoctorNumber, ClinicName, Lat, Lng, ContactTel
        , Address, ConsultationStatusID, RegisterStatus, AreaLevel1, AreaLevel2
    FROM (
        SELECT AUS.ScheduleID, AUS.SystemID, AUS.ClinicID, AUS.DoctorID, AUS.ScheduleDate
            , AUS.StartTime, AUS.EndTime, AUS.Status, AUS.BookBy, AUS.Note
            , AUS.Remark, AUS.SourceType, CM.CompanyName, AU.UserName AS DoctorName, AU.UserNumber AS DoctorNumber
            , CC.CodeDesc AS ClinicName, CD.Lat, CD.Lng, CD.ContactTel, CD.Address
            , CR.ConsultationStatusID, CR.RegisterStatus, A1.CodeDesc AS AreaLevel1, A2.CodeDesc AS AreaLevel2
        FROM ACM_User_Schedule AUS WITH (NOLOCK)
            LEFT JOIN Client_Register CR WITH (NOLOCK)
            ON AUS.BookBy = CR.ClientID
                AND CR.SourceType = 'F'
                AND AUS.ClientRegisterNum = CR.ClientRegisterNum
            INNER JOIN ACM_User AU WITH (NOLOCK) ON AU.UserID = AUS.DoctorID
            INNER JOIN Code_Clinic CC WITH (NOLOCK) ON AUS.ClinicID = CC.CodeID
            INNER JOIN Clinic_Detail CD WITH (NOLOCK) ON CC.CodeID = CD.ClinicID
            INNER JOIN Code_Area A1 WITH (NOLOCK) ON CD.AreaLevel1ID = A1.CodeID
            INNER JOIN Code_Area A2 WITH (NOLOCK) ON CD.AreaLevel2ID = A2.CodeID
            INNER JOIN Company_Master CM WITH (NOLOCK) ON CC.SystemID = CM.SystemID
        WHERE BookBy = 1
    ) PAGE_TABLE_ALIAS
) PAGE_TABLE_ALIAS
WHERE PAGE_ROW_NUMBER > 1
ORDER BY PAGE_ROW_NUMBER

[abel533]

找到你失败的原因了。。

默认的 ReplaceSql 支持不了，应该选择 regex 方式，参考：

https://github.com/pagehelper/Mybatis-PageHelper/commit/1801260c9de8350448ca7a07f267d8c635a8c140

明天发布 5.1.10 版本，仅仅修改一个默认值。

[yccool]

看你生成的sql是使用sqlserver2005的方式row_number方式，我使用的是sqlserver2012方式，fetch...next..

[abel533]

@yccool 5.1.10 还有问题吗？

[yccool]

@yccool 5.1.10 还有问题吗？

再等你的5.1.10版本呀，没看到你发布到maven仓库啊，现在还没用上5.1.10版本呢

[abel533]

https://search.maven.org/artifact/com.github.pagehelper/pagehelper/5.1.10/jar

中央仓库已经有了，昨晚就提交了。

[yccool]

https://search.maven.org/artifact/com.github.pagehelper/pagehelper/5.1.10/jar

中央仓库已经有了，昨晚就提交了。

我说的是这上面：https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper

[yccool]

https://search.maven.org/artifact/com.github.pagehelper/pagehelper/5.1.10/jar

中央仓库已经有了，昨晚就提交了。

还有就是针对springboot版本的：https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper-spring-boot-starter 也没有看到呀

[yccool]

我在springboot里面的pom.xml里面这样应用： `

com.github.pagehelper pagehelper 5.1.10

`

但是还是跟以前一样啊，无法解决链表with(nolock)的问题，你真的在springboot里面测试过吗？