在T-SQL上计数项频率的问题 [英] Problem counting item frequency on T-SQL
本文介绍了在T-SQL上计数项频率的问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想计算表格中不同字段的数字从1到100的频率。
假设我有表格Results以下数据：
LottoId赢利者二等奖
--------- --- --------- ---------
1 1 2 3
2 1 2 3
我想要能得到每个数字的频率。为此，我使用以下代码：
- 创建数字临时表
CREATE TABLE #Numbers b $ b Number int）
- 将数字插入临时表
declare @counter int
set @counter = 0
while @counter< 100
begin
set @counter = @counter + 1
INSERT INTO #Numbers（Number）VALUES（@counter）
end
-
SELECT＃Numbers.Number，Count（Results.Winner）as Winner，Count（Results.Second）as Second，Count（Results.Third）as Third FROM #Numbers
LEFT JOIN结果ON
＃Numbers.Number = Results.Winner OR＃Numbers.Number = Results.Second OR＃Numbers.Number = Results.Third
GROUP BY＃Numbers.Number
问题是计数对每个数字重复相同的值。在这种特殊情况下，我得到以下结果：
Number Winner Second Third
------ --- --------- --------- ---------
1 2 2 2
2 2 2 2
3 2 2 2
...
当我应该得到这个：
数字优胜者二等奖
--------- --------- ---- ----- ---------
1 2 0 0
2 0 2 0
3 0 0 2
...
我失踪了什么？
解决方案>
如果您正在使用SQL Server 2005 +
With
WinnerCounts As
$ b选择＃Numbers.Number，Count（Results.Winner）作为结果
FROM #Numbers
JOIN结果
On＃Numbers.Number = Results.Winner
）
，SecondCounts作为
（
选择＃Numbers.Number，Count（Results.Second）作为结果
FROM #Numbers
JOIN结果
On＃Numbers.Number = Results.Sond
）
，ThirdCounts作为
（
选择＃Numbers.Number，Count（Results.Third）作为结果
FROM #Numbers
JOIN结果
On＃Numbers.Number = Results.Third
）
选择Numbers.Number，Coalesce（WinnerCounts.Results，0）As Winner，Coalesce（SecondCounts.Result，0） Coalesce（ThirdCounts.Result，0）As Third
从#Numbers
左侧加入WinnerCounts
在WinnerCounts.Results =＃Numbers.Number
左侧加入SecondCounts
在SecondCounts .Results =＃Numbers.Number
Left Join ThirdCounts
On ThirdCounts.Results =＃Numbers.Number
另一个可能的解决方案，将在旧版本的SQL Server中工作：
选择＃Numbers.Number
，SUM（Winners.Winner不为空的情况下，则为1 Else 0结束）作为WinnerCount
，SUM（Seconds.Second不为空的情况下，则Else 0结束）作为SecondCount
，SUM （case当Thirds.Third不是Null然后1否则0 End）作为ThirdCount
从#Numbers
左连接结果作为优胜者
在Winners.Winner =＃Numbers.Number
左连接结果作为秒
On Seconds.Second =＃Numbers.Number
左连接结果三进制
On Thirds.Third =＃Numbers.Number
Group By＃Numbers.Number
I'm trying to count the frequency of numbers from 1 to 100 on different fields of a table.
Let's say I have the table "Results" with the following data:
LottoId Winner Second Third
--------- --------- --------- ---------
1 1 2 3
2 1 2 3
I'd like to be able to get the frequency per numbers. For that I'm using the following code:
--Creating numbers temp table
CREATE TABLE #Numbers(
Number int)
--Inserting the numbers into the temp table
declare @counter int
set @counter = 0
while @counter < 100
begin
set @counter = @counter + 1
INSERT INTO #Numbers(Number) VALUES(@counter)
end
--
SELECT #Numbers.Number, Count(Results.Winner) as Winner,Count(Results.Second) as Second, Count(Results.Third) as Third FROM #Numbers
LEFT JOIN Results ON
#Numbers.Number = Results.Winner OR #Numbers.Number = Results.Second OR #Numbers.Number = Results.Third
GROUP BY #Numbers.Number
The problem is that the counts are repeating the same values for each number. In this particular case I'm getting the following result:
Number Winner Second Third
--------- --------- --------- ---------
1 2 2 2
2 2 2 2
3 2 2 2
...
When I should get this:
Number Winner Second Third
--------- --------- --------- ---------
1 2 0 0
2 0 2 0
3 0 0 2
...
What am I missing?
解决方案
If you are using SQL Server 2005+
With
WinnerCounts As
(
Select #Numbers.Number, Count(Results.Winner) As Results
FROM #Numbers
JOIN Results
On #Numbers.Number = Results.Winner
)
, SecondCounts As
(
Select #Numbers.Number, Count(Results.Second) As Results
FROM #Numbers
JOIN Results
On #Numbers.Number = Results.Second
)
, ThirdCounts As
(
Select #Numbers.Number, Count(Results.Third) As Results
FROM #Numbers
JOIN Results
On #Numbers.Number = Results.Third
)
Select Numbers.Number, Coalesce(WinnerCounts.Results,0) As Winner, Coalesce(SecondCounts.Result,0) As Second, Coalesce(ThirdCounts.Result,0) As Third
From #Numbers
Left Join WinnerCounts
On WinnerCounts.Results = #Numbers.Number
Left Join SecondCounts
On SecondCounts.Results = #Numbers.Number
Left Join ThirdCounts
On ThirdCounts.Results = #Numbers.Number
Another possible solution which will work in older versions of SQL Server:
Select #Numbers.Number
, SUM( Case When Winners.Winner Is Not Null Then 1 Else 0 End ) As WinnerCount
, SUM( Case When Seconds.Second Is Not Null Then 1 Else 0 End ) As SecondCount
, SUM( Case When Thirds.Third Is Not Null Then 1 Else 0 End ) As ThirdCount
From #Numbers
Left Join Results As Winners
On Winners.Winner = #Numbers.Number
Left Join Results As Seconds
On Seconds.Second = #Numbers.Number
Left Join Results As Thirds
On Thirds.Third = #Numbers.Number
Group By #Numbers.Number
这篇关于在T-SQL上计数项频率的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
