MySQL，在查询中苦苦挣扎

Question

这是我当前的查询

SELECT sub.Name, sub.playlistid
FROM (SELECT TrackId, playlistId, track.name AS Name
  FROM playlisttrack NATURAL JOIN track
ORDER BY TrackId) AS sub

这将返回以下内容。

Name   PlaylistID
For Those About To Rock (We Salute You) 8
For Those About To Rock (We Salute You) 17
For Those About To Rock (We Salute You) 1
Balls to the Wall   1
Balls to the Wall   8
Balls to the Wall   17
Fast As a Shark 8
Fast As a Shark 1
Fast As a Shark 17
Fast As a Shark 5

我不知道如何进行计数，所以它会做类似的事情

Name    NumberofPlaylists
For Those About To Rock (We Salute You) 3
Balls to the Wall   3
Fast as a Shark 4

每当我数数时，它就会给我

For Those About To Rock (We Salute You) 8715 (total amount of tuples)\

类似的问题

我能走到这一步。

SELECT sub.BillingCountry, sub.aid, sub.title
  FROM(SELECT BillingCountry, Title, album.AlbumId AS aid#Count oftime purchased in that country
    From album natural join track Natural join invoiceline NATURAL JOIN invoice
    ORDER BY AlbumId) as sub
Order BY sub.aid, sub.billingcountry

这就是我得到的

Brazil  1   For Those About To Rock We Salute You
Canada  1   For Those About To Rock We Salute You
Canada  1   For Those About To Rock We Salute You
Italy   1   For Those About To Rock We Salute You
Italy   1   For Those About To Rock We Salute You
Italy   1   For Those About To Rock We Salute You
Norway  1   For Those About To Rock We Salute You
Norway  1   For Those About To Rock We Salute You
Norway  1   For Those About To Rock We Salute You
Norway  1   For Those About To Rock We Salute You
Canada  2   Balls to the Wall
Germany 2   Balls to the Wall

但是，现在我想计算某个国家/地区购买某物的次数，所以我想要返回的是

Norway 4 For those about to rock we salute you
...

这会类似吗？

select distinct a.country,b.playlist,a.cnt from
(select country,count(1) as cnt  from yourtable group by country) as a

left join

(select * from  (SELECT sub.BillingCountry, sub.aid, sub.title
                    FROM(SELECT BillingCountry, Title, album.AlbumId AS aid
                        From album natural join track Natural join invoiceline NATURAL JOIN invoice
                        Order BY sub.aid, sub.billingcountry)) as b 
on a.country = b.country

Answer 1

SELECT track.name 
    , COUNT(*)
FROM playlisttrack 
    NATURAL JOIN track
GROUP BY track.name