MYSQL8.0：如果在通知的日期范圍內沒有活動，如何使結果為空或零？-有解無憂

業務規則：計算通知用戶所在部門的列印量（本例為應用中認證的用戶），即使沒有列印。

注1：查詢應回傳列印量、部門名稱以及上級部門名稱。

注2：如果知情用戶未與任何部門關聯，則應將“無部門”視為部門并計算其列印量。

MySQL 版本 8.0 架構 SQL

CREATE TABLE IF NOT EXISTS `departments` (
  `id` BIGINT(20) UNSIGNED NOT NULL,
  `parent_department` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
  `name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `departments_parent_department_foreign` (`parent_department` ASC),
  CONSTRAINT `departments_parent_department_foreign`
    FOREIGN KEY (`parent_department`)
    REFERENCES `departments` (`id`)
    ON UPDATE CASCADE)
ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `users` (
  `id` BIGINT(20) UNSIGNED NOT NULL,
  `department_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
  `username` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `users_username_unique` (`username` ASC),
  INDEX `users_department_id_foreign` (`department_id` ASC),
  CONSTRAINT `users_department_id_foreign`
    FOREIGN KEY (`department_id`)
    REFERENCES `departments` (`id`)
    ON UPDATE CASCADE)
ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `prints` (
  `id` BIGINT(20) UNSIGNED NOT NULL,
  `department_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
  `user_id` BIGINT(20) UNSIGNED NOT NULL,
  `date` DATE NOT NULL,
  `pages` BIGINT(20) UNSIGNED NOT NULL,
  `copies` BIGINT(20) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `prints_department_id_foreign` (`department_id` ASC),
  INDEX `prints_user_id_foreign` (`user_id` ASC),
  CONSTRAINT `prints_department_id_foreign`
    FOREIGN KEY (`department_id`)
    REFERENCES `departments` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `prints_user_id_foreign`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

啞資料：

insert into departments (id,parent_department,name)
values
(1, null, 'dep 1'),
(2, null, 'dep 2'),
(3, 1, 'dep 3'),
(4, 1, 'dep 4');

insert into users (id,department_id,username)
values
(1, null, 'user 1'),
(2, 1, 'user 2'),
(3, 2, 'user 3'),
(4, 3, 'user 4'),
(5, 4, 'user 5');

insert into prints (id,department_id,user_id,date,pages,copies)
values
(1,null,1,'2020-1-11',1,2),  # dep null, user 1, total 2
(2,null,1,'2020-1-12',3,2),  # dep null, user 1, total 6
(3,1,1,'2020-1-13',4,1),     # dep 1,    user 1, total 4

(4,null,2,'2020-1-13',3,2),  # dep null, user 2, total 6
(5,2,2,'2020-1-15',2,2),     # dep 2,    user 2, total 4
(6,2,2,'2020-1-16',5,1),     # dep 2,    user 2, total 5

(7,4,2,'2021-12-30',1,10);   # dep 5, user 2, total 10

查詢至今

select
    SUM(i.copies * i.pages) AS total_print,
    d.name AS department,
    parent.name AS parent_department
from prints as i
right join departments as d on d.id = i.department_id
inner join users as u on u.department_id = d.id
left join departments as parent on parent.id = d.parent_department
where `u`.`username` = 'user 5'
and (i.date between cast('2020-1-10' as date) and cast('2020-1-17' as date) or i.date is null)
group by u.department_id;

輸入以下用戶時的預期結果：

'用戶 1' 期望：total_print 14，部門為空，父部門為空

不作業：由于該用戶沒有鏈接到任何部門，他應該計算所有“空部門”的列印量。但是，它不回傳任何結果。

'用戶 2' 期望：total_print 4，部門 'dep 1'，parent_department null

按預期作業。

'用戶 3' 期望：total_print 9，部門 'dep 2'，parent_department null

按預期作業。

'用戶 4' 期望：total_print null，部門 'dep 3'，parent_department 'dep 1'

按預期作業。

'用戶 5' 期望：total_print null，部門 'dep 4'，parent_department 'dep 1'

不作業。在這種情況下，查詢停止作業，因為通知部門的列印超出了通知的日期范圍。我不知道為什么會發生這種行為。

小提琴： https ://www.db-fiddle.com/f/jFEN43Ecq4J58AdKpfcWhF/1

謝謝你的幫助。

uj5u.com熱心網友回復：

在這種情況下，查詢停止作業，因為通知部門的列印超出了通知的日期范圍。我不知道為什么會發生這種行為。

由于print是 OUTER 聯接的一部分，因此使用dateWHERE 子句中的列會隱式地將聯接轉換為 INNER JOIN，這就是查詢不回傳任何結果的原因。

在應用日期過濾器之前，查詢回傳以下結果：

副本	頁面	部	父系	用戶名	日期
10	1	第 4 部	第 1 部	用戶 5	2021-12-30

然后資料庫將date過濾器應用于這些結果：

WHERE ... 
 (  i.date BETWEEN cast('2020-1-10' as date) AND cast('2020-1-17' as date)  
    OR i.date is null
 )

由于date顯然不是 NULL，2020-12-30 也不在日期范圍 2020-01-10 和 2020-01-17 之間，因此該行被洗掉，查詢不回傳任何內容。解決方案是將date過濾器移動到 JOIN 中。

也就是說，混合right和left連接根本不直觀，imo。它使查看 sql 的人更難理解預期結果。就個人而言，我建議重寫查詢以僅使用 LEFT JOIN 而不是兩者的混合。

詢問：

SELECT
        SUM(i.copies * i.pages) AS total_print
        , d.name AS department
        , parent.name AS parent_department
FROM    departments d 
            INNER JOIN users as u ON u.department_id = d.id
            LEFT JOIN departments as parent ON parent.id = d.parent_department
            LEFT JOIN prints as i ON d.id = i.department_id
                 AND i.`date` >= '2020-01-10'
                 AND i.`date` <= '2020-01-17'
WHERE u.username = 'user 5'
GROUP BY u.department_id;

結果：

總列印	部	父系
空值	第 4 部	第 1 部

db<>在這里擺弄

uj5u.com熱心網友回復：

對于您的查詢和結構，我們需要有創意。

由于您需要一個部門來鏈接用戶和列印，我們需要在您的案例 0 中創建一個虛擬部門

現在我們可以在查詢中用這個 0 替換 prints 和 users 中所有 NULL 部門，這樣就可以建立鏈接了。

最后我們需要洗掉虛擬部門。

盡管如此，由于您在部門中有遞回結構并且您正在使用 MySQL 8，所以請看一下Recursive CTE

CREATE TABLE IF NOT EXISTS `departments` (
  `id` BIGINT(20) UNSIGNED NOT NULL,
  `parent_department` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
  `name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `departments_parent_department_foreign` (`parent_department` ASC),
  CONSTRAINT `departments_parent_department_foreign`
    FOREIGN KEY (`parent_department`)
    REFERENCES `departments` (`id`)
    ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `users` (
  `id` BIGINT(20) UNSIGNED NOT NULL,
  `department_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
  `username` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `users_username_unique` (`username` ASC),
  INDEX `users_department_id_foreign` (`department_id` ASC),
  CONSTRAINT `users_department_id_foreign`
    FOREIGN KEY (`department_id`)
    REFERENCES `departments` (`id`)
    ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `prints` (
  `id` BIGINT(20) UNSIGNED NOT NULL,
  `department_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
  `user_id` BIGINT(20) UNSIGNED NOT NULL,
  `date` DATE NOT NULL,
  `pages` BIGINT(20) UNSIGNED NOT NULL,
  `copies` BIGINT(20) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `prints_department_id_foreign` (`department_id` ASC),
  INDEX `prints_user_id_foreign` (`user_id` ASC),
  CONSTRAINT `prints_department_id_foreign`
    FOREIGN KEY (`department_id`)
    REFERENCES `departments` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `prints_user_id_foreign`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

insert into departments (id,parent_department,name)
values
(1, null, 'dep 1'),
(2, null, 'dep 2'),
(3, 1, 'dep 3'),
(4, 1, 'dep 4'),
(0,NULL,'dummy');


insert into users (id,department_id,username)
values
(1, null, 'user 1'),
(2, 1, 'user 2'),
(3, 2, 'user 3'),
(4, 3, 'user 4'),
(5, 4, 'user 5');

insert into prints (id,department_id,user_id,date,pages,copies)
values
(1,null,1,'2020-1-11',1,2),  # dep null, user 1, total 2
(2,null,1,'2020-1-12',3,2),  # dep null, user 1, total 6
(3,1,1,'2020-1-13',4,1),     # dep 1,    user 1, total 4

(4,null,2,'2020-1-13',3,2),  # dep null, user 2, total 6
(5,2,2,'2020-1-15',2,2),     # dep 2,    user 2, total 4
(6,2,2,'2020-1-16',5,1),     # dep 2,    user 2, total 5

(7,4,2,'2021-12-30',1,10);   # dep 5, user 2, total 10

select
    SUM(i.copies * i.pages) AS total_print,
    IF(d.name = 'dummy', NULL,d.name) AS department,
    parent.name AS parent_department
from (SELECT id,IFNULL(department_id,0) AS department_id ,user_id,date,pages,copies FROM prints) as i
RIGHT join departments as d on d.id = i.department_id
INNER join (SELECT id,IFNULL(department_id, 0) department_id ,username FROM users) as u on u.department_id = d.id
left join departments as parent on parent.id = d.parent_department
where `u`.`username` = 'user 1'
and (i.date between cast('2020-1-10' as date) and cast('2020-1-17' as date) or i.date is null)
group by u.department_id;

總列印 | 部門 | 父系
----------: | :--------- | :----------------
         14 | 空       | 空值

select
    SUM(i.copies * i.pages) AS total_print,
    IF(d.name = 'dummy', NULL,d.name) AS department,
    parent.name AS parent_department
from (SELECT id,IFNULL(department_id,0) AS department_id ,user_id,date,pages,copies FROM prints) as i
RIGHT join departments as d on d.id = i.department_id
INNER join (SELECT id,IFNULL(department_id, 0) department_id ,username FROM users) as u on u.department_id = d.id
left join departments as parent on parent.id = d.parent_department
where `u`.`username` = 'user 2'
and (i.date between cast('2020-1-10' as date) and cast('2020-1-17' as date) or i.date is null)
group by u.department_id;

總列印 | 部門 | 父系
----------: | :--------- | :----------------
          4 | 第 1 部分 | 空值

select
    SUM(i.copies * i.pages) AS total_print,
    IF(d.name = 'dummy', NULL,d.name) AS department,
    parent.name AS parent_department
from (SELECT id,IFNULL(department_id,0) AS department_id ,user_id,date,pages,copies FROM prints) as i
RIGHT join departments as d on d.id = i.department_id
INNER join (SELECT id,IFNULL(department_id, 0) department_id ,username FROM users) as u on u.department_id = d.id
left join departments as parent on parent.id = d.parent_department
where `u`.`username` = 'user 3'
and (i.date between cast('2020-1-10' as date) and cast('2020-1-17' as date) or i.date is null)
group by u.department_id;

總列印 | 部門 | 父系
----------: | :--------- | :----------------
          9 | 第 2 節 | 空值

db<>在這里擺弄

轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/461716.html

標籤：mysql sql

上一篇：如何對兩列應用sortBy。在laravel雄辯

下一篇：如何通過計算從一個表中獲取資料？