刪除結果集的冗餘值

使用 MySQL Workbench，我設法製作了一個創建和插入腳本（查看所有內容）。

用腳本

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

我可以生成下面的結果集：

問題：如何使用 SQL 操作結果集以使重複值不可見，例如具有這樣的結果集：

創建腳本

-- -----------------------------------------------------
-- 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)
);

插入腳本

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);

ROW_NUMBER()如果您在上面使用，實現它的一種方法是在 mysql 中使用函式mysql 8.0。

SELECT CASE
         WHEN row_num = 1 THEN abbr_Company
         ELSE ''
      END companyname1,
      CASE
         WHEN row_num = 1 THEN name_Company
         ELSE ''
      END companyname2,
      ddate,
      turnover,
      name_Employee,
      idCodeTurnover,
      row_num
 FROM (
       SELECT abbr_Company,
              name_Company,
              ddate,
              turnover,
              name_Employee,
              idCodeTurnover,
              ROW_NUMBER() OVER (PARTITION BY abbr_Company, name_Company 
                                     ORDER BY turnover DESC) AS row_num
         FROM Profit
         INNER JOIN Employee ON Employee.idEmployee = Profit.idEmployee
         INNER JOIN Company  ON Profit.idCompany = Company.idCompany
) X
ORDER BY abbr_Company,
        name_Company,
        row_num;

這是dbfiddle中的完整重現

請注意，由於您沒有給出如何定義重複項，我已添加 abbr_Company,name_CompanyasPARTITION BY子句並按最高順序turnover

引用自：https://dba.stackexchange.com/questions/302264