· 8 years ago · Jan 03, 2018, 07:32 AM
1-- Adminer 4.3.1 MySQL dump
2
3SET NAMES utf8;
4SET time_zone = '+00:00';
5SET foreign_key_checks = 0;
6SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
7
8DROP TABLE IF EXISTS `item`;
9CREATE TABLE `item` (
10 `id` int(11) NOT NULL AUTO_INCREMENT,
11 `nome` text NOT NULL,
12 `disponibili` int(11) NOT NULL,
13 PRIMARY KEY (`id`)
14) ENGINE=InnoDB DEFAULT CHARSET=utf8;
15
16INSERT INTO `item` (`id`, `nome`, `disponibili`) VALUES
17(1, 'Bandana Rossa', 4),
18(2, 'Gatto spelacchiato', 0),
19(3, 'Rossetto esaurito', 0),
20(4, 'Cane bagnato', 0),
21(5, 'Teschio parlante una lingua sconosciuta', 0),
22(6, 'Sito web lento', 0);
23
24DROP TABLE IF EXISTS `ordini`;
25CREATE TABLE `ordini` (
26 `id` int(11) NOT NULL AUTO_INCREMENT,
27 `data` int(11) NOT NULL,
28 `blablabla` text NOT NULL,
29 PRIMARY KEY (`id`)
30) ENGINE=InnoDB DEFAULT CHARSET=utf8;
31
32INSERT INTO `ordini` (`id`, `data`, `blablabla`) VALUES
33(1, 12, 'Bo1'),
34(2, 466, 'Bo23');
35
36DROP TABLE IF EXISTS `ordiniHASitem`;
37CREATE TABLE `ordiniHASitem` (
38 `id_ordine` int(11) NOT NULL,
39 `id_item` int(11) NOT NULL,
40 `quanti` int(11) NOT NULL
41) ENGINE=InnoDB DEFAULT CHARSET=utf8;
42
43INSERT INTO `ordiniHASitem` (`id_ordine`, `id_item`, `quanti`) VALUES
44(1, 1, 0),
45(1, 2, 0),
46(1, 3, 0),
47(2, 1, 0),
48(2, 4, 0);
49
50
51CREATE
52 ALGORITHM = UNDEFINED
53 DEFINER = `dario_press`@`127.0.0.1`
54 SQL SECURITY DEFINER
55VIEW `test`.`venduti` AS
56 SELECT
57 COUNT(0) AS `venduti`, `i`.`nome` AS `nome`
58 FROM
59 ((`test`.`ordini` `o`
60 JOIN `test`.`ordiniHASitem` `oi` ON ((`oi`.`id_ordine` = `o`.`id`)))
61 JOIN `test`.`item` `i` ON ((`i`.`id` = `oi`.`id_item`)))
62 GROUP BY `i`.`id`
63
64
65-- 2017-12-28 10:42:05
66
67
68select *
69
70from ordini as o
71JOIN ordiniHASitem as oi ON oi.id_ordine = o.id
72JOIN item as i ON i.id = oi.id_item
73
74----------
75
76id,data,blablabla,id_ordine,id_item,quanti,id,nome,disponibili
771,12,Bo1,1,1,0,1,Bandana Rossa,4
781,12,Bo1,1,2,0,2,Gatto spelacchiato,0
791,12,Bo1,1,3,0,3,Rossetto esaurito,0
802,466,Bo23,2,1,0,1,Bandana Rossa,4
812,466,Bo23,2,4,0,4,Cane bagnato,0
82
83
84-------------
85Venduti
86
87select count(*) as venduti, i.nome
88
89from ordini as o
90JOIN ordiniHASitem as oi ON oi.id_ordine = o.id
91JOIN item as i ON i.id = oi.id_item
92
93group by i.id
94
95venduti,nome
962,Bandana Rossa
971,Gatto spelacchiato
981,Rossetto esaurito
991,Cane bagnato