如何在SELECT COUNT语句中使用CASE语句？ [英] How to use a CASE statement within a SELECT COUNT statement?

问题描述

我需要做一个case语句。

根据变量的值，它需要从表中选择正确的列

StartDate和EndDate是不同的变量。

我创建了一个名为Region的变量，它应该确定查询选择哪个列。 p>

EDIT：Region可以是EW表示英格兰和威尔士，SC表示苏格兰或NI表示北爱尔兰。
如果是EW，则应选择第1列，第2列为SC，第3列为NI。

  SELECT 
 COUNT（COLUMN1）
 FROM bankholidays 
 WHERE COLUMN1 BETWEEN @StartDate AND @EndDate）
  

解决方案

1）如果您不关心从您的计数中排除null，您不需要在COUNT语句中指定列名称。即

  select count（Column1）
  

将给出与

相同的结果

  select count（1）
  

或

  select count （*）
  

只要column1没有空值。如果column1确实包含null，那么它们不会被计数（所以如果有10条记录，其中3条在column1中有空值，那么使用 count（column1）或使用其他方法的计数为10。

我提到这一点，如果你关心nulls然后改变这里使用的列是有意义的; if你不用，只是 count（1） / counnt（*）的简单逻辑。 p>

所有这一切，这里是如何更改该列：

  select count （
 case @Region 
当1然后Column1 
当2然后Column2 
 else Column3 
 end 
）
  

2）如果要更改WHERE语句中使用的列，有以下几种方法：

SELECT COUNT（1）
从bankholidays
WHERE case @Region
当1然后Column1
当2然后Column2
else Column3
end BETWEEN @StartDate AND @EndDate


或

  SELECT COUNT（1）
 FROM bankholidays 
 WHERE（@Region = 1 and Column1 BETWEEN @StartDate AND @EndDate ）
或（@Region = 2和Column2 BETWEEN @StartDate AND @EndDate 
或（@Region不在（1,2）和列3 BETWEEN @StartDate AND @EndDate 
  

我喜欢上面的第一种风格，因为它涉及较少的重复;然而第二种风格提供了选择使用不同的开始&结束日期，或者添加其他逻辑，所以仍然值得注意。

I need to make a case statement.

Depending on what the variables value is, it needs to select the correct column from the table

StartDate and EndDate are different variables.

There is a variable i created called Region which should determine what column the query selects.

EDIT: Region can either be 'EW' for England and Wales, 'SC' for Scotland or 'NI' for Northern Ireland. If it is EW it should select column 1, SC for column 2, NI for column 3

SELECT
COUNT(COLUMN1)
FROM  bankholidays
WHERE COLUMN1 BETWEEN @StartDate AND @EndDate)

解决方案

1) If you're not concerned about excluding nulls from your count, you don't need to specify a column name in your COUNT statement. i.e.

select count(Column1)

Will give the same result as

select count(1)

Or

select count(*)

So long as column1 has no null values. If column1 does contain nulls, those aren't counted (so if there are 10 records, 3 of which have null values in column1, you'd get a result of 7 using count(column1) or a count of 10 using the other methods.

I mention this first as if you care about nulls then changing which column is used here makes sense; if you don't, go with the simpler logic of just count(1) / counnt(*).

All that said, here's how to change that column:

select count(
    case @Region 
        when 1 then Column1 
        when 2 then Column2 
        else Column3 
    end
)

2) If you want to change the column used in your WHERE statement, there are a couple of approaches:

SELECT COUNT(1)
FROM  bankholidays
WHERE case @Region 
    when 1 then Column1 
    when 2 then Column2 
    else Column3        
end BETWEEN @StartDate AND @EndDate

or

SELECT COUNT(1)
FROM  bankholidays
WHERE (@Region = 1 and Column1 BETWEEN @StartDate AND @EndDate)
or (@Region = 2 and Column2 BETWEEN @StartDate AND @EndDate
or (@Region not in (1,2) and Column3 BETWEEN @StartDate AND @EndDate

Personally I prefer the first style above, since it involves less repetition; however the second style offers the option to use different start & end dates for the different columns, or to add in other logic too, so is still worth being aware of.

这篇关于如何在SELECT COUNT语句中使用CASE语句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！