Saturday, January 31, 2009

a small t-sql puzzle

A friend of mine found a small sql puzzle online somewhere and told me about that.I know it is not that hard to solve the problem using t-sql but I want to find out all the possible ways that I can to solve the puzzle.

Two tables
1.Users with their color assignments
2.All the possible colors.

Problem:
Find all the people that has all the colors in the second table.

Solutions:

Declare @usrs table(
[name] varchar(5),
Color varchar(10)
)
Declare @AllColors table(
Color varchar(10)
)
insert into @usrs values('a','red')
insert into @usrs values('a','green')
insert into @usrs values('a','blue')
insert into @usrs values('b','red')
insert into @usrs values('b','green')
insert into @usrs values('c','blue')
insert into @usrs values('c','red')
insert into @usrs values('c','green')
insert into @usrs values('d','blue')
insert into @usrs values('d','red')
insert into @usrs values('e','green')
insert into @usrs values('e','blue')


insert into @allColors values('red')
insert into @allColors values('green')
insert into @allColors values('blue')
--Solution1
Declare @AllColorsString Varchar(Max)
Select @AllColorsString = isnull(@AllColorsString,'')+ Color +','
From @AllColors
Order by Color ;
Print @AllColorsString;
With usrsNColors as
(
SELECT u1.name,
( SELECT Color + ','
FROM @usrs u2
WHERE u2.name = u1.name
ORDER BY color
FOR XML PATH('') ) AS ColorsAssigned
FROM @usrs u1
GROUP BY [name]
)
Select [Name],ColorsAssigned
From usrsNColors
Where ColorsAssigned = @AllColorsString;

--Solution2
SELECT DISTINCT Name, Colors
FROM @usrs u1
CROSS APPLY ( SELECT Color + ','
FROM @usrs u2
WHERE u2.Name = u1.Name
ORDER BY Color
FOR XML PATH('') ) D ( Colors )
Where Colors = @AllColorsString;
--Solution3
WITH CTE ( [name], colorList, ctecolor, length )
AS ( SELECT name, CAST( '' AS VARCHAR(8000) ), CAST( '' AS VARCHAR(8000) ), 0
FROM @usrs
GROUP BY [name]
UNION ALL
SELECT p.[name], CAST( colorList +
CASE WHEN length = 0 THEN '' ELSE ', ' END + color AS VARCHAR(8000) ),
CAST( color AS VARCHAR(8000)), length + 1
FROM CTE c
INNER JOIN @usrs p
ON c.name = p.name
WHERE p.color> c.ctecolor )
select [name],colorList from cte
where length = (select count(*) from @AllColors)
Order by [name];

--Solution 4 (very similar to 3)
WITH CTE ( [name], colorList, ctecolor, length )
AS ( SELECT name, CAST( '' AS VARCHAR(100) ), CAST( '' AS VARCHAR(100) ), 0
FROM @usrs
GROUP BY [name]
UNION ALL
SELECT p.[name], CAST( colorList +
CASE WHEN length = 0 THEN '' ELSE ',' END + color AS VARCHAR(100) ),
CAST( color AS VARCHAR(100)), length + 1
FROM CTE c
INNER JOIN @usrs p
ON c.name = p.name
WHERE p.color> c.ctecolor )
select [name],colorList from cte
where colorList+',' = @AllColorsString
Order by [name];

--Solution5
With usrsNColors as
(
select row_number() over (partition by Name order by Color asc) as rownum, Name,Color
from @usrs
)
,ColorCntForUsr AS
(
Select Name,Max(rownum) as TotalColors From usrsNColors
Group By Name
)
Select * from ColorCntForUsr
Where TotalColors = (Select Count(*) from @AllColors);

--Solution6
select Name, Sum(case when a.color = u.color then 1 else 0 end) as matchCount from @usrs u
cross Join @AllColors a
Group by Name
Having Sum(case when a.color = u.color then 1 else 0 end) = (Select Count(*) from @AllColors);

--Solution7
Very similar to 6 but with a Inner Join.

No comments:

Post a Comment

IIS Manager Crashes on Load/Start Windows 7, IIS 7.5

IIS Manager Crashes on Start  Windows 7, IIS 7.5, Power Shell 5.1. Here is the error I got in the event viewer. IISMANAGER_CRASH IIS Ma...