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 方式，参考：

1801260

明天发布 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里面这样应用：
<dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper</artifactId> <version>5.1.10</version> </dependency>

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