[Homework] Database Basics - Table Relations - Problem18. Continents and Currencies *

vani4ka66 24 Точки

[Homework] Database Basics - Table Relations - Problem18. Continents and Currencies *

Някой може ли да ми каже защо този код не работи?

SELECT
ContinentCode,
MAX(CurrencyCode) AS CurrencyCode,
MAX(CurrencyUs) AS CurrencyUsage
FROM (SELECT c.ContinentCode,
cr.CurrencyCode,
COUNT(cr.Description) AS CurrencyUs
FROM Currencies cr
JOIN Countries c ON cr.CurrencyCode = c.CurrencyCode
GROUP BY c.ContinentCode, cr.CurrencyCode
HAVING COUNT(cr.Description) > 1) as sel
GROUP BY ContinentCode
ORDER BY ContinentCode

Изкарва ми този резултат, а би трябвало да има още един ред - AS - AUD - 2. Тъй като двете стойности са равни, не би ли трябвало да ми изкара и този ред?

ContinentCode   CurrencyCode      CurrencyUsage
AF      XOF      8
AS      ILS      2
EU      NOK      26
NA      XCD      8
OC      XPF      8

Тагове:

06/10/2016 12:42:34 Module: C# DB

alexei.tcekov 33 Точки

С подобна заявка и аз не успях да я докарам ... и накрая използвах DENSE_RANK ... Тео каза няколко думи за тази функция в упражнението от вторник ...

ето моето решение : "

select asd.ContinentCode, asd.CurrencyCode, asd.Countt from (
select c.ContinentCode, c.CurrencyCode, COUNT(c.CurrencyCode) as Countt, DENSE_RANK() over (partition by c.ContinentCode order by COUNT(c.CurrencyCode) desc) as rankk from Countries c

group by c.ContinentCode, c.CurrencyCode) as asd
where asd.rankk = 1 and asd.Countt <> 1"

06/10/2016 14:16:40

vani4ka66 24 Точки

Да, така стана. Мерси!

SELECT
sel.ContinentCode,
sel.CurrencyCode AS CurrencyCode,
sel.CurrencyUs AS CurrencyUsage
FROM (SELECT c.ContinentCode,
cr.CurrencyCode,
COUNT(cr.Description) AS CurrencyUs,
DENSE_RANK() over (partition by c.ContinentCode order by COUNT(cr.CurrencyCode) desc) as rankk
FROM Currencies cr
JOIN Countries c ON cr.CurrencyCode = c.CurrencyCode
GROUP BY c.ContinentCode, cr.CurrencyCode
HAVING COUNT(cr.Description) > 1) as sel
where sel.rankk = 1
ORDER BY ContinentCode

06/10/2016 15:02:13

RFilipov 136 Точки

SELECT c.ContinentCode, 
	   cc.CurrencyCode, 
	   COUNT(cc.COUNTryCode) AS [CurrencyUsage]
FROM Continents c
JOIN Countries cc ON c.ContinentCode = cc.ContinentCode 
GROUP BY c.ContinentCode , cc.CurrencyCode
HAVING COUNT(cc.CountryCode) = (SELECT MAX(xxx.CurrencyXX) 
                                FROM (SELECT cx.ContinentCode, 
										     ccx.CurrencyCode, 
											 COUNT(ccx.COUNTryCode) AS [CurrencyXX]
    FROM Continents cx
    JOIN Countries ccx ON cx.ContinentCode = ccx.ContinentCode 
    WHERE c.ContinentCode = cx.ContinentCode 
    GROUP BY cx.ContinentCode , ccx.CurrencyCode) AS xxx)
AND COUNT(cc.CountryCode) > 1
ORDER BY c.ContinentCode

06/10/2016 15:39:45 06/10/2016 15:40:23

tormi 7 Точки

Кода на RFilipov, при мен го дава, че е два пъти по-бавен от кода на vani4ka66 /Query cost 62% срещу 38%/

махнах DENSE_RANK() и получих малко по-добър Query cost 25%, срещу 46%, 28%

SELECT GrpConCur.ContinentCode,
       GrpCurrCode.CurrencyCode,
       GrpConCur.CurrencyUsage
FROM
(
    SELECT GrpCnt.ContinentCode,
           MAX(GrpCnt.CurrencyUsage) AS CurrencyUsage
    FROM
    (
        SELECT cn.ContinentCode,
               cn.CurrencyCode,
               COUNT(1) AS CurrencyUsage
        FROM Countries AS cn
        GROUP BY cn.ContinentCode,
                 cn.CurrencyCode
        HAVING COUNT(1) > 1
    ) AS GrpCnt
    GROUP BY GrpCnt.ContinentCode
) AS GrpConCur
INNER JOIN
(
    SELECT cn.CurrencyCode,
           cn.ContinentCode,
           COUNT(1) AS CurrencyUsage
    FROM Countries AS cn
    GROUP BY cn.ContinentCode,
             cn.CurrencyCode
    HAVING COUNT(1) > 1
) AS GrpCurrCode ON GrpConCur.ContinentCode + CONVERT( VARCHAR(10), GrpConCur.CurrencyUsage) = GrpCurrCode.ContinentCode + CONVERT(VARCHAR(10), GrpCurrCode.CurrencyUsage)
ORDER BY GrpConCur.ContinentCode;

, но пък ми пищи заради кънвърта :)

12/10/2016 01:59:29