Problem description: there are two tables, user_id, one is for all the users table t1, t2 said another part of the user table, I'd like to establish a temporary table
The CREATE MULTISET TABLE TABLE
AS (
* *
SEL T1.* *
, t2.The case when
The FROM T1
Left the join t2
On t1. User_id=t3. User_id
WHERE DEAL_DATE between cast (' 20170701 'as the date format' YYYYMMDD ') and cast (' $TX_DATE 'as the date format' YYYYMMDD ')
) WITH DATA PRIMARY INDEX (USER_ID);
I was realized by casewhen judgment in the temporary table when pick up the table data by limiting conditions can choose from users of t1 and t2 user case here when don't know how to write,

CodePudding user response:

Also said in self-study HIVE, why not put the data processing in CASE the WHEN later? Don't know the right of say

CodePudding user response:

Don't put data processing later in CASE the WHEN? Don't know that's right,

Wrong, it is the case when got first and then link once you deal with the results of the data

CodePudding user response:

For data processing, first left join either intersection, the data from either t1 data outside of the intersection, you can distinguish between the two parts of the data, use case judgment t2. User_id for null, to distinguish, specific don't know the results you want

CodePudding user response:

 
The CREATE TABLE t1 (user_id1 int, username varchar (10), DEAL_DATE DATE) 
The CREATE TABLE t2 (user_id2 int, usercomments varchar (10)) 

INSERT INTO t1 values (1, 'Lily', '2018-01-01') 
INSERT INTO t1 values (2, 'Lucy', '2018-01-01') 
INSERT INTO t1 values (3, 'Tom', '2018-01-01') 

INSERT INTO t2 values (2, 't2') 
INSERT INTO t2 values (3, 't2') 


Select a t1. *, t2. * 
,  CASE WHEN t2. User_id2 is null then 'the user is only in t1' ELSE 'the user is in both t1 and t2' end AS userCheck  
The from t1 left join t2 on t1. User_id1=t2. User_id2