日常sql

-- 查询包含两条以上订单的客户id
select  c.customer_id,count(o.order_id) as tcount  from crm_customer c inner JOIN crm_customer_external ce on c.customer_id = ce.customer_id
inner join crm_order_info o on ce.external_userid=o.source_customer_id and ce.channel_type = o.channel_type 
GROUP BY customer_id  HAVING tcount > 1  LIMIT 100

说明：查询出现两次以上的记录 通过 group by … having …

-- 一对多，字符串拼接
select re.customer_rel_id, GROUP_CONCAT(l.name) as stuName from 
crm_customer_label_rel  re 
left join crm_label l on l.id = re.label_rel_id  group by re.customer_rel_id

使用mysql的GROUP_CONCAT可对字符进行拼接

select c.customer_id,c.nick_name , f.user_id,u.clerk_name , f.create_time ,s.stuName
FROM crm_customer c inner join crm_customer_flower_rel f on c.customer_id = f.customer_id
left join store_shopping_guide u on f.user_id = u.clerk_no
left join ( select re.customer_rel_id, GROUP_CONCAT(l.name) as stuName from 
crm_customer_label_rel  re  left join crm_label l on l.id = re.label_rel_id  group by re.customer_rel_id ) s
on s.customer_rel_id = c.customer_id where f.create_time >='2021-03-26 00:00:00'
and u.channel_code ='1101'

select cc.* ,
dd.channel_name from (select cc.customer_id, GROUP_CONCAT(DISTINCT ec.channel_name) as channel_name  from (
select DISTINCT c.customer_id from crm_customer c INNER JOIN
crm_customer_external ec on c.customer_id = ec.customer_id 
INNER JOIN crm_order_info o on (o.source_customer_id = ec.external_userid and o.channel_type = ec.channel_type)
where 
 (o.receiver_area='罗湖区' or o.receiver_address like '%罗湖区%' )
 or  (c.address_city='深圳市' and c.address_county='罗湖区') 
) dd
inner JOIN 
crm_customer cc  on dd.customer_id = cc.customer_id
INNER JOIN 
crm_customer_external ec on cc.customer_id = ec.customer_id  
GROUP BY cc.customer_id )
dd inner join 
crm_customer cc  on dd.customer_id = cc.customer_id

select cc.* ,dd.totalAmount,dd.orderNum,dd.totalAmount/dd.orderNum as kdj,
dd.channel_name from (

select cc.customer_id,cc.totalAmount,cc.orderNum,GROUP_CONCAT(DISTINCT ec.channel_name) as channel_name  from (

select c.customer_id,sum(o.pay_ment_amount) as totalAmount,count(o.order_id) orderNum from crm_customer c 
INNER JOIN
crm_customer_external ec on c.customer_id = ec.customer_id 
INNER JOIN crm_order_info o on (o.source_customer_id = ec.external_userid and o.channel_type = ec.channel_type)
where 
 (o.receiver_area='罗湖区' or o.receiver_address like '%罗湖区%' )
 or  (c.address_city='深圳市' and c.address_county='罗湖区') 
   GROUP BY c.customer_id
) cc

INNER JOIN 
crm_customer_external ec on cc.customer_id = ec.customer_id  
GROUP BY cc.customer_id ,cc.totalAmount,cc.orderNum
)
dd inner join 
crm_customer cc  on dd.customer_id = cc.customer_id

编辑官方商城退单状态

## 20210607143924251
#已关闭
update Shop_ReturnOrders set Status=3,LogisticStatus=3 where  ReturnOrderId = 131294
#已完成
update Shop_ReturnOrders set Status=2 where  ReturnOrderId = 131290

pos查会员及其销售相关数据

      select t.会员卡号,t.会员手机号,t.会员姓名,t.开卡门店编号,t.开卡门店名称,t.区域名称,t.上级渠道编号,t.上级渠道名称,sum(t.amount) "消费金额",max(t.sheetdate) "最近一次消费日期", count(1) "消费频次"
 from (
select m.cardno "会员卡号",m.phone "会员手机号",m.name "会员姓名",c.code "开卡门店编号",c.name "开卡门店名称",v.WholeName "区域名称",c1.code "上级渠道编号",c1.name "上级渠道名称",
ss.sheetid,sum(ssg.amount) amount,ss.sheetdate
from membercard m
left join channel c on m.channelid=c.channelid
left join channel c1 on c.Parentid=c1.channelid
left join varea v on c.areaid=v.id
left join channeltypelist ctl on c.channeltypeid=ctl.id
left join salesheet ss on m.guid=ss.memberid
left join salesheetgoods ssg on ss.guid=ssg.guid
where ctl.channeltypename in ('零售店铺') and m.status=1  --and c.code='1137' --and  m.cardno='18676383401'
group by  m.cardno,m.phone,m.name,c.code,c.name,v.WholeName,c1.code,c1.name ,ss.sheetid,ss.sheetdate ) t 
group by  t.会员卡号,t.会员手机号,t.会员姓名,t.开卡门店编号,t.开卡门店名称,t.区域名称,t.上级渠道编号,t.上级渠道名称  

需求
统计 员工指定时间内添加的客户总数，有效客户总数，有效的客户时间段内 序号 1-100 101-10001等阶段的客户数 其中有效客户为 员工第一个添加 并且消费总金额要大于300

select 
	c.company_code AS companyCode,
	c.company_name AS companyName,
	c.channel_code AS storeCode,
	c.channel_name AS storeName,
	c.user_id AS userId,
	c.clerk_name AS clerkName,
	c.total AS total,
	r.effectiveTotal,r.jd1,	r.jd2,r.jd3,r.jd4,r.jd5
from 
(
		select 
		g.company_code,
		g.company_name,
		g.channel_code,
		g.channel_name,
		r.user_id,
		g.clerk_name,
		count(1) as total
		from 
			store_shopping_guide g 
		INNER JOIN
			crm_customer_flower_rel r 
		on r.user_id = g.clerk_no    
		where 
		r.create_time >='2021-01-06 00:00:00'
		and r.status = '0'
		and g.company_code ='8010'
		and g.status= 0
		GROUP BY g.company_code,g.company_name,g.channel_code,g.channel_name,r.user_id,g.clerk_name 
) c

LEFT JOIN 

(
		select 
		user_id,
		count(1) as effectiveTotal,
		count(case when pm >= 1 and pm <= 100 then 1 end) as jd1,
		count(case when pm >= 101 and pm <= 1000 then 1 end) as jd2,
		count(case when pm >= 1001 and pm <= 3000 then 1 end) as jd3,
		count(case when pm >= 3001 and pm <= 5000 then 1 end) as jd4,
		count(case when pm >= 5001 and pm <= 1000000 then 1 end) as jd5
		from 
		 (
				select 
				(@i:=@i+1) as pm, r.user_id,r.external_userid,r.create_time from 
				(
						select r.user_id,r.external_userid,r.create_time 
						from (
								select 
								r.user_id,r.external_userid,r.create_time,
								(select sum(o.pay_ment_amount) from  crm_customer_external e1 INNER JOIN  crm_order_info o
								on o.channel_type = e1.channel_type and o.source_customer_id = e1.external_userid  
								where o.scrm_order_status = 3  and e1.customer_id = r.customer_id) as saleAmount,
								(select sum(t.return_amount) from  crm_customer_external e1 INNER JOIN  crm_return_order_info t
								on t.channel_type = e1.channel_type and t.source_customer_id = e1.external_userid  
								where t.return_status = 8  and e1.customer_id = r.customer_id) as returnAmount
							
								FROM
									store_shopping_guide g 
								INNER JOIN
									crm_customer_flower_rel r 
								ON r.user_id = g.clerk_no 
                INNER JOIN 
								crm_customer_external e on e.customer_id = r.customer_id
								INNER JOIN 
								(
									select  external_userid,min(create_time) AS create_time
									from crm_customer_flower_rel
									where create_time
									GROUP BY external_userid
								 ) r1
								ON (r.external_userid = r1.external_userid and r.create_time = r1.create_time)
								where 
								r.create_time >='2021-08-06 00:00:00'
								and e.join_time >='2021-08-06 00:00:00'
								and r.status = '0'      
								and g.company_code ='8010'
								and g.status= 0
						) r
            where IFNULL(saleAmount,0) - IFNULL(returnAmount,0) >= 300
          
            ORDER BY r.create_time 

        ) r,(SELECT @i:=0) AS i
						
		 ) r
		GROUP BY r.user_id
)  r
on c.user_id = r.user_id

该sql存在的问题 ： 查询最高添加该客户的员工 进行了全表扫描

按分数段统计
400到500人数，300到400人数

select
count(case when score between 400 and 500 then 1 end) as 400到500,
count(case when score between 300 and 400 then 1 end) as 300到400
from student_info;

按分数段和专业统计
400到500人数，300到400人数

select
count(case when score between 400 and 500 then 1 end) as 400到500,
count(case when score between 300 and 400 then 1 end) as 300到400
from student_info GROUP BY profession;

mysql update时，更新的表不能在set和where中用于子查询

update 
crm_customer_flower_rel r,
( select    r.id  from 
	 crm_customer_flower_rel r 
  INNER JOIN 
	(
	select  external_userid,min(create_time) AS create_time
	from crm_customer_flower_rel
	where create_time
	GROUP BY external_userid
   ) r1
  on (r.external_userid = r1.external_userid and r.create_time = r1.create_time)
) s

set first_join= '1'
where s.id = r.id