如何从含算术运算符的表中查询范围并获取指定col5值？

1. How to Perform Range Queries Using Arithmetic Operators from the Same Table #

To run range queries that involve arithmetic operations on columns within the same table, you can directly embed the arithmetic expressions in your WHERE clause. This works for any combination of operators (<, >, =, BETWEEN, etc.) and column calculations.

For example:

• If you want rows where the sum of colA and colB falls between 100 and 200:

  SELECT * FROM your_table
  WHERE (colA + colB) BETWEEN 100 AND 200;
  

• If you need rows where colC is greater than twice colD:

  SELECT * FROM your_table
  WHERE colC > (2 * colD);
  

You can also use computed columns (persisted or virtual) if you frequently use the same arithmetic expression, but inline expressions are perfect for ad-hoc queries. Just make sure to wrap complex calculations in parentheses to avoid operator precedence issues.

2. Retrieving col5 Value for Input Integer 74 #

First, let's clarify the logic from your table data. It looks like each row defines a set of conditions using operators in col3 (against col1) and col4 (against col2). When the input value satisfies both conditions, we return the corresponding col5.

For input 74, the row with col5 = 'Approved' matches because:

• 74 < 75 (aligns with col3 = '<' and col1 =75)
• 74 > 0 (assuming col4 = '>' for this row, as the original data formatting seems to have omitted this operator)

Since SQL doesn't let you use string values directly as operators, we'll use CASE statements to map each operator string to the correct boolean condition. Here's the query:

SELECT col5
FROM `table`
WHERE
  -- Evaluate condition for col1 and col3
  CASE col3
    WHEN '<' THEN 74 < col1
    WHEN '>' THEN 74 > col1
    WHEN '=' THEN 74 = col1
    ELSE FALSE -- Handle unexpected operators
  END
  AND
  -- Evaluate condition for col2 and col4; handle NULL if col4 is optional
  CASE
    WHEN col4 IS NULL THEN TRUE -- No condition to check
    WHEN col4 = '<' THEN 74 < col2
    WHEN col4 = '>' THEN 74 > col2
    WHEN col4 = '=' THEN 74 = col2
    ELSE FALSE
  END;

Quick Notes: #

• If your table's col4 for the Approved row is actually a different operator (not missing), adjust the CASE statement accordingly.
• To make this dynamic (use any input value instead of hardcoding 74), you can use a parameterized query (e.g., ? in prepared statements) or a session variable depending on your SQL dialect.

内容的提问来源于stack exchange，提问作者Nikhil