PostgreSQL如何基于字段中的比较条件匹配数值查询价格？

Got it, let's tackle this problem. The core issue here is that your Condition_weight column stores text-based comparison rules, so we need a way to evaluate those rules against your input numeric value. Here are two practical approaches that match the "concise query" you're remembering:

1. Dynamic SQL (The "concise" approach you likely recall) #

This method builds a SQL query on the fly by combining your input value with the stored condition string. It’s clean and directly reuses the logic in your table.

Example for SQL Server: #

-- Replace @InputValue with your actual numeric input
DECLARE @InputValue INT = 30;
DECLARE @DynamicSQL NVARCHAR(MAX);

-- Build the query string
SET @DynamicSQL = N'
SELECT Price 
FROM Table_1 
WHERE Type = ''A'' 
AND ' + CAST(@InputValue AS NVARCHAR(10)) + ' ' + Condition_weight;

-- Execute the dynamically built query
EXEC sp_executesql @DynamicSQL;

Important Note: #

Be cautious with dynamic SQL if your input comes from untrusted sources—it can expose you to SQL injection risks. Always validate or parameterize user inputs if this is a public-facing system.

2. CASE Expression (Safer, non-dynamic alternative) #

If you want to avoid dynamic SQL entirely, you can map each stored condition to explicit logical checks using a CASE statement. This is more secure but requires updating the query if new conditions are added to the table.

Example: #

DECLARE @InputValue INT = 30;

SELECT Price
FROM Table_1
WHERE Type = 'A'
AND CASE
    WHEN Condition_weight = '>50' THEN @InputValue > 50
    WHEN Condition_weight = '>10 & <50' THEN @InputValue > 10 AND @InputValue < 50
    WHEN Condition_weight = '<10' THEN @InputValue < 10
    ELSE 0 -- Ignore any unrecognized conditions
END = 1;

Both methods will return the correct Price for your input value. The dynamic SQL approach is the one you probably saw before—it’s concise and leverages the existing condition strings directly.

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