SQL语句相关笔记(增删改查、类型转换、获取当前时间）

1、-- 类型转换

select * 
from user_customer_label
where CONVERT(id,UNSIGNED) between '3133' and '3694'

2、-- 最近三个月之内下过单     now()当前时间

select DISTINCT uc.id,uc.last_buy_time
from user_customer uc
left join order_info oi on uc.id = oi.customer_id 
left join order_detail od on oi.order_no = od.order_no
where oi.order_no is not null and uc.last_buy_time >= DATE_ADD(now(),INTERVAL -3 MONTH)

3、-- 过去6个月下过单但是最近3个月没有下过单.        now()是当前时间

select DISTINCT uc.id,uc.last_buy_time 
from user_customer uc
left join order_info oi on uc.id = oi.customer_id 
left join order_detail od on oi.order_no = od.order_no
where oi.order_no is not null  and  uc.last_buy_time BETWEEN DATE_ADD(now(),INTERVAL -6 MONTH) AND now() 
and uc.id in (select uc.id
from user_customer uc 
left join order_info oi on uc.id = oi.customer_id 
left join order_detail od on oi.order_no = od.order_no
where uc.last_buy_time NOT BETWEEN DATE_ADD(now(),INTERVAL -3 MONTH) AND now())

4、-- 每个商品有多少客户买过

select pi.id,pi.prd_name, count( DISTINCT uc.id)
from user_customer uc
left join order_info oi on uc.id = oi.customer_id 
left join order_detail od on oi.order_no = od.order_no
left join product_info pi on od.prd_id = pi.id
where oi.order_no is not null 
GROUP BY pi.prd_name 

5、-- 客户买了什么商品  

select DISTINCT uc.id,pi.prd_name
from user_customer uc
left join order_info oi on uc.id = oi.customer_id 
left join order_detail od on oi.order_no = od.order_no
left join product_info pi on od.prd_id = pi.id
where oi.order_no is not null and  pi.prd_name = '百佳红酒'

6、-- 客户买了什么类目的商品 商品类目标签

select DISTINCT uc.id,uc.name,pc.category_name
from user_customer uc
left join order_info oi on uc.id = oi.customer_id 
left join order_detail od on oi.order_no = od.order_no
left join product_info pi on od.prd_id = pi.id
left join product_category pc on pi.prd_category = pc.id
where oi.order_no is not null and pc.category_name = '商品酒'

7、-- 标签既包含品鉴酒又包含商品酒的客户

      --写法一

select customer_id,name,COUNT(customer_id)  
from user_customer_label
where name in( '商品酒','品鉴酒')
GROUP BY customer_id
HAVING COUNT(customer_id) > 1

       --写法二

SELECT * from user_customer_label where customer_id in (
SELECT customer_id from user_customer_label where name  = '商品酒') and name  = '品鉴酒'

8、 -- 同时买过商品酒和品鉴酒的客户

SELECT DISTINCT uc.id,pc.category_name from user_customer uc 
left join order_info oi on uc.id = oi.customer_id 
left join order_detail od on oi.order_no = od.order_no
left join product_info pi on od.prd_id = pi.id
left join product_category pc on pi.prd_category = pc.id
where uc.id in (
SELECT DISTINCT uc.id from user_customer uc  
left join order_info oi on uc.id = oi.customer_id 
left join order_detail od on oi.order_no = od.order_no
left join product_info pi on od.prd_id = pi.id
left join product_category pc on pi.prd_category = pc.id
where pi.prd_category = '3')
and pi.prd_category = '4'

9、-- 插入语句

insert into user_customer_label(id,customer_id,name,type,tenant_id,deleted,create_by_id,create_time)
values('1536790110170783745','1534718640775098369','商品酒','sale','1480758206460583938','F','1527669109789032450',NOW());

10、-- 更新语句

UPDATE order_info 
SET send_time = '2022-09-28 10:41:08',create_time = '2022-09-27 15:49:54',modify_time = '2022-09-27 15:49:54'
where create_time = '2022-09-01 15:49:54'

11、-- 删除语句

DELETE from user_customer_label where name = '近3个月之内下过单'