هفتمین چالش SQL (بررسی دو به دو مشترک بودن مجموعه ها)
figure.png
با حل این مساله به زبان SQL شاهد قابلیت های ذهنی خود خواهید بود.
توضیح تکمیلی:
لیست تمام ترکیبات 2 از 4 برابر است با:
{ {a, b}, {a, c}, {a, d}, {b, c}, {b, d}, {c, d} }
پس باید بین این زوج مجموعه ها عنصر مشترک وجود داشته باشد تا "دو به دو مشترک" باشند.
برای تست راه حل از اسکریپت زیر استفاده شود.
create table table_1
([group] int not null,
element int not null,
primary key([group], element));
insert into table_1
values (1,3),(1,5),(1,6),
(2,4),(2,6),(2,7),
(3,4),(3,5),(3,8),
(4,3),(4,7),(4,8);
راه حل های پذیرفته شده:
1-cherchil_era
مرحبا به این راه حل
SELECT CASE SUM([Count])%((SUM([sum]) * (SUM([sum]) -1)))
WHEN 0 THEN 'Yes'
ELSE 'No'
END
FROM (
SELECT COUNT(g2) AS [Count],1 AS [SUM]
FROM (
SELECT t1.[group] AS g1,
t2.[group] AS g2
FROM dbo.Table_1 AS t1
LEFT OUTER JOIN dbo.Table_1 AS t2
ON t1.element = t2.element
AND t1.[group] <> t2.[group]
GROUP BY
t1.[group],
t2.[group]
) AS t
GROUP BY g1
) AS result
2-behrouzlo
SELECT ( CASE WHEN SUM(P) = ( SELECT ( COUNT(DISTINCT [group]) - 1 )
* ( COUNT(DISTINCT [group]) )
FROM table_1
) THEN 'Yes'
ELSE 'No'
END ) Result
FROM ( SELECT T1.[group] ,
1 P
FROM dbo.table_1 T1
CROSS APPLY ( SELECT *
FROM dbo.table_1 T2
WHERE T1.[group] <> T2.[group]
AND T1.element = T2.element
) L
GROUP BY T1.[group] ,
L.[group]
) T
3-msalim
SELECT ( CASE WHEN COUNT(*) = POWER((SELECT COUNT(DISTINCT [group]) FROM table_1), 2)
THEN 'Yes'
ELSE 'No'
END ) Result
FROM ( SELECT T1.[group]
FROM dbo.table_1 T1
INNER JOIN dbo.table_1 T2
ON T1.element = T2.element
GROUP BY T1.[group] ,
T2.[group]
) T
4-msalim
SELECT CASE COUNT(g2) % POWER(COUNT(DISTINCT g1),2)
WHEN 0 THEN 'Yes'
ELSE 'No'
END
FROM (
SELECT t1.[group] AS g1,
t2.[group] AS g2
FROM dbo.Table_1 AS t1
LEFT OUTER JOIN dbo.Table_1 AS t2
ON t1.element = t2.element
GROUP BY
t1.[group],
t2.[group]
) AS result
5-msalim
select case
when not exists (select g1.[Group]
from dbo.Table_1 g1, dbo.Table_1 g2
where g1.Element = g2.Element
group by g1.[Group]
having count(distinct g2.[Group]) <
(select count(distinct [Group]) from dbo.Table_1))
then 'Yes'
else 'No'
End as result;
پاسخ با نقل قول
نوشته شده توسط msalim
