对T-SQL问题计数项目频率(Problem counting item frequency on

我想数字从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
...

我在想什么？

Answer 1:

如果您正在使用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

Answer 2:

您可以使用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

文章来源: Problem counting item frequency on T-SQL