错误代码：1242子查询返回超过1行的mysql(Error Code: 1242 Subquery returns more than 1 row mysql)

错误代码：1242子查询返回超过1行的mysql(Error Code: 1242 Subquery returns more than 1 row mysql)

我正在尝试根据目的地（DEST）选择成本。 如果国家不存在成本，那么我必须找到大陆，如果大陆也不存在成本，那么我根据其他人（目的地）选择。

SELECT SUM(CD.COST+CW.COST) AS TOTAL_COST FROM SHIP_COST_BY_DEST CD INNER JOIN SHIP_COST_BY_WEIGHT CW ON CD.PROD_CODE = CW.PROD_CODE WHERE IF((SELECT UPPER(CD.DEST) FROM SHIP_COST_BY_DEST), (UPPER(CD.DEST) = UPPER('Others')), (IF((SELECT UPPER(CD.DEST) FROM SHIP_COST_BY_DEST WHERE UPPER(CD.DEST)=UPPER('India')), UPPER(CD.DEST) = UPPER('India'),'Asia')))

任何人都可以帮助我

I am trying select the cost based on destination(DEST). If the cost not exist for country, then i have to find for continent, if the cost not exist for continent also, then i have select based on others(destination).

SELECT SUM(CD.COST+CW.COST) AS TOTAL_COST FROM SHIP_COST_BY_DEST CD INNER JOIN SHIP_COST_BY_WEIGHT CW ON CD.PROD_CODE = CW.PROD_CODE WHERE IF((SELECT UPPER(CD.DEST) FROM SHIP_COST_BY_DEST), (UPPER(CD.DEST) = UPPER('Others')), (IF((SELECT UPPER(CD.DEST) FROM SHIP_COST_BY_DEST WHERE UPPER(CD.DEST)=UPPER('India')), UPPER(CD.DEST) = UPPER('India'),'Asia')))

Could any one help me

最满意答案

这是一个非常复杂的WHERE子句 - 维持这将是一场噩梦。 但问题是你的一个子查询（括号内的选择）返回多个值，而逻辑不允许这样做。 你最容易做的事情（因为我没有你的数据）是分别运行每个子查询，看看哪个返回多个值，看看它在那个地方是否合乎逻辑。

例如，你不能说

Name = (SELECT Name FROM AllCustomers)

因为名称不能同时将所有这些名称等同。 但是，你可以说

Name IN (SELECT Name FROM AllCustomers)

因为这会为你提供一个可能的IN列表。 此外，您需要确保所有子查询不仅仅在适当时返回一个答案，而且它们只能返回一个答案，或者当数据不同于数据时，您最终会遇到生产问题你测试过。

That's an awfully complex WHERE clause - it will be a nightmare to maintain. But the problem is that one of your subqueries (The selects inside the parentheses) is returning multiple values and logic doesn't permit that. The easiest thing for you to do (since I don't have your data) is to run each of those subqueries separately and see which returns multiple values, and see if it logically makes sense at that place.

For example, you can't say

Name = (SELECT Name FROM AllCustomers)

because the name can't EQUAL all those names at once. You can, however, say

Name IN (SELECT Name FROM AllCustomers)

because that gives you a list of possibilities for it to be IN. Also, you need to make sure that not only are all of your subqueries only returning one answer if appropriate NOW, but that they can ONLY return one answer, or you'll end up with a problem in production when the data is different from what you tested with.