【求助】MySQL8.0查询语句错误
Posted by:
fei xu
Date: January 16, 2022 07:51AM
MySQL8.0
-- DDL
CREATE TABLE `a3_category` (
`ID` bigint NOT NULL AUTO_INCREMENT,
`NAME` varchar(64) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb3
CREATE TABLE `a3_item` (
`ID` bigint NOT NULL DEFAULT '0',
`IS_ACTIVE` int NOT NULL DEFAULT '1',
`CATEGORY` bigint NOT NULL,
`ICON` mediumtext COMMENT,
`NAME` varchar(64) NOT NULL,
`CODE` varchar(36) DEFAULT NULL,
`MEMBER_ID` bigint DEFAULT NULL,
`IS_DELETED` int NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
-- DML
INSERT INTO test.a3_item (ID, IS_ACTIVE, CATEGORY, ICON, NAME, CODE, MEMBER_ID, IS_DELETED) VALUES (-10, 1, -1, '1', 'mock1', 'mock1', 0, 0);
INSERT INTO test.a3_item (ID, IS_ACTIVE, CATEGORY, ICON, NAME, CODE, MEMBER_ID, IS_DELETED) VALUES (-9, 1, -1, '2', 'mock2', 'mock2', 0, 0);
INSERT INTO test.a3_item (ID, IS_ACTIVE, CATEGORY, ICON, NAME, CODE, MEMBER_ID, IS_DELETED) VALUES (-8, 1, -1, '3', 'mock3', 'mock3', 0, 0);
INSERT INTO test.a3_item (ID, IS_ACTIVE, CATEGORY, ICON, NAME, CODE, MEMBER_ID, IS_DELETED) VALUES (-7, 1, -2, '4', 'mock4', 'mock4', 0, 0);
INSERT INTO test.a3_item (ID, IS_ACTIVE, CATEGORY, ICON, NAME, CODE, MEMBER_ID, IS_DELETED) VALUES (-6, 1, -1, '5', 'mock5', 'mock5', 0, 0);
INSERT INTO test.a3_item (ID, IS_ACTIVE, CATEGORY, ICON, NAME, CODE, MEMBER_ID, IS_DELETED) VALUES (-5, 1, -1, '6', 'mock6', 'mock6', 0, 0);
INSERT INTO test.a3_item (ID, IS_ACTIVE, CATEGORY, ICON, NAME, CODE, MEMBER_ID, IS_DELETED) VALUES (-4, 1, -1, '7', 'mock7', 'mock7', 0, 0);
INSERT INTO test.a3_item (ID, IS_ACTIVE, CATEGORY, ICON, NAME, CODE, MEMBER_ID, IS_DELETED) VALUES (-3, 1, -1, '8', 'mock8', 'mock8', 0, 0);
INSERT INTO test.a3_item (ID, IS_ACTIVE, CATEGORY, ICON, NAME, CODE, MEMBER_ID, IS_DELETED) VALUES (-2, 1, -1, '9', 'mock9', 'mock9', 0, 0);
INSERT INTO test.a3_item (ID, IS_ACTIVE, CATEGORY, ICON, NAME, CODE, MEMBER_ID, IS_DELETED) VALUES (-1, 1, -1, '0', 'mock10', 'mock10', 0, 0);
INSERT INTO test.a3_category (ID, NAME) VALUES (1, 'IT Service');
INSERT INTO test.a3_category (ID, NAME) VALUES (2, 'Asset Service');
-- QUERY SQL
select count(1)
from a3_item item
join (select max(ID) ID
from a3_item
where IS_DELETED = 0
and IS_ACTIVE = 1
group by code
union
select max(ID) ID
from a3_item
where IS_DELETED = 0
group by code
having max(IS_ACTIVE) = 0) latest on latest.ID = item.ID
join a3_category on item.CATEGORY = a3_category.ID
where item.IS_DELETED = 0
and item.CATEGORY = 1
and (item.MEMBER_ID = 1 or item.MEMBER_ID = 0);
-- ERROR INFO
[08S01] Communications link failure The last packet successfully received from the server was 58 milliseconds ago. The last packet sent successfully to the server was 58 milliseconds ago. Communications link fail ...