MySQL Forums
Forum List  »  Chinese

【求助】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 ...

Options: ReplyQuote


Subject
Views
Written By
Posted
【求助】MySQL8.0查询语句错误
409
January 16, 2022 07:51AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.