Обединяване на таблици и завъртане на данни с MySQL

Цел 1: Вашите данни за продажби се съхраняват в таблицата Purchases. Вашият търговски персонал иска да вижда данните за продажбите в обобщена форма, разбити по тримесечия.

Ако вашата таблица Покупки няма данни за продажби, създайте такива. Уверете се, че данните обхващат четири тримесечия. След това напишете заявка за завъртане на данните, както следва:

Album              Q1   Q2   Q3   Q4

OK Computer        2    5    3    7

 Sea Change        8    6    2    1

Не създавайте отделна таблица или изглед. Не променяйте никакви таблици.

Запазете заявката си като dba1lesson10project1.sql и предайте проекта.

Това е, което трябва да направя. Но таблицата, с която иска да работя, изглежда така. И в заданието пише, че изобщо не мога да го променя.

CustomerID    DateOfPurchase   SongID

1              2007-03-31        3
3              2007-06-30        4
4              2007-09-30        4
5              2007-12-31        5

Знам, че трябва да свържа три маси, за да мога да ги групирам по заглавие. Кои са моите таблици с песни, албуми и покупки.

 SELECT Albums.Title FROM Albums
 LEFT JOIN Songs
 INNER JOIN Purchases
 ON Songs.SongID = Purchases.SongID
 ON Albums.Title = Purchases.SongID,
 SELECT Title,
 SUM(CASE WHEN QUARTER(DateOfPurchase) = 1 THEN 1 ELSE 0 END) AS 'Q1',
 SUM(CASE WHEN QUARTER(DateOfPurchase) = 2 THEN 1 ELSE 0 END) AS 'Q2',
 SUM(CASE WHEN QUARTER(DateOfPurchase) = 3 THEN 1 ELSE 0 END) AS 'Q3',
 SUM(CASE WHEN QUARTER(DateOfPurchase) = 4 THEN 1 ELSE 0 END) AS 'Q4'
 From Purchases
 GROUP BY Title;

Донякъде съм на загуба, когато става въпрос за обединяване на три отделни таблици и след това завъртане на данните

Опитах кода по-горе в множество други варианти, които не ме провалиха след частта за присъединяване към таблица.

Всяка помощ ще бъде много ценена.


mysql join pivot
person Dylan McCann    schedule 18.09.2013    source източник
comment
Моля, покажете схеми на таблици и примерни данни за всичките три таблици.   -  person peterm    schedule 18.09.2013

Отговори (1)


arrow_upward
1
arrow_downward

Моето предложение, преди да се опитате да PIVOT данните, би било първо да напишете заявката, за да върнете колоните, от които се нуждаете, това ще включва обединяване на вашите таблици. Не предоставихте дефинициите на вашите таблици, така че правя няколко предположения за структурата. Ако вашите таблици са структурирани по следния начин:

CREATE TABLE Purchases
    (`CustomerID` int, `DateOfPurchase` datetime, `SongID` int)
;

CREATE TABLE Albums
    (`AlbumId` int, `Title` varchar(11))
;

CREATE TABLE Songs
    (`SongID` int, `AlbumID` int)
;

След това бихте ИЗБИРАЛИ от таблиците, като използвате JOIN, подобен на този код:

select a.title,
  p.DateOfPurchase
from albums a
inner join songs s
  on a.albumid = s.albumId
inner join purchases p
  on s.songid = p.songid

Тази заявка ще ви върне албума Title, както и DateOfPurchase, който ви е необходим, за да завъртите данните. След като разработите JOINS, можете да замените p.DateOfPurchase с вашата агрегатна функция и CASE израз и да добавите клаузата GROUP BY, за да получите крайния резултат:

select a.title,
  SUM(CASE WHEN Quarter(p.DateOfPurchase) = 1 THEN 1 ElSE 0 END) AS Q1,
  SUM(CASE WHEN Quarter(p.DateOfPurchase) = 2 THEN 1 ELSE 0 END) AS Q2,
  SUM(CASE WHEN Quarter(p.DateOfPurchase) = 3 THEN 1 ELSE 0 END) AS Q3,
  SUM(CASE WHEN Quarter(p.DateOfPurchase) = 4 THEN 1 ELSE 0 END) AS Q4
from albums a
inner join songs s
  on a.albumid = s.albumId
inner join purchases p
  on s.songid = p.songid
group by a.title;

Вижте SQL Fiddle с демонстрация. Това дава резултат:

|       TITLE | Q1 | Q2 | Q3 | Q4 |
| OK Computer |  1 |  0 |  0 |  0 |
|  Sea Change |  0 |  1 |  1 |  0 |
person Taryn    schedule 18.09.2013
comment
Благодаря ти. Оценявам го, много. - person Dylan McCann; 19.09.2013