如何在 where 子句之后的 between 子句中提供多个值 [英] How to supply multiple values in between clause after where clause

问题描述

SELECT 
    ROW_NUMBER() OVER (ORDER BY Vendor_PrimaryInfo.Vendor_ID ASC) AS RowNumber, 
    *
FROM  
    Unit_Table 
INNER JOIN
    Vendor_Base_Price ON Unit_Table.Unit_ID = Vendor_Base_Price.Unit_ID 
INNER JOIN
    Vendor_PrimaryInfo ON Vendor_Base_Price.Vendor_ID = Vendor_PrimaryInfo.Vendor_ID 
INNER JOIN
    Vendor_Registration ON Vendor_Base_Price.Vendor_ID = Vendor_Registration.Vendor_ID 
                        AND Vendor_PrimaryInfo.Vendor_ID = Vendor_Registration.Vendor_ID 
INNER JOIN
    Category_Table ON Vendor_Registration.Category_ID = Category_Table.Category_ID
LEFT JOIN
    Vendor_Value_Table ON Vendor_Registration.Vendor_ID = Vendor_Value_Table.Vendor_ID 
LEFT JOIN
    Feature_Table ON Vendor_Value_Table.Feature_ID = Feature_Table.Feature_ID
WHERE
    Vendor_Registration.Category_ID = 5 
    AND Vendor_PrimaryInfo.City = 'City'
    AND (value_text in ('sample value') or 
         (SELECT 
              CASE WHEN ISNUMERIC(value_text) = 1 
                      THEN CAST(value_text AS INT)   
                      ELSE -1 
              END) BETWEEN 0 AND 100)

因为列有多个值，可能是 text 也可能是 int 这就是我根据大小写进行转换的原因.我的问题是:我只想获取值在 0 到 100 之间或值在 300 到 400 之间或值类似于样本值的记录.

As column has multiple values which may be text or may be int that's why I cast based on case. My question is: I just want to fetch the records either whose value is between 0 and 100 or value between 300 to 400 or value is like sample value.

我只想将条件放在 where 子句之后，不想在运算符之间多次使用 column_name，因为这些值来自 url

I just want to place the condition after where clause and do not want to use column_name multiple time in between operator because these values are coming from url

预先感谢任何帮助将不胜感激.

Thanks in advance any help would be grateful.

推荐答案

你可以试试这个方法..

You can try this way..

WHERE
    Vendor_Registration.Category_ID = 5 
    AND Vendor_PrimaryInfo.City = 'City'
    AND (value_text in ('sample value') or 
         (CASE WHEN (ISNUMERIC(value_text) = 1)
                      THEN CAST(value_text AS INT)   
                      ELSE -1 
              END) BETWEEN 0 AND 100)

这篇关于如何在 where 子句之后的 between 子句中提供多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！