在一个sql查询中计算很多布尔字段？

Not sure exactly how to explain this but Imaging you have a table with lots of boolean fields like this......

不确定如何解释这个但是想象你有一个包含很多布尔字段的表......

Table: Cars

表：汽车

Columns:

列：

Automatic: boolean

自动：布尔值

Silver: boolean

银色：布尔值

American: boolean

美国人：布尔

Noisy: boolean

吵：布尔

Smelly: boolean

臭臭：布尔

fast: boolean

fast：布尔值

(silly fields and most of them wouldn't be bools in reality but just an example)

（愚蠢的田地，其中大部分都不会在现实中沸腾，只是一个例子）

What I need to do is produce a list of these fields with a number of search results next to each one, so if there were 100 silver cars in the database and 57 American cars the list might look a bit like this...

我需要做的是生成这些字段的列表，每个字段旁边都有一些搜索结果，所以如果数据库中有100辆银色车和57辆美国车，那么列表可能看起来有点像......

Automatic: (150)

自动：（150）

Silver (100)

银（100）

American (57)

美国人（57）

Noisy (120)

吵闹（120）

Smelly (124)

臭臭（124）

fast (45)

快（45）

So, it's basically like a list of filters, if the user clicks "silver" they will narrow the search down to only show silver cars and they know they will get 100 results. The numbers next to all the other filters will then decrease because we've filtered out all cars that aren't silver.

所以，它基本上就像一个过滤器列表，如果用户点击“白银”，他们会缩小搜索范围，只显示银色汽车，他们知道他们将得到100个结果。然后，所有其他过滤器旁边的数字将减少，因为我们已经过滤掉所有非银色的汽车。

Counting the occurences of one field would be easy....

计算一个场的出现很容易......

SELECT COUNT(*) FROM CARS WHERE Automatic = true;

SELECT COUNT（*）FROM CARS WHERE Automatic = true;

... would give me the first row for example. But I don't want to have to do one SQL statement for each filter as there could be over 30 of them. I've seen plenty of sites do this so it must be easier than I think it is.

......例如，我会给第一行。但是我不想为每个过滤器做一个SQL语句，因为它们可能超过30个。我见过很多网站这样做，所以它必须比我想象的要容易。

Any help would really be appreciated :)

真的很感激任何帮助:)

Jon

乔恩

3 个解决方案

#1

Assuming the bit is 1/0 then you can SUM instead of COUNT:

假设该位为1/0，则可以使用SUM而不是COUNT：

SELECT SUM(Automatic) as Automatic, SUM(Smelly) as Smelly, SUM(American) as Japanese FROM ...

#2

With sql server 2008 when trying to sum a field of data type bit the following error ocures:

使用sql server 2008时，尝试对数据类型的字段进行求和时，会出现以下错误：

Msg 8117, Level 16, State 1, Line 10

Msg 8117，Level 16，State 1，Line 10

Operand data type bit is invalid for sum operator.

操作数数据类型位对于sum运算符无效。

so you can try this:

所以你可以试试这个：

SELECT SUM(CAST(Automatic AS TINYINT)) as Automatic, SUM(CAST(Smelly AS TINYINT)) as Smelly, SUM(CAST(American AS TINYINT)) as Japanese FROM YourTable

#3

If it's not already 1/0, then, based on whatever the truevalue is, you can do this:

如果它还不是1/0，那么，根据truevalue的不同，你可以这样做：

  Select
     Sum(Case When Automatic = truevalue Then 1 Else 0 End) as Automatic,
     Sum(Case When Smelly = truevalue   Then 1 Else 0 End) as Smelly,
     Sum(Case When American = truevalue   Then 1 Else 0 End) as American,
     Sum(Case When Noisy = truevalue   Then 1 Else 0 End) as Noisy
  From Table

#1