英文:
FIND_IN_SET is getting wrong results
问题
首先,我搜索了关于同样的问题,但没有找到合适的解决方案。
我的问题出现在以下的代码中,它返回了错误的结果:
SELECT FbID, FhID,
FbRef,
FbDate,
(D.AccName) AS DName,
FbQuan,
CONCAT(CategoryName, '-', ProductName) AS ProdName,
(C.AccName) AS CusName,
FhPurPrice, FbSalePrice,
(R.AccName) AS ResoName,
Curr1.CurrencyName,
Curr2.CurrencyName,
Plc1.PlaceName AS FhResoPlaceName,
Plc2.PlaceName AS FbCusPlaceName,
'linked' AS xLinkStatus,
1 AS xStatus
FROM tblfatora2 F2
INNER JOIN tblfatora1 F1 ON F1.FhRef = F2.FhRef
INNER JOIN tblproducts P ON P.ProductID = F1.FhProduct
INNER JOIN tblcategories CT ON CT.CategoryID = P.ProductCategory
INNER JOIN tblaccounts R ON R.AccID = F1.FhReso
INNER JOIN tblaccounts C ON C.AccID = F2.FbCus
INNER JOIN tblaccounts D ON D.AccID = F1.FhDriver
INNER JOIN tblcurrencies Curr1 ON C.AccCurrID = Curr1.CurrencyID
INNER JOIN tblcurrencies Curr2 ON R.AccCurrID = Curr2.CurrencyID
LEFT JOIN tblplaces Plc1 ON F1.FhResoPlace = Plc1.PlaceID
LEFT JOIN tblplaces Plc2 ON F2.FbCusPlace = Plc2.PlaceID
WHERE FIND_IN_SET(`FhID`, '18313,18314')
ORDER BY FbDate, FbID
它给出的结果是错误的。
注意:我在这里使用FIND_IN_SET
,因为我无法在使用这个SQL语句的vb.net代码内使用(IN),在该代码中,我通过循环填充变量(LinkedFatora),然后我使用相同的SQL语句,但将(WHERE FIND_IN_SET('FhID', '18313,18314'))替换为(WHERE FIND_IN_SET('FhID', @FhID))。
我寻找了错误的原因,但无法找到它。
英文:
First, I searched about the same problem but I didn't find an appropriate solution.
My problem is with the following code, it's returning wrong results:
SELECT FbID,FhID,
FbRef,
FbDate,
(D.AccName) AS DName,
FbQuan,
CONCAT(CategoryName,'-',ProductName) AS ProdName,
(C.AccName) AS CusName,
FhPurPrice,FbSalePrice,
(R.AccName) AS ResoName,
Curr1.CurrencyName,
Curr2.CurrencyName,
Plc1.PlaceName AS FhResoPlaceName,
Plc2.PlaceName AS FbCusPlaceName,
'linked' AS xLinkStatus,
1 AS xStatus
FROM tblfatora2 F2
INNER JOIN tblfatora1 F1 ON F1.FhRef = F2.FhRef
INNER JOIN tblproducts P ON P.ProductID = F1.FhProduct
INNER JOIN tblcategories CT ON CT.CategoryID = P.ProductCategory
INNER JOIN tblaccounts R ON R.AccID = F1.FhReso
INNER JOIN tblaccounts C ON C.AccID = F2.FbCus
INNER JOIN tblaccounts D ON D.AccID = F1.FhDriver
INNER JOIN tblcurrencies Curr1 ON C.AccCurrID = Curr1.CurrencyID
INNER JOIN tblcurrencies Curr2 ON R.AccCurrID = Curr2.CurrencyID
LEFT JOIN tblplaces Plc1 ON F1.FhResoPlace = Plc1.PlaceID
LEFT JOIN tblplaces Plc2 ON F2.FbCusPlace = Plc2.PlaceID
WHERE FIND_IN_SET(`FhID`, '18313,18314')
ORDER BY FbDate, FbID
the results that it gives me are: enter image description here
note: I use FIND_IN_SET here because I can't use (IN) where I use that SQL statement in a procedure inside vb.net code:
Public Sub MySql_GetLinked()
xDtAll = New DataTable()
Dim xPar(0) As MySqlParameter
xPar(0) = New MySqlParameter("@FhID", MySqlDbType.String) With {
.Value = LinkedFatora}
xClsMySql.GetData(xSqlLinked, xDtAll, xPar)
End Sub
So I fill the variable (LinkedFatora) by loop and I use the same SQL statement but I replace (WHERE FIND_IN_SET('FhID', '18313,18314')) with (WHERE FIND_IN_SET('FhID', @FhID)).
I looked for the error's reason but couldn't catch it.
答案1
得分: 0
我发现问题,它将 (WHERE FIND_IN_SET(FhID
, '18313,18314')) 更改为 (WHERE FIND_IN_SET(FbID
, '18313,18314')):
SELECT FbID,FhID,
FbRef,
FbDate,
(D.AccName) AS DName,
FbQuan,
CONCAT(CategoryName,'-',ProductName) AS ProdName,
(C.AccName) AS CusName,
FhPurPrice,FbSalePrice,
(R.AccName) AS ResoName,
Curr1.CurrencyName,
Curr2.CurrencyName,
Plc1.PlaceName AS FhResoPlaceName,
Plc2.PlaceName AS FbCusPlaceName,
'linked' AS xLinkStatus,
1 AS xStatus
FROM tblfatora2 F2
INNER JOIN tblfatora1 F1 ON F1.FhRef = F2.FhRef
INNER JOIN tblproducts P ON P.ProductID = F1.FhProduct
INNER JOIN tblcategories CT ON CT.CategoryID = P.ProductCategory
INNER JOIN tblaccounts R ON R.AccID = F1.FhReso
INNER JOIN tblaccounts C ON C.AccID = F2.FbCus
INNER JOIN tblaccounts D ON D.AccID = F1.FhDriver
INNER JOIN tblcurrencies Curr1 ON C.AccCurrID = Curr1.CurrencyID
INNER JOIN tblcurrencies Curr2 ON R.AccCurrID = Curr2.CurrencyID
LEFT JOIN tblplaces Plc1 ON F1.FhResoPlace = Plc1.PlaceID
LEFT JOIN tblplaces Plc2 ON F2.FbCusPlace = Plc2.PlaceID
WHERE FIND_IN_SET(`FbID`, '18313,18314')
ORDER BY FbDate, FbID
英文:
I found the problem, it's changing (WHERE FIND_IN_SET(FhID
, '18313,18314')) to (WHERE FIND_IN_SET(FbID
, '18313,18314')):
SELECT FbID,FhID,
FbRef,
FbDate,
(D.AccName) AS DName,
FbQuan,
CONCAT(CategoryName,'-',ProductName) AS ProdName,
(C.AccName) AS CusName,
FhPurPrice,FbSalePrice,
(R.AccName) AS ResoName,
Curr1.CurrencyName,
Curr2.CurrencyName,
Plc1.PlaceName AS FhResoPlaceName,
Plc2.PlaceName AS FbCusPlaceName,
'linked' AS xLinkStatus,
1 AS xStatus
FROM tblfatora2 F2
INNER JOIN tblfatora1 F1 ON F1.FhRef = F2.FhRef
INNER JOIN tblproducts P ON P.ProductID = F1.FhProduct
INNER JOIN tblcategories CT ON CT.CategoryID = P.ProductCategory
INNER JOIN tblaccounts R ON R.AccID = F1.FhReso
INNER JOIN tblaccounts C ON C.AccID = F2.FbCus
INNER JOIN tblaccounts D ON D.AccID = F1.FhDriver
INNER JOIN tblcurrencies Curr1 ON C.AccCurrID = Curr1.CurrencyID
INNER JOIN tblcurrencies Curr2 ON R.AccCurrID = Curr2.CurrencyID
LEFT JOIN tblplaces Plc1 ON F1.FhResoPlace = Plc1.PlaceID
LEFT JOIN tblplaces Plc2 ON F2.FbCusPlace = Plc2.PlaceID
WHERE FIND_IN_SET(`FbID`, '18313,18314')
ORDER BY FbDate, FbID
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论