SQL最喜欢的颜色测试

Question

假设如下表:

USER  COLOR  ORDER
==================
JOE   RED    1
JOE   BLUE   2 
PHIL  BLUE   3

Joe首先选择了红色，然后选择了蓝色作为他最喜欢的颜色…如何编写返回: 的查询?

COLOR COUNT
RED   1
BLUE  1

哪里只计算Joe的第一次投票?

Answer 1

您将获得每个USER的最低ORDER值，然后再次将其与表连接以获得这些记录的COLOR，然后按COLOR分组以对它们进行计数:

select y.COLOR, count(*) as COUNT
from (select USER, min(ORDER) as ORDER from TheTable group by USER) x
inner join TheTable y on x.USER = y.USER and x.ORDER = y.ORDER
group by COLOR

Answer 2

这与我在这里的回答基本相同，在这里我也提供了额外的解释。

SELECT color,count(*) AS count
FROM votes AS v
JOIN (
    SELECT user,MIN(order) AS order
    FROM votes
    GROUP BY user
) AS v2 ON (v.user=v2.user AND v.order=v2.order)
GROUP BY color;

Answer 3

最困难的部分是表中的大多数列都是保留关键字…

CREATE TABLE #Votes (
    [USER] VARCHAR(50),
    COLOR VARCHAR(50),
    [ORDER] INT
)
INSERT INTO #Votes ([USER], COLOR, [ORDER]) VALUES ('JOE', 'RED', 1)
INSERT INTO #Votes ([USER], COLOR, [ORDER]) VALUES ('JOE', 'BLUE', 2)
INSERT INTO #Votes ([USER], COLOR, [ORDER]) VALUES ('PHIL', 'BLUE', 2)
;WITH NumberedVotes AS (
    SELECT
        [USER],
        COLOR,
        ROW_NUMBER() OVER (PARTITION BY [USER] ORDER BY [ORDER]) AS RowNumber
    FROM #Votes
)
SELECT
    COLOR,
    COUNT(*) AS [COUNT]
FROM NumberedVotes
WHERE RowNumber = 1
GROUP BY COLOR
ORDER BY [COUNT] DESC

Answer 4

在TSQL 2005及以后的版本中:

SELECT color, COUNT(color) AS VOTES FROM
(SELECT *, ROW_NUMBER() OVER (PARTITION BY [user] ORDER BY [order]) AS Row FROM @votes) T
WHERE T.Row = 1 GROUP BY color

Answer 5

declare @tb table ( [user] varchar(10), color varchar(10), [order] int  identity (1,1))
insert @tb ([user],color) 
select 'JOE'  [user], 'RED' color 
union all
select 'JOE' [user], 'BLUE' color 
union all
select 'PHIL' [user], 'BLUE' color 

select color, count(user) from
(
select distinct p.color,t.[user] from @tb t 
        cross apply (select top 1 color from @tb e where e.[user] = t.[user] order by [order]) p
) u
group by color