SELECT
*
FROM
(
SELECT * FROM companyInfo AS c WHERE c.aaaid = '156199073619'
) t
LEFT JOIN (
SELECT aaaid,nameen FROM coNameInfos WHERE aaaid = '156199073619' AND type IN ( '100', '101', '110', '120' ) ORDER BY type LIMIT 1
) t2 ON t.aaaid = t2.aaaid
t1表有数据 t2表没有数据
结果: 没有数据 left join表现为inner join结果
CREATE TABLE companyInfo (
aaaid VARCHAR(13) NULL COMMENT '3aid',
orgtype TEXT NULL COMMENT '组织类型 91',
cycode TEXT NULL COMMENT '国家code cn',
cycn TEXT NULL COMMENT '国家（本地）',
cyen TEXT NULL COMMENT '国家（通用）',
namecn TEXT NULL COMMENT '公司名称（本地）中国',
nameen TEXT NULL COMMENT '公司名称（通用）China',
oname TEXT NULL COMMENT '曾用名',
credate_time DATETIME NULL COMMENT '数据创建日期',
update_time DATETIME NULL COMMENT '数据更新日期'
) ENGINE=OLAP
UNIQUE KEY(aaaid)
DISTRIBUTED BY HASH(aaaid) BUCKETS 30
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"min_load_replica_num" = "1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "DEFAULT",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"function_column.sequence_col" = "update_time",
"store_row_column" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);;
CREATE TABLE coNameInfos (
aaaid VARCHAR(13) NULL COMMENT '企业aaa_id',
id BIGINT NOT NULL AUTO_INCREMENT(1) COMMENT 'id',
type TEXT NULL COMMENT '',
namecn TEXT NULL COMMENT '',
nameen TEXT NULL COMMENT '名称（通用）',
aldate DATE NULL COMMENT '变更日期',
credate_time DATETIME NULL COMMENT '数据创建日期',
update_time DATETIME NULL COMMENT '数据更新日期'
) ENGINE=OLAP
UNIQUE KEY(aaaid, id)
COMMENT 'OLAP'
DISTRIBUTED BY HASH(aaaid) BUCKETS 7
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"min_load_replica_num" = "1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"function_column.sequence_col" = "update_time",
"store_row_column" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);;
