select tabel1.key from table1 left semi join tabel2 on table1.key=table2.key

select tabel_A.key from table_A join tabel_B on table_A.key=tabel_B.key

select tabel_A.key from table_A right semi join tabel_B on table_A.key=tabel_B.key

select tabel_A.key from table_A left outer join tabel_B on table_A.key=tabel_B.key

select distinct(userid) from orders o1 where o1.pid = 'p01' and exists (select 1 from orders o2 where o1.userid= o2.userid and o2.pid='p02')

select distinct (o1.userid) from orders o1 left join orders o2     on o1.userid= o2.userid     and o2.pid='p02' where o1.pid='p01'

select distinct (userid) from orders ol where ol.pid in ('p01','p02')

select distinct (userid) from orders ol where ol.pid = 'p01' and ol.pid = 'p02'

[  {    "type":"Promo fund",    "currency":"AUD",    "amount":15,    "actualAmount":15,    "rebatePolicyIDs":[      214,      215    ]  },  {    "type":"AirlineRebate",    "currency":"AUD",    "amount":7,    "actualAmount":7,    "rebatePolicyIDs":[      450283,      450284    ]  }]

select get_json_object(default.json_array(info)[0],'$.type') as type,get_json_object(default.json_array(info)[0],'$.rebatePolicyIDs') as promoid,get_json_object(default.json_array(info)[0],'$.currency') as currency,get_json_object(default.json_array(info)[0],'$.amount') as amount

select get_json_object(default.json_array(info)[0],'$.type') as type,default.json_array(get_json_object(default.json_array(info)[0],'$.rebatePolicyIDs'))[0] as promoid,get_json_object(default.json_array(info)[0],'$.currency') as currency,get_json_object(default.json_array(info)[0],'$.amount') as amount

select get_json_object(info,'$.type') as type,default.json_array(get_json_object(info,'$.rebatePolicyIDs'))[0] as promoid,get_json_object(info,'$.currency') as currency,get_json_object(info,'$.amount') as amount

select get_json_object(info,'$.type') as type,get_json_object(info,'$.rebatePolicyIDs') as promoid,get_json_object(info,'$.currency') as currency,get_json_object(info,'$.amount') as amount

SELECT SUM(a),COUNT(DISTINCT a),COUNT(1),AVG(a) FROM test

记录的表为ctrip_hotel_order，其中：

• order_id：订单id

• hotel_id：被访问的酒店id

• hotel_id：酒店ID

• hotel_name：酒店名称

• hotel_id：酒店id

• hotel_name：酒店名称

• order_num：订单量

注意：当订单量重复时，请按照 hotel_id 升序排列，最终输出至多两条数据

select     hotel_id    ,hotel_name    ,order_numfrom (    select         b.hotel_id        ,b.hotel_name        ,count(distinct a.orderid) as order_num    from ctrip_hotel_order a    join ctrip_hotel_info b         on a.hotel_id = b.hotel_id    group by         b.hotel_id        ,b.hotel_name)t order by order_num desc,hotel_id asclimit 2;

携程酒店有两张数据表。

用户表（user1)存放所有用户信息，包含

• uid：用户id

• name：姓名

• orderid：订单id

• uid：用户id

• cost：用户该单付款金额

• hotelid：入住酒店ID

• uid: 用户id

• name: 姓名

• avg_cost: 单均价。

• 单均价用数学平均，结果四舍五入保留一位小数即可

• 如果用户在历史上没有下过任何订单，则记为0

• 最后结果按照用户单均价由大到小降序排列

• 如果单均价相同，则按照用户id升序排列

select     a.uid    ,a.name    ,round(sum(a.cost)/count(distinct a.orderid),1) as avg_costfrom(    select         a.uid        ,a.name        ,ifnull(b.orderid,1) as orderid        ,ifnull(b.cost,0) as cost    from user1 as a    left join order1 as b         on a.uid = b.uid)agroup by     a.uid    ,a.nameorder by round(sum(a.cost)/count(distinct a.orderid),1) desc,a.uid asc;

select bu_name,salary_avgfrom (    select bu_name,round(avg(salary),2) as salary_avg    from department a     inner join employee b on a.bu_id = b.bu_id        where b.level >= 5         group by bu_name         having round(avg(salary),2)>= 9000) torder by salary_avg desc;