使用变量在bash中生成where子句 [英] generate where clause in bash using variables

问题描述

我在bash中有一些变量，如下所示.

I have some variables in bash like below.

min_date='2020-06-06'
max_date='2020-06-08'
max_seq_min_date=1  --This is till where data is processed
max_seq_max_date=3  --This is till where data has to be processed

batch_date is the column where I will use the min and max_date values

每个日期将有4个序列

我想生成一个可以在sql查询中使用的where子句

I want to generate a where clause that I can use in sql query

现在我想生成一个如下所示的where子句

Now I want to generate a where clause that looks like below

WHERE 1=1 or (batch_date = '2020-06-06' and seq_num in ('2','3', '4')) 
or (batch_date = '2020-06-07' and seq_num in ('1','2','3','4'))
or (batch_date = '2020-06-08' and seq_num in ('1','2','3'))

我如何实现我想要的?

推荐答案

对查询进行较小的更改将更加高效-使其更容易动态生成(等效)SQL.

It will be more efficient to make minor changes to the query - making it easier to generate (equivalent) SQL dynamically.

它使用"between"运算符来避免"in(...)"条件下的可变长度列表.

It uses "between" operator to avoid variable length lists for the 'in (...)' conditions.

请注意有关1 = 1的注释，该注释按问题保留，但需要复查，因为它始终会使条件通过.

Note comment about 1=1, it is kept as per question, but need to be reviewed, as it will always make the condition pass.

min_date='2020-06-06'
max_date='2020-06-08'
max_seq_min_date=1
max_seq_max_date=3

echo "
WHERE 1 = 1 or case
    when batch_date = '$min_date' then seq_num between 1 and $max_seq_min_date
    when batch_date = '$max_date' then seq_num between 1 and $max_seq_max_date
    when batch_date between '$min_date' and '$max_date' then seq_num between 1 and 4
    else false
    end
" 

我没有mysql，但以上方法适用于Postgresql.

I do NOT have mysql, but the above works for Postgresql.

这篇关于使用变量在bash中生成where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！