CREATE TABLE t_data_customer
(
id
decimal(20,0) NOT NULL,
tenant_key
varchar(10) COLLATE utf8_general_ci NOT NULL,
obj_id
decimal(20,0) NOT NULL,
name
varchar(500) COLLATE utf8_general_ci DEFAULT NULL,
creator
decimal(20,0) DEFAULT NULL,
create_time
datetime DEFAULT NULL,
update_time
datetime DEFAULT NULL,
comment_time
datetime DEFAULT NULL,
data_status
tinyint(4) DEFAULT NULL,
field_1
varchar(200) COLLATE utf8_general_ci DEFAULT NULL,
PRIMARY KEY (id
) /*T![clustered_index] NONCLUSTERED */,
KEY index_id
(id
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
CREATE TABLE t_data_contact
(
id
decimal(20,0) NOT NULL,
tenant_key
varchar(10) COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (id
) /*T![clustered_index] NONCLUSTERED */,
KEY index_id
(id
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
INSERT INTO t_data_customer(id, tenant_key, obj_id, name, creator, field_1, create_time, update_time) VALUES (3571879421192346095, ‘TG2UL3IHCF’, 1, ‘科技股份有限公司(示例)’, 5111879422635328884, ‘5111879422635328884’, ‘2021-04-19 09:03:31’, ‘2021-04-19 09:03:31’);
INSERT INTO t_data_contact(id, tenant_key) VALUES (3571879421283146133, ‘TG2UL3IHCF’);
查询语句:
SELECT
M2.field_1 AS field_1,
M2.field_2 AS field_2,
M2.field_3 AS field_3,
M2.field_4 AS field_4,
M2.field_5 AS field_5,
M2.field_6 AS field_6,
M4.field_1 AS field_19
FROM
(
SELECT
M1.id AS field_1,
M1.NAME AS field_2,
M1.creator AS field_3,
M1.field_1 AS field_4,
M1.create_time AS field_5,
M1.update_time AS field_6
FROM
t_data_customer M1
WHERE
( M1.TENANT_KEY = ‘TG2UL3IHCF’ )
LIMIT 100
) M2
LEFT JOIN (
SELECT
M3.id AS field_1
FROM
t_data_contact M3
WHERE
( M3.TENANT_KEY = ‘TG2UL3IHCF’ )
LIMIT 100
) M4 ON M2.field_1 = M4.field_1 limit 100
注意一下单引号,貌似论坛会转换成中文的单引号