Início / user-241759

user2494430's questions

Martin Hope
user2494430
Asked: 2021-11-08 05:48:40 +0800 CST
  • 0

Usando o MySQL Workbench eu consegui fazer um script de criação e inserção (veja todos eles abaixo).

Com o roteiro

SELECT abbr_Company, name_Company, ddate, turnover, name_Employee
FROM Profit
INNER JOIN Employee ON Employee.idEmployee = Profit.idEmployee
INNER JOIN Company ON Profit.idCompany = Company.idCompany
ORDER BY idCodeTurnover

Eu posso gerar o conjunto de resultados abaixo: conjunto de resultados

Pergunta : Como posso manipular com SQL o conjunto de resultados para ter valores duplicados não visíveis, por exemplo, tendo um conjunto de resultados como este: insira a descrição da imagem aqui

Criar roteiro

-- -----------------------------------------------------
-- Schema playautomatedb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS playautomatedb DEFAULT CHARACTER SET utf8 ;
USE playautomatedb ;

-- -----------------------------------------------------
-- Table playautomatedb.Company
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS playautomatedb.Company (
  idCompany SMALLINT NOT NULL,
  name_company VARCHAR(45) NOT NULL,
  abbr_company VARCHAR(3) NOT NULL,
  PRIMARY KEY (idCompany),
  UNIQUE INDEX idCompany_UNIQUE (idCompany ASC) 
  );


-- -----------------------------------------------------
-- Table playautomatedb.Employee
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS playautomatedb.Employee (
  idEmployee TINYINT NOT NULL,
  name_Employee VARCHAR(45) NOT NULL,
  abbr_Employee VARCHAR(3) NOT NULL,
  PRIMARY KEY (idEmployee),
  UNIQUE INDEX idEmployee_UNIQUE (idEmployee ASC) 
);


-- -----------------------------------------------------
-- Table playautomatedb.Automaat
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS playautomatedb.Automate (
  idAutomate SMALLINT NOT NULL,
  type_Automate VARCHAR(45) NOT NULL,
  idCompany SMALLINT NULL,
  PRIMARY KEY (idAutomate),
  UNIQUE INDEX idAutomate_UNIQUE (idAutomate),
    FOREIGN KEY (idCompany)
    REFERENCES playautomatedb.Company (idCompany)
    );


-- -----------------------------------------------------
-- Table playautomatedb.Profit
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS playautomatedb.Profit (
  idCodeTurnover INT NOT NULL,
  ddate DATE NOT NULL,
  idEmployee TINYINT NOT NULL,
  turnover INT NOT NULL,
  idCompany SMALLINT NULL,
  PRIMARY KEY (idCodeTurnover),
    FOREIGN KEY (idEmployee)
    REFERENCES playautomatedb.Employee (idEmployee),
  CONSTRAINT idCompany_UNIQUE
    FOREIGN KEY (idCompany)
    REFERENCES playautomatedb.Company (idCompany)
);

INSERIR Script

use playautomatedb;

-- insert Company
INSERT INTO Company (idCompany, name_Company, abbr_Company) 
VALUES 
(1, 'Coffeeshop de Engel', 'CE'),
(2, 'Snackbar Vork', 'SV'),
(3, 'Kantine BV WSE', 'WSE'),
(4, 'Zwembad de Snip', 'ZS');


-- insert Employee 
INSERT INTO Employee (idEmployee, name_Employee, abbr_Employee)
VALUES
(1, 'Jan Hein', 'JHE'),
(2, 'Klaas Jansen', 'KJA'),
(3, 'Johan Bakker', 'JBA');


-- insert Automate
INSERT INTO Automate (idAutomate, type_Automate, idCompany)
VALUES
(21, 'F1_Racer', 1),
(23, 'Mega Crusher', 1),
(34, 'Photoplay', 1),

(22, 'F1 Racer', 2),
(37, 'Mega Crusher', 2),

(35, 'Photoplay', 3),

(36, 'Mega Crusher', 4),
(18, 'F1 Racer', 4);


-- insert profit
INSERT INTO Profit (idCodeTurnover, ddate, idEmployee, turnover, idCompany)
VALUES
(1, '2011-01-11', 1, 1542, 1),
(2, '2011-02-11', 2, 1653, 1),
(3, '2011-03-11', 1, 1482, 1),
(4, '2011-04-11', 3, 2018, 1),
(5, '2011-05-11', 2, 1503, 1),

(6, '2011-05-12', 2, 2035, 2),
(7, '2011-03-01', 2, 837, 3),
(8, '2011-06-02', 2, 912, 3),
(9, '2011-09-01', 1, 799, 3),

(10, '2011-04-11', 3, 1387, 4);
mysql duplication