代码之家 › 专栏 › 技术社区 › Raúl Roa

在t-sql上计算项目频率时出现问题

frequency count sql-server-2008 tsql

Raúl Roa · 技术社区 · 14 年前

我试图在一个表的不同字段上计算从1到100的数字的频率。

假设我有一个包含以下数据的“结果”表:

LottoId   Winner    Second    Third                                                                                                                                                                                                                                                            
--------- --------- --------- ---------
1         1         2         3                                                                                                                                                                                                                                                                
2         1         2         3

我想知道每个号码的频率。为此,我使用以下代码:

--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

问题是计数对每个数字重复相同的值。在这种情况下,我得到了以下结果:

Number    Winner    Second    Third                                                                                                                                                                                                                                                            
--------- --------- --------- ---------
1         2         2         2                                                                                                                                                                                                                                                                
2         2         2         2 
3         2         2         2
...

当我得到这个:

Number    Winner    Second    Third                                                                                                                                                                                                                                                            
--------- --------- --------- ---------
1         2         0         0                                                                                                                                                                                                                                                                
2         0         2         0 
3         0         0         2
...

我错过了什么?

2 回复 | 直到 14 年前

Thomas 14 年前

如果您使用的是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

另一个可能在旧版本的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

Anthony Faull 14 年前

您可以使用pivot和unpivot。

SELECT Number, Winner, Second, Third
FROM (SELECT LottoID, Ranking, Number
        FROM Lotto UNPIVOT (Number FOR Ranking 
        IN ([Winner], [Second], [Third])) AS unpvt) flat
     PIVOT (COUNT(LottoId) FOR Ranking
     IN ([Winner], [Second], [Third])) crosstab