nMQJvV99

1# Продаваемые авторы и книги
2# ------------------------------------------------------------
3    SELECT
4        DISTINCT(a.name),
5        b.sold,
6				b.value,
7        b.books  
8    FROM
9        `Authors` a  
10    JOIN 
11    		Books_Authors ba 
12       		ON ba.idAuthor = a.idAuthor 
13    JOIN
14        (
15            SELECT
16                ba.`idAuthor` AS author,
17                sum(b.BookSold) AS sold,
18                sum(Cost * BookSold) AS VALUE,
19                GROUP_CONCAT(DISTINCT b.BookName ORDER BY b.BookName ASC SEPARATOR ', ') AS books   
20            FROM
21                Books AS b   
22            JOIN
23                Books_Authors ba 
24                    ON ba.idBook = b.idBook   
25            WHERE
26                b.BookSold IS NOT NULL   
27            GROUP BY
28                author
29            ORDER BY
30                sold DESC 
31        ) b    
32            ON ba.`idAuthor` = b.`Author`   
33    ORDER BY sold DESC
34    LIMIT 5
35
36
37# Дамп таблиц
38# ------------------------------------------------------------
39
40DROP TABLE IF EXISTS `Authors`;
41
42CREATE TABLE `Authors` (
43  `idAuthor` int(10) NOT NULL AUTO_INCREMENT,
44  `name` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
45  `fam` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
46  `birthday` date DEFAULT NULL,
47  `AuthorName` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
48  PRIMARY KEY (`idAuthor`)
49) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
50
51LOCK TABLES `Authors` WRITE;
52/*!40000 ALTER TABLE `Authors` DISABLE KEYS */;
53
54INSERT INTO `Authors` (`idAuthor`, `name`, `fam`, `birthday`, `AuthorName`)
55VALUES
56	(1,'Николай Николаевич','Носов','2008-11-23','Носов Николай Николаевич'),
57	(2,'Артур Конан','Дойль','1859-05-22','Дойль Артур Конан'),
58	(3,'Роджер Желязны','Желязны','1993-10-10','Роджер Желязны');
59
60/*!40000 ALTER TABLE `Authors` ENABLE KEYS */;
61UNLOCK TABLES;
62
63
64# Дамп таблицы Books
65# ------------------------------------------------------------
66
67DROP TABLE IF EXISTS `Books`;
68
69CREATE TABLE `Books` (
70  `idBook` int(10) NOT NULL AUTO_INCREMENT,
71  `id_author` int(10) DEFAULT NULL,
72  `BookName` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
73  `BookSold` int(10) DEFAULT NULL,
74  `Cost` int(3) DEFAULT NULL,
75  PRIMARY KEY (`idBook`),
76  KEY `id_author` (`id_author`)
77) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
78
79LOCK TABLES `Books` WRITE;
80/*!40000 ALTER TABLE `Books` DISABLE KEYS */;
81
82INSERT INTO `Books` (`idBook`, `id_author`, `BookName`, `BookSold`, `Cost`)
83VALUES
84	(1,1,'Незнайка учится',3,1),
85	(2,1,'Незнайка-путешественник',1,1),
86	(3,1,'Винтик, Шпунтик и пылесос',4,2),
87	(4,2,'Затерянный мир',12,6),
88	(5,2,'Шерлок Холмс',3,6),
89	(7,3,'Хроники Амбера',9,10);
90
91/*!40000 ALTER TABLE `Books` ENABLE KEYS */;
92UNLOCK TABLES;
93
94
95# Дамп таблицы Books_Authors
96# ------------------------------------------------------------
97
98DROP TABLE IF EXISTS `Books_Authors`;
99
100CREATE TABLE `Books_Authors` (
101  `idBookAuthor` int(10) NOT NULL AUTO_INCREMENT,
102  `idBook` int(11) NOT NULL,
103  `idAuthor` int(11) NOT NULL,
104  PRIMARY KEY (`idBookAuthor`)
105) ENGINE=InnoDB DEFAULT CHARSET=utf8;
106
107LOCK TABLES `Books_Authors` WRITE;
108/*!40000 ALTER TABLE `Books_Authors` DISABLE KEYS */;
109
110INSERT INTO `Books_Authors` (`idBookAuthor`, `idBook`, `idAuthor`)
111VALUES
112	(1,1,1),
113	(2,2,1),
114	(3,3,1),
115	(4,4,2),
116	(5,5,2),
117	(6,6,2),
118	(7,7,3);
119
120/*!40000 ALTER TABLE `Books_Authors` ENABLE KEYS */;
121UNLOCK TABLES;
122
123
124
125/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
126/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
127/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
128/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
129/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
130/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;