MYSQL存储过程查询回退逻辑(MYSQL Stored Procedure Query Fallback Logic)

MYSQL存储过程查询回退逻辑(MYSQL Stored Procedure Query Fallback Logic)

我确信这是一个愚蠢的问题，但在阅读完所有内容之后，我无法弄清楚使用Where子句的回退逻辑创建存储过程的正确方法。

我有以下查询：

SELECT FeeName, Amount, Category, VariableName FROM client_rates WHERE state_abv = state_abvIN AND Purchase= purchaseIN AND Category= CategoryIN And client_id=client_idIN AND agent_id=agent_idIN AND ((lender_id=lender_idIN AND county=countyIN) OR (lender_id=lender_idIN AND county='NA') OR (lender_id=1 AND county=countyIN) OR (lender_id=1 AND county='NA'));

因为我使用了OR，它将返回一个匹配所有4个条件的集合。 我想要的是通过条件“回退”。

意思是我希望它返回一个集合where（lender_id = lender_idIN AND county = countyIN）返回一个值。 但是，如果它返回NULL集，则尝试下一个条件集（lender_id = lender_idIN AND county ='NA'）等。

我不认为CASE会解决这个问题，我曾经使用过嵌套的IF EXIST语句，但是强制每个查询运行两次（必须有一个更好的解决方案）。

任何建议，谢谢你的帮助。

I am sure this is a stupid question but after reading everything I cannot figure out the proper way of creating a stored procedure with fallback logic for the Where clause.

I have the following query:

SELECT FeeName, Amount, Category, VariableName FROM client_rates WHERE state_abv = state_abvIN AND Purchase= purchaseIN AND Category= CategoryIN And client_id=client_idIN AND agent_id=agent_idIN AND ((lender_id=lender_idIN AND county=countyIN) OR (lender_id=lender_idIN AND county='NA') OR (lender_id=1 AND county=countyIN) OR (lender_id=1 AND county='NA'));

Because I have used OR it will return a set which matches all 4 conditions. What I want is to "fallback" through conditions.

Meaning I want it to return a set where (lender_id=lender_idIN AND county=countyIN) returns a value. However if it returns a NULL set then try the next condition set (lender_id=lender_idIN AND county='NA') and etc.

I dont think CASE will work for this issue and I once used nested IF EXIST statements which worked but forced each query to run twice (there has to be a better solution than that).

Any suggestions and thank you for the help.

最满意答案

你的问题不明确。 我假设... IN符号是参数而不是列，'NA'是client_rates中县的可能值，而在WHERE的最后AND条件中你要返回行（...），否则where（...），否则where（...）等（CASE表达式仅评估第一个真正的WHEN。）

SELECT FeeName, Amount, Category, VariableName FROM client_rates WHERE state_abv=state_abvIN AND Purchase=purchaseIN AND Category=CategoryIN AND client_id=client_idIN AND agent_id=agent_idIN AND CASE WHEN (lender_id=lender_idIN AND county=countyIN) THEN 1 WHEN (lender_id=lender_idIN AND county='NA') THEN 1 WHEN (lender_id=1 AND county=countyIN) THEN 1 ELSE (lender_id=1 AND county='NA') END;

PS：但是这是一个与以下不同的查询：当CASE之前的所有内容都匹配时，如果找不到lender_idIN，那么对于lender_id（无论是否存在lender_id = 1）返回1，如果countyIN不是发现然后为县返回'NA'（无论是否有县='NA'）。 （这可能涉及'NA'在client_rates中不是县的可能值。）当它们给出不同的结果时：如果找不到lender_idIN但是没有client_rates lender_id值为1和/或如果找不到countyIN但是没有client_rates'NA'的县值。 然后第一个查询将返回一个空表，但第二个查询返回一个表示lender_id和/或一个'NA'表示县。 让你的“意义......”清楚！ 包括查询“哪个工作”（如果你确定它确实）。

Your question is not clear. I'll assume that the ...IN symbols are parameters not columns, that 'NA' is a possible value for county in client_rates, and that in your WHERE's last AND condition you want to return rows where (...), otherwise where (...), otherwise where (...), etc. (A CASE expression evaluates only up to the first true WHEN.)

SELECT FeeName, Amount, Category, VariableName FROM client_rates WHERE state_abv=state_abvIN AND Purchase=purchaseIN AND Category=CategoryIN AND client_id=client_idIN AND agent_id=agent_idIN AND CASE WHEN (lender_id=lender_idIN AND county=countyIN) THEN 1 WHEN (lender_id=lender_idIN AND county='NA') THEN 1 WHEN (lender_id=1 AND county=countyIN) THEN 1 ELSE (lender_id=1 AND county='NA') END;

PS: But that is a different query from the following: When there is a match for everything before the CASE, if lender_idIN is not found then 1 is returned for lender_id (whether or not there is a lender_id=1) and if countyIN is not found then 'NA' is returned for county (whether or not there is a county='NA'). (This probably involves 'NA' not being a possible value for county in client_rates.) When they give different results: If lender_idIN is not found but there is no client_rates lender_id value of 1 and/or if countyIN is not found but there is no client_rates county value of 'NA'. Then the first query will return an empty table but the second query returns a 1 for lender_id and/or a 'NA' for county. Make your "Meaning..." clear! Include the query "which worked" (if you're sure it did).