CREATE TABLE cust
(
cust_id INT NOT NULL ,
cust_name VARCHAR(50) NOT NULL,
cust_concat VARCHAR(50) NOT NULL
);
INSERT INTO cust(cust_id,cust_name,cust_concat) VALUES (1001,'kylin','Mary');
INSERT INTO cust(cust_id,cust_name,cust_concat) VALUES (1002,'kris','Ada');
INSERT INTO cust(cust_id,cust_name,cust_concat) VALUES (1003,'Echo','Bob');
INSERT INTO cust(cust_id,cust_name,cust_concat) VALUES (1004,'kylin','Gem');
数据如下,目标:找到和Mary一样的cust_name的订单的所有信息
第一种子查询/第二种自连接
#子查询方法
select * from cust
where cust_name in
(select cust_name from cust where cust_name = 'Mary');
#自连接方法
select c1.* from cust c1,cust c2
where c1.cust_name = c2.cust_name
and c2.cust_concat = 'Mary'
注意:自连接的要点要搞准比的列是哪一列(这里比的是cust_name所以保证两个表的name一样,后来e2的concat保证是那个名字就可以了)
再举个例子
CREATE TABLE orde
(
id INT NOT NULL ,
name VARCHAR(50) NOT NULL,
goods VARCHAR(50) NOT NULL,
price INT NOT NULL
);
INSERT INTO orde(id,name,goods,price) VALUES (1,'小明','A',20);
INSERT INTO orde(id,name,goods,price) VALUES (2,'小红','B',30);
INSERT INTO orde(id,name,goods,price) VALUES (3,'小李','C',40);
INSERT INTO orde(id,name,goods,price) VALUES (4,'小东','D',50);
INSERT INTO orde(id,name,goods,price) VALUES (5,'小惠','E',60);
目标:找到比小李购买的price高的所有订单
比的是:price
附加条件:小李
select o1.*
from orde o1
join orde o2
on o1.price>o2.price
and o2.name = '小东'
问题:hive不支持非等值连接怎么办
select distinct
case when a.price > b.price then a.id else null end
,case when a.price > b.price then a.name else null end
from orde a
join orde b
on b.name = '小李'
相当于给a表只添加小李的信息~好对比~