CREATE DATABASE testdbs DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
CREATE TABLE testdbs.h_info (
tid bigint(20) NOT NULL AUTO_INCREMENT,
code varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL ,
btestcode varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL ,
PRIMARY KEY (tid) /*T![clustered_index] CLUSTERED */,
UNIQUE KEY code_UNIQUE (code)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ;
CREATE TABLE testdbs.b1_info (
tid bigint(20) NOT NULL AUTO_INCREMENT,
code varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL ,
PRIMARY KEY (tid) /*T![clustered_index] CLUSTERED */,
UNIQUE KEY code_UNIQUE (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ;
CREATE TABLE testdbs.b2_info (
tid bigint(20) NOT NULL AUTO_INCREMENT,
code varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL ,
period varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL ,
pay decimal(11,2) NOT NULL DEFAULT ‘0.00’ ,
code1 varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL ,
type1 char(2) COLLATE utf8mb4_unicode_ci NOT NULL ,
code2 varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL ,
PRIMARY KEY (tid) /*T![clustered_index] CLUSTERED */,
UNIQUE KEY code_UNIQUE (code),
KEY ix_period (period,code1),
KEY ix_code12 (code1,code2),
KEY ix_code1 (code1),
KEY ix_code2 (code2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’’;
CREATE TABLE testdbs.m_info (
tid bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘主键’,
code varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL ,
code3 varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL ,
type3 char(2) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘00’,
code4 varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL ,
PRIMARY KEY (tid) /*T![clustered_index] CLUSTERED */,
UNIQUE KEY code_UNIQUE (code),
KEY code4 (code4,type3),
KEY idx_type3 (type3,code,code4),
KEY code3 (code3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
执行以下语句报错
select d.tid,smi.tid ,b.tid ,
SUM(CASE WHEN d.period < ‘2020-01’ THEN d.pay ELSE 0 END) AS beforeYearAmt
FROM testdbs.m_info smi
INNER JOIN testdbs.b2_info d
ON smi.code = d.code1 AND d.type1 = ‘03’ AND smi.code3 = ‘S21011’
LEFT JOIN testdbs.h_info a ON smi.code4 = a.code AND smi.type3 = ‘01’
LEFT JOIN testdbs.b1_info b ON a.btestcode = b.code
WHERE d.code2 = ‘S21011’
AND d.period <= ‘2021-12’
LIMIT 10