FIND_IN_SET返回错误的结果

huangapple go评论51阅读模式
英文:

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

huangapple
  • 本文由 发表于 2023年2月8日 19:56:37
  • 转载请务必保留本文链接:https://go.coder-hub.com/75385455.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定