如何查询int列中的任何值?

How can you query a column for any value in that column? (ie. How do I build a dynamic where clause that can either filter the value, or not.)

如何查询列中的任何值?(即。如何构建一个动态where子句，该子句可以过滤值，也可以不过滤值。

I want to be able to query for either a specific value, or not. For instance, I might want the value to be 1, but I might want it to be any number.

我希望能够查询一个特定的值，或者不是。例如，我可能希望这个值是1，但是我可能希望它是任何数字。

Is there a way to use a wild card (like "*"), to match any value, so that it can be dynamically inserted where I want no filter?

是否有一种方法可以使用通配符(如“*”)来匹配任何值，以便可以在不需要过滤器的地方动态插入它?

For instance:

例如:

select int_col from table where int_col = 1  // Query for a specific value
select int_col from table where int_col = *  // Query for any value

The reason why I do not want to use 2 separate SQL statements is because I am using this as a SQL Data Source, which can only have 1 select statement.

我不想使用两个单独的SQL语句的原因是，我将它用作一个SQL数据源，它只能有一个select语句。

7 个解决方案

#1

Sometimes I would query for actual value (like 1, 2...) so I can't not have a condition either.

有时我会查询实际值(比如1,2…)，所以我也不能没有条件。

I take it you want some dynamic behavior on your WHERE clause, without having to dynamically build your WHERE clause.

我认为您需要在WHERE子句上执行一些动态行为，而不必动态构建WHERE子句。

With a single parameter, you can use ISNULL (or COALESCE) like this:

使用一个参数，您可以像这样使用ISNULL(或联合):

 SELECT * FROM Table WHERE ID = ISNULL(@id, ID)

which allows a NULL parameter to match all. Some prefer the longer but more explicit:

这允许一个空参数匹配所有。有些人喜欢更长的但更明确的:

 SELECT * FROM Table WHERE (@id IS NULL) OR (ID = @id)

#2

A simple answer would be use: IS NOT NULL. But if you are asking for say 123* for numbers like 123456 or 1234 or 1237 then the you could convert it to a varchar and then test against using standard wild cards.

一个简单的答案是:NOT NULL。但是如果你要求123*，比如123456或1234或1237，那么你可以将它转换为varchar，然后测试使用标准的通配符。

In your where clause: cast(myIntColumn as varchar(15)) like '123%'.

在你的where子句:cast(myIntColumn as varchar(15))，比如“123%”。

#3

Assuming the value you're filtering on is a parameter in a stored procedure, or contained in a variable called @Value, you can do it like this:

假设您过滤的值是存储过程中的一个参数，或者包含在一个名为@Value的变量中，您可以这样做:

select * from table where @Value is null or intCol = @Value

If @Value is null then the or part of the clause is ignored, so the query won't filter on intCol.

如果@Value为null，则忽略子句的or部分，因此查询不会对intCol进行过滤。

#4

I don't quite understand what you're asking. I think you should use two different queries for the different situations you have.

我不太明白你在问什么。我认为您应该针对不同的情况使用两个不同的查询。

When you're not looking for a specific value:

当你不寻找特定的价值时:

SELECT * FROM table

When you are looking for a specific value:

当你在寻找一个特定的值时:

SELECT * FROM table WHERE intcol = 1

#5

You can use the parameter as a wildcard by assigning special meaning to NULL:

可以将参数用作通配符，将特殊意义赋给NULL:

DECLARE @q INT = 1
SELECT * FROM table WHERE IntegerColumn = @q OR @q IS NULL

This way, when you pass in NULL; you get all rows.

这样，当你传入NULL;你得到的所有行。

If NULL is a valid value to query for, then you need to use two parameters.

如果NULL是要查询的有效值，那么需要使用两个参数。

#6

If you really want the value of your column for all rows on the table you can simply use

如果您确实希望为表上的所有行指定列的值，那么您可以使用它

select int_col
  from table

If you want to know all the distinct values, but don't care how many times they're repeated you can use

如果你想知道所有不同的值，但不管重复多少次，你都可以使用。

select distinct int_col
  from table

And if you want to know all the distinct values and how many times they each appear, use

如果你想知道所有不同的值以及它们出现的次数，就用

select int_col, count(*)
  from table
  group by int_col

To have the values sorted properly you can add

要正确排序值，可以添加

  order by int_col

to all the queries above.

对于上面的所有查询。

Share and enjoy.

分享和享受。

#7

The equivalent of wildcards for numbers are the comparators.

数字的通配符是比较器。

So, if you wanted to find all positive integers:

如果你想找到所有的正整数

select int_col from table where int_col > 0

any numbers between a hundred and a thousand:

一百到一千之间的任何数字:

select int_col from table where int_col BETWEEN 100 AND 1000

and so on.

等等。

#1