Tag Archives: when subquery is not introduced with exists

[Thinkphp6] Connect to SQL Server and use subquery to report error: when subquery is not introduced with exists, only one expression can be specified in the selection list

Connect to SQL Server in thinkphp6 and report errors using subquery

sentence

Print SQL statements

Mysql

SELECT `NickName`,`Gender`,`Mobile`,`RealName`,`Birthday` FROM `SiteCustomerSupplement` `s` 

INNER JOIN `WxUser` `u` ON `u`.`OpenId`=`s`.`OpenId` 

WHERE `s`.`OpenId` IN (SELECT DISTINCT `OpenId` FROM `WxPackagesOrder` WHERE `OrderStatus` >= 40 AND `RegionId` = 72) AND `Birthday` BETWEEN '2021-10-26' AND '2021-11-02'

SQL Server

SELECT T1.* FROM (SELECT thinkphp.*, ROW_NUMBER() OVER ( ORDER BY rand()) AS ROW_NUMBER FROM (SELECT [NickName],[Gender],[Mobile],[RealName],[Birthday] FROM [SiteCustomerSupplement] [s] 

INNER JOIN [WxUser] [u] ON [u].[OpenId]=[s].[OpenId]

WHERE [s].[OpenId] IN (SELECT T1.* FROM (SELECT thinkphp.*, ROW_NUMBER() OVER ( ORDER BY rand()) AS ROW_NUMBER FROM (SELECT DISTINCT [OpenId] FROM [WxPackagesOrder] WHERE [OrderStatus] >= 40 AND [RegionId] = 72) AS thinkphp) AS T1) AND [Birthday] BETWEEN '2021-10-26' AND '2021-11-02') AS thinkphp) AS T1

Obviously, it is much longer than the MySQL statement, mainly this paragraph

SELECT thinkphp.*, ROW_NUMBER() OVER ( ORDER BY rand()) AS ROW_NUMBER FROM

So when the problem comes, how to solve it?Just replace it with a native statement. Don’t use TP internal encapsulation.