TSQL:在所有行的每列中查找奇数和偶数

Question

我有一个包含6列的表，其中包含从1到90的随机数。

nr1 nr2 nr3 nr4 nr5 nr6
-----------------------
12  26  70  74  84  87
6   13  19  37  67  72
29  31  35  49  69  74
3   14  30  50  66  87
6   10  13  17  22  46
28  29  33  35  65  80
25  31  43  61  63  86
12  20  22  39  55  72
9   12  28  71  82  85
5   13  28  30  42  63
33  37  48  65  83  84
3   10  40  54  69  85
6   19  30  53  55  76
17  41  42  43  66  76
2   22  28  39  61  79
26  37  53  81  86  90
2   51  55  57  61  82
1   18  30  34  65  75
18  28  40  63  68  86

我需要生成一个查询，该查询将提供以下结果：

Total ODD numbers in a table: 
Total EVEN numbers in a table: 
Total number of rows containing 6 even numbers:
Total number of rows containing 6 odd numbers:
Total number of rows containing 5 even and 1 odd number:
Total number of rows containing 5 odd and 1 even number:
Total number of rows containing 4 even and 2 odd numbers:
Total number of rows containing 4 odd and 2 even numbers:
Total number of rows containing 3 even and 3 odd numbers:

到目前为止，我只知道如何计算只包含偶数或奇数的行数，但我被其他问题所困扰

SELECT COUNT(*) AS ROWS_ODDS FROM table
WHERE nr1 %2!=0 AND nr2 %2!=0 AND nr3 %2!=0 AND nr4 %2!=0 AND nr5 %2!=0 AND nr6 %2!=0
SELECT COUNT(*) AS ROWS_EVENS FROM table
WHERE nr1 %2=0 AND nr2 %2=0 AND nr3 %2=0 AND nr4 %2=0 AND nr5 %2=0 AND nr6 %2=0

有什么想法吗？非常感谢。

Answer 1

简单地求模结果的和：

with cte as 
(
SELECT 
nr1 %2 + nr2 %2 + nr3 %2 + nr4 %2 + nr5 %2 + nr6 %2 as odd_values
FROM tab
)
select odd_values, 6-odd_values as even_values, count(*)
from cte 
group by odd_values
-- this adds the grand total
union all
select
sum(odd_values), sum(6-odd_values), -1
from cte

格式化输出由您决定：-(

参见小提琴

Answer 2

您可以使用横向联接来取消平移行，并计算每行的偶数和奇数值的数量。

select 
sum(cnt_even) total_even_values,
sum(cnt_odd)  total_odd_values,
sum(case when cnt_even = 6 then 1 else 0 end) cnt_rows_with_6_even_numbers,
sum(case when cnt_even = 5 then 1 else 0 end) cnt_rows_with_5_even_numbers,
sum(case when cnt_even = 4 then 1 else 0 end) cnt_rows_with_4_even_numbers,
...
sum(case when cnt_even = 0 then 1 else 0 end) cnt_rows_with_0_even_numbers
from mytable
cross apply (
select sum(1 - is_odd) as cnt_even, sum(is_odd) as cnt_odd
from (values (nr1 % 2), (nr2 % 2), (nr3 % 2), (nr4 % 2), (nr5 % 2), (nr6 % 2)) as x(is_odd)
) x

Answer 3

对于每一行，您可以通过将条件转换为int并相加来计算奇数或偶数的数量：

SELECT ( cast( (nr1 % 2==0) as int)+ cast( (nr2 % 2==0) as int) + ....) as even,
( cast( (nr1 % 2!=0) as int)+ cast( (nr2 % 2!=0) as int) + ....) as odd FROM table;

在你得到所有的偶数和奇数计数后，你可以计算出你要求的所有结果。