CREATE TABLE test1 (
id int(11) NOT NULL,
user_id int(11) NOT NULL,
brand int(11) NOT NULL ,
regulate int(11) NOT NULL,
) ENGINE=OLAP
UNIQUE KEY(id, user_id, brand, regulate)
COMMENT 'OLAP'
PARTITION BY LIST(regulate)
(PARTITION P_VJPSVG_UMFSC_MOSL VALUES IN ("77714","77711","77710"),
PARTITION P_STSVG_STASIC VALUES IN ("77715","77725"))
DISTRIBUTED BY HASH(user_id, brand, regulate) BUCKETS 3
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"is_being_synced" = "false",
"storage_format" = "V2",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"store_row_column" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false"
);
CREATE TABLE test2 (
id int(11) NOT NULL,
brand int(11) NOT NULL ,
regulate int(11) NOT NULL,
user_id int(11) NOT NULL,
) ENGINE=OLAP
UNIQUE KEY(id, brand, regulate, user_id)
COMMENT 'OLAP'
PARTITION BY LIST(regulate)
(PARTITION P_VJPSVG_UMFSC_MOSL VALUES IN ("77714","77711","77710"),
PARTITION P_STSVG_STASIC VALUES IN ("77715","77725"))
DISTRIBUTED BY HASH(user_id, brand, regulate) BUCKETS 3
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"is_being_synced" = "false",
"storage_format" = "V2",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"store_row_column" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false"
);
INSERT INTO test1(id,user_id,brand,regulate) values(66297,910369,5,77711);
INSERT INTO test2(id,brand,regulate,user_id) values(79220,5,77711,910369);
SELECT
a.user_id,
a.brand,
a.regulate,
c.user_id AS c_user_id
FROM
test1 a
inner JOIN test2 c ON ( a.user_id = c.user_id AND a.brand = c.brand AND a.regulate = c.regulate)
WHERE
a.id = 66297
AND a.brand = 5
AND a.regulate = 77711;
SELECT * from test1 a where a.id = 66297 AND a.brand = 5 AND a.regulate = 77711;
SELECT * from test2 c where c.user_id =910369 and c.brand=5 and c.regulate = 77711;
------关联查询：查不出数据（去点a.id条件或者去掉一个关联条件都能查出数据）：----------
------------------单独查询test1：-------------------------------
------------------单独查询test2：------------------------------------
