使用动态WHERE语句创建SQL查询 [英] Create SQL query with dynamic WHERE statement

问题描述

尽管我想找到一种纯SQL方法而不是特定于Postgresql的实现，但我还是以Postgresql作为数据库，

I'm using Postgresql as my database, in case that's helpful, although I'd like to find a pure SQL approach instead of a Postgresql specific implementation.

我有大量的制造电子产品的测试数据，我想从测试过程中获取满足某些条件的设备中提取数据，最好使用单独的表格，其中包含每个设备的测试条件

I have a large set of test data obtained from manufacturing a piece of electronics and I'd like to take that set of data and extract from it which units met certain criteria during test, ideally using a separate table that contains the test criteria from each step of manufacturing.

作为一个简单的例子，假设我在两个不同的测试步骤中检查了设备的温度回读。在步骤1中，温度应在20C-30C的范围内，而步骤2在50C-60C的范围内。

As a simple example, let's say I check the temperature readback from the unit in two different steps of the test. In step 1, the temperature should be in the range of 20C-30C while step 2 should be in the range of 50C-60C.

让我们假设以下表格结构为一组示例数据（表名 test_data）：

Let's assume the following table structure with a set of example data (table name 'test_data'):

temperature   step   serial_number
    25          1        1
    55          2        1
    19          1        2
    20          2        2

下表包含上述通过条件（表名 criteria）：

and let's assume the following table that contains the above mentioned pass criteria (table name 'criteria'):

temperature_upper   temperature_lower   step
        20                 30              1
        50                 60              2

此刻，我可以使用静态方法使用以下查询：

At the moment, using a static approach, I can just use the following query:

    SELECT * FROM test_data  WHERE       
  ( test_data.step = 1 AND test_data.temperature > 20 AND test_data.temperature < 30 ) OR   
  ( test_data.step = 2 AND test_data.temperature > 50 AND test_data.temperature < 60 );

这将有效地产生下表：

temperature   step   serial_number
    25          1        1
    55          2        1

我想使我的选择查询更具动态性，而不是开始静态定义，而是使其从test_criteria表的结果列表中构造自身。希望将其扩展为一个复杂的查询，例如，可以在步骤1中检查温度，电压和电流，但在步骤2中仅检查电流。

I'd like to make my select query more dynamic and instead of begin statically defined, make it construct itself off of a list of results from the test_criteria table. The hope is to grow this into a complex query where temperature, voltage and current might be checked in step 1 but only current in step 2, for example.

感谢任何

推荐答案

您可以使用表之间的联接来解决

You can solve using a join between the tables

SELECT t.*
FROM test_data  t
INNER JOIN criteria c ON t.step =  c.step 
  AND t.temperature > c.temperature_upper 
      AND t.temperature < c.temperature_lower

或如果您想> =和< =

OR if you want >= and <=

SELECT t.*
FROM test_data  t
INNER JOIN criteria c ON t.step =  c.step 
  AND t.temperature netween c.temperature_upper AND  c.temperature_lower

这篇关于使用动态WHERE语句创建SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！