SQL题：

1、寻找用户推荐人 

 

select name 
from customer 
where ifnull(referee_id,0) !=2;

null不可以直接与数值类比较，可以用到 ifnull 判断null值，将其转为0

select name 
from customer
where id  not in 
(select id 
from customer where  referee_id =2)

select name 
from Customer 
where referee_id != 2 or referee_id is null

= 或 ！= 只能判断基本数据类型，is 关键字只能判断null
null值无法与确定的值作比较，用 is NULL 或者 is not NULL 判断

2、产品销售分析

编写解决方案，以获取 Sales 表中所有 sale_id 对应的 product_name 以及该产品的所有 year 和 price

select  product_name, year, price from Sales 
left join product 
on Sales.product_id =product.product_id

3、进店却未进行过交易的顾客

​

​ 

有一些顾客可能光顾了购物中心但没有进行交易。请你编写一个解决方案，来查找这些顾客的 ID ，以及他们只光顾不交易的次数。 

select customer_id,count(customer_id) as count_no_trans from 

(select customer_id, transaction_id from Visits v
left join Transactions t
on v.visit_id = t.visit_id) as a

where transaction_id is null group by customer_id

4、上升的温度

SELECT w.id

FROM weather w

INNER JOIN Weather a

ON w.recordDate = DATE_ADD(a.recordDate, INTERVAL 1 day)

WHERE w.Temperature > a.Temperature;

5、每台机器的进程平均运行时间

 

select a1.machine_id, round(avg(a2.TIMESTAMP - a1.TIMESTAMP ), 3) as processing_time
from activity a1, activity a2
where a1.machine_id = a2.machine_id
and a1.process_id = a2.process_id
and a1.activity_type = 'start'
and a2.activity_type = 'end'
GROUP BY a1.machine_id;