sql查询

Shine°

已于 2024-04-09 13:44:30 修改

阅读量2.9k

点赞数

分类专栏： mysql 文章标签： sql

于 2021-10-21 17:02:13 首次发布

本文链接：https://blog.csdn.net/weixin_44415815/article/details/120890130

版权

mysql 专栏收录该内容

19 篇文章 1 订阅

订阅专栏

根据数组获取7天内的数据总和

SELECT r.topic_id,SUM(r.visit) AS visit ,SUM(r.`like`) AS `like`,SUM(r.`comment`) AS `comment`
FROM forum_customer_topic_report AS r
LEFT JOIN forum_customer_topic AS t ON t.id = r.topic_id
WHERE r.topic_id IN ('312403', '312980') 
AND DATE_ADD(t.publish_time,INTERVAL 7 DAY) >= r.date 
GROUP BY r.topic_id

SUM(comment) AS comment,求字段总和
DATE_SUB(NOW(),INTERVAL 7 DAY) <= date 时间内
GROUP BY topic_id 字段分组

关联查询

SELECT t.id, t.customer_id, c.nick_name,l.title,t.gift_name,t.status, t.create_time 
FROM forum_activity_lottery_order AS t 
LEFT JOIN forum_activity_lottery AS l ON (l.id = t.lottery_id) 
LEFT JOIN forum_customer AS c ON (c.id = t.customer_id) 
WHERE DATE_FORMAT( t.create_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' );

关联分组求和

SELECT customer_id,c.nick_name,COUNT(focus_id) 
FROM forum_customer_focus AS f 
LEFT JOIN forum_game AS g ON(g.id = f.focus_id) 
LEFT JOIN forum_customer AS c ON(c.id = f.customer_id) 
WHERE type = 1 AND g.category = 1 
GROUP BY customer_id

LEFT JOIN forum_activity_lottery AS l ON (l.id = t.lottery_id) 关联表

分组总和

SELECT customer_id,COUNT(focus_id) 
FROM forum_customer_focus 
GROUP BY customer_id

查询数组值从某日开始到某日结束的总和

SELECT topic_id,date,SUM(visit) AS visit,SUM(`like`) AS `like`,SUM(`comment`) AS `comment` 
FROM forum_customer_topic_report 
WHERE topic_id IN ('311276','311285','311289','311291','311292','311296') 
AND DATE_ADD(date,INTERVAL 1 DAY) >= date 
GROUP BY topic_id

首先按照排序值排序，排除排序值为0的

SELECT * FROM forum_discuss
WHERE discuss_status = 1
ORDER BY FIELD(discuss_sort,0) ASC,discuss_sort ASC,discuss_hot DESC

多对多关联查询

SELECT t.id,t.title,d.discuss_name
FROM forum_discuss_topic AS dt
LEFT JOIN forum_customer_topic AS t ON t.id = dt.topic_id
LEFT JOIN forum_discuss AS d ON d.id = dt.discuss_id
WHERE d.id IN (2)
ORDER BY t.create_time DESC

查看专区活跃等级用户数量

SELECT ca.game_id,COUNT(DISTINCT customer_id)
FROM forum_customer_active AS ca
WHERE ca.game_id IN (11,40,39,26,24,43) AND ca.active >= 3000 AND ca.active < 4500
GROUP BY ca.game_id

添加一条数据

INSERT INTO forum_customer_point_log (customer_id,text,point,current_point,type,create_time) 
VALUES (1,'1',1,'1','1','1');

批量添加数据

INSERT INTO forum_customer_point_log (customer_id,text,point,current_point,type,create_time)
SELECT id,'本周聊点啥投票奖励','200',point,'1',NOW()
FROM forum_customer 
where id IN ('1','2')

批量修改数据

UPDATE forum_customer SET point = point +200 WHERE id IN ('1','2')

模糊查询替换

update forum_game set game_icon = replace(game_icon,"http://tradingimage.3975.com/","https://tradingimage.3975.com/")

时间段分组统计

SELECT discuss_id,COUNT(DISTINCT ip),COUNT(DISTINCT customer_id)
FROM forum_customer_discuss_scan_log
WHERE create_time BETWEEN '2021-11-02 00:00:00' AND '2021-11-02 23:59:59'
GROUP BY discuss_id

话题帖子查询

SELECT t.id,t.customer_id,c.nick_name,t.title,t.content,t.`like`
FROM forum_discuss_topic AS dt
LEFT JOIN forum_customer_topic AS t ON t.id = dt.topic_id
LEFT JOIN forum_customer AS c ON t.customer_id = c.id
WHERE dt.discuss_id = 15 AND t.create_time <= '2021-11-7 17:00:00'
ORDER BY t.create_time DESC

SELECT t.id,t.customer_id,c.nick_name,g.game_name,t.title,t.content,t.`like`,t.create_time
FROM forum_discuss_topic AS dt
LEFT JOIN forum_customer_topic AS t ON t.id = dt.topic_id
LEFT JOIN forum_game AS g ON g.id = t.game_id
LEFT JOIN forum_customer AS c ON t.customer_id = c.id
WHERE dt.discuss_id = 441 AND t.create_time BETWEEN '2023-01-25 00:00:00' AND '2023-01-25 23:59:59'
ORDER BY t.create_time DESC

帖子投票用户

SELECT vdo.`value`,vl.customer_id,c.nick_name
FROM forum_vote AS v
LEFT JOIN forum_vote_detail AS vd ON vd.vote_id = v.id
LEFT JOIN forum_vote_detail_option AS vdo ON vdo.detail_id = vd.id
LEFT JOIN forum_vote_log_detail AS vld ON vld.option_id = vdo.id
LEFT JOIN forum_vote_log AS vl ON vl.id = vld.log_id
LEFT JOIN forum_customer AS c ON c.id = vl.customer_id
WHERE v.topic_id  = '318675'
ORDER BY vdo.id DESC

两表合并字段求和

SELECT date, discuss_id,COUNT(DISTINCT customer_id) AS dau,COUNT(DISTINCT ip) AS ip
FROM(
SELECT DATE_FORMAT(tl.create_time, '%Y-%m-%d') AS date, dt.discuss_id,tl.customer_id,tl.ip
FROM forum_customer_topic_scan_log AS tl
LEFT JOIN forum_discuss_topic AS dt ON dt.topic_id = tl.topic_id
WHERE dt.discuss_id != '' AND tl.create_time BETWEEN '2021-11-17 00:00:00' AND '2021-11-17 23:59:59'
UNION
SELECT DATE_FORMAT(create_time, '%Y-%m-%d') AS date, discuss_id,customer_id,ip
FROM forum_customer_discuss_scan_log
WHERE create_time BETWEEN '2021-11-17 00:00:00' AND '2021-11-17 23:59:59'
)t
GROUP BY discuss_id

商品详细

SELECT g.id,a.app_name,c.category_name,s.email_title,s.email_content,s.price,g.start_time,g.end_time,gt.gift_value,c.unit_price
FROM forum_market_goods as g
LEFT JOIN forum_market_app as a on a.id = g.app_id
LEFT JOIN forum_market_category as c on c.id = g.category_id
LEFT JOIN forum_market_goods_sku as s on s.goods_id = g.id
LEFT JOIN forum_market_gift as gt on gt.id = s.gift_code
WHERE g.id >'1630'

筛选一年中每天的数量

select year(create_time) as 年,
month(create_time) as 月,
day(create_time) as 日,
count(*) as 冰雪传奇数量
from forum_customer_focus
where year(create_time)=2021 AND focus_id = 40
group by year(create_time), month(create_time), day(create_time)

查询近三个月的数据

SELECT p.customer_id,c.sex
FROM forum_customer_point_mission_progress AS p
LEFT JOIN forum_customer AS c ON c.id = p.customer_id
WHERE p.add_time > DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
GROUP BY p.customer_id

查询专区每日关注以及累计关注

select DATE_FORMAT(create_time,'%Y-%m-%d') AS createTime,
focus_id AS focusID,count(*) as 每日新增数量,
(SELECT COUNT(*) FROM forum_customer_focus WHERE create_time <= DATE_FORMAT(createTime,'%Y-%m-%d 23:59:59') AND focus_id = focusID AND type = 1 ) AS 累计数量
from forum_customer_focus 
where year(create_time)=2022 AND focus_id IN (11)
group by focus_id, DATE_FORMAT(create_time,'%Y-%m-%d')

商品详情

SELECT g.id,a.app_name,c.category_name,s.email_title,s.email_content,s.price,g.start_time,g.end_time
FROM forum_market_goods as g
LEFT JOIN forum_market_app as a on a.id = g.app_id
LEFT JOIN forum_market_category as c on c.id = g.category_id
LEFT JOIN forum_market_goods_sku as s on s.goods_id = g.id
WHERE g.id >'650'

专区活跃等级

SELECT
CASE 
WHEN active BETWEEN 0 AND 150 THEN '1'
WHEN active BETWEEN 150 AND 350 THEN '2'
WHEN active BETWEEN 350 AND 750 THEN '3'
WHEN active BETWEEN 750 AND 1500 THEN '4'
WHEN active BETWEEN 1500 AND 2250 THEN '5'
WHEN active BETWEEN 2250 AND 3000 THEN '6'
WHEN active BETWEEN 3000 AND 4500 THEN '7'
WHEN active BETWEEN 4500 AND 6000 THEN '8'
WHEN active >=6000 THEN '9'
END AS coust , COUNT(DISTINCT customer_id)
FROM forum_customer_active
WHERE game_id IN (64)
GROUP BY coust

连表更新字段值

UPDATE forum_market_customer_order AS o
LEFT JOIN forum_market_customer_order_detail AS d on d.order_id = o.id
LEFT JOIN forum_market_goods AS g on g.id = d.goods_id
LEFT JOIN forum_market_category AS c ON c.id = g.category_id
SET o.cost = d.num * c.unit_price

查询判断更新字段

UPDATE forum_market_customer_order AS o
LEFT JOIN forum_market_customer_order_pay_info AS r ON r.order_id = o.id 
SET o.status = CASE WHEN o.price != r.pay_amount THEN '-1' ELSE o.status END

查询指定时间段内的数据总量

SELECT
	t.id 
FROM
	forum_customer_topic AS t
	LEFT JOIN forum_customer_topic_report AS r ON r.topic_id = t.id 
WHERE
	r.date BETWEEN DATE_FORMAT( t.publish_time, '%Y-%m-%d %H:%i:%s' ) 
	AND DATE_FORMAT( DATE_ADD( t.publish_time, INTERVAL 7 DAY ), '%Y-%m-%d %H:%i:%s' ) 
GROUP BY
	t.id 
HAVING
	SUM( r.`like` ) > $weekLike

封号

INSERT INTO forum_customer_black_list (customer_id,type,reason,operation_userid,`status`,add_time,end_time)
SELECT id,'1','工作室刷积分','1',1,NOW(),'2099-12-31 23:59:59'
FROM forum_customer 
where id IN ('297947','297952')

工作室

SELECT id,create_time
FROM forum_customer
WHERE create_time BETWEEN '2022-06-09 00:00:00' AND '2022-06-12 23:59:59'

SELECT customer_id
FROM forum_customer_black_list
WHERE customer_id IN ('392667','392668')

分组排序取最新值

SELECT * FROM ( SELECT customer_id, add_time FROM forum_customer_point_mission_progress WHERE mission_id = 1 AND customer_id IN ('100166','413803') ORDER BY add_time DESC LIMIT 10000000 ) a GROUP BY customer_id

礼包列表导出

SELECT o.id, g.game_name, e.gift_name, e.gift_content, e.gift_point, o.create_time, o.status, o.customer_id, o.fictitious_server_name, o.fictitious_role_name, o.fictitious_server_id, o.fictitious_role_id
FROM forum_activity_exchange_order AS o
LEFT JOIN forum_activity_exchange AS e ON e.id = o.exchange_id
LEFT JOIN forum_game AS g ON g.id = e.game_id
WHERE o.create_time BETWEEN '2022-05-01 00:00:00' AND '2022-07-02 23:59:59'

查重，多字段查重

select goods_id,`status` from tab_order WHERE `status` = 4 group by goods_id,`status` having count(*) >2;

多字段模糊查询

SELECT id,customer_id,game_id,tag_id,title,content,is_water,create_time FROM forum_customer_topic WHERE is_water = 0 AND ( title LIKE "%签到%" OR title LIKE "%打卡%" OR content LIKE "%签到%" OR content LIKE "%打卡%" )

多字段模糊更新

UPDATE forum_customer_topic SET is_water = 1 WHERE is_water = 0 AND ( title LIKE "%签到%" OR title LIKE "%打卡%" OR content LIKE "%签到%" OR content LIKE "%打卡%" )

分组查询

SELECT user_id, SUM(price) FROM order_info WHERE create_date BETWEEN '2021-11-03 00:00:00' AND '2022-11-03 23:59:59' AND state = 2 AND user_id IN (SELECT user_id FROM order_info WHERE create_date BETWEEN '2021-07-01 00:00:00' AND '2022-06-30 23:59:59' AND state = 2 GROUP BY user_id HAVING sum(price) > 500000) GROUP BY user_id;

SELECT SUM(price) FROM order_info WHERE create_date BETWEEN '2021-11-03 00:00:00' AND '2022-11-03 23:59:59' AND state = 2 AND user_id = 47377463;

SELECT SUM(price) FROM order_info WHERE create_date BETWEEN '2021-07-01 00:00:00' AND '2022-06-30 23:59:59' AND state = 2 AND user_id = 47377463;

查询时间范围内的用户积分

SELECT
	customer_id,
	SUM( CASE WHEN type = 1 THEN point ELSE 0 END ) AS '收入',
	SUM( CASE WHEN type = 2 THEN point ELSE 0 END ) AS '支出' 
FROM
	forum_customer_point_log 
WHERE
	create_time BETWEEN '2022-11-01 00:00:00' 
	AND '2022-11-30 23:59:59' 
GROUP BY
	customer_id

查询用户总积分

SELECT
	SUM(point - use_point) AS point, customer_id
FROM
	forum_customer_point_log 
WHERE
	expire_time >= unix_timestamp(now()) AND type = 1
GROUP BY
	customer_id

查询标签帖子

SELECT t.id,c.nick_name,t.customer_id,t.title,t.content,t.`like`,t.`comment`,t.create_time 
FROM forum_customer_topic AS t LEFT JOIN forum_customer AS c ON c.id = t.customer_id 
WHERE t.tag_id = 14 AND t.game_id = 85 AND t.create_time > '2022-12-08 00:00:00'

查询订单价格

SELECT *,FROM_UNIXTIME(add_time,'%Y-%m-%d %H:%i:%s') as pay_time FROM recharge_info WHERE user_id = '19937540' AND ((pay_id = 'weixin_pay' AND STATUS = 2) OR ( pay_id = '9999' AND STATUS = 1 ))  AND add_time BETWEEN '1641955070' AND '1673491090' ;

SELECT * FROM order_info WHERE user_id = '18536683' AND state = 2 AND pay_date BETWEEN '2022-01-12 10:39:44' AND '2023-01-12 10:39:22'

登录用户专区礼包活跃值

SELECT ca.game_id,ca.customer_id,g.game_name 
FROM forum_customer_active AS ca LEFT JOIN forum_game AS g ON g.id = ca.game_id 
WHERE ca.game_id IN ( 85, 96 ) AND ca.active >= 100 
	AND ca.customer_id IN ( SELECT customer_id FROM forum_customer_point_mission_progress WHERE mission_id = 1 AND add_time BETWEEN '2023-01-31 00:00:00' AND '2023-02-06 23:59:59') 
ORDER BY ca.game_id ASC

SELECT customer_id ,fictitious_server_name,fictitious_role_name,fictitious_server_id,fictitious_role_id 
FROM forum_activity_exchange_order WHERE exchange_id = '1767' AND customer_id IN ('431954','570860')

批量更新不同id不同值

UPDATE forum_customer 
SET point = (
	SELECT
		SUM( point - use_point ) AS point 
	FROM
		forum_customer_point_log 
	WHERE
		expire_time >= unix_timestamp(
		now()) 
		AND type = 1 
		AND customer_id = forum_customer.id
	)
	WHERE id IN ('189658','102058') ;

获取任务订单

SELECT
	o.id,
	g.game_name,
	c.nick_name,
	o.customer_id,
	t.task_name,
	o.fictitious_role_id,
	o.fictitious_role_name,
	o.fictitious_server_name,
	o.create_time,
	o.STATUS 
FROM
	forum_activity_task_order AS o
	LEFT JOIN forum_activity_task AS t ON t.id = o.task_id
	LEFT JOIN forum_customer AS c ON c.id = o.customer_id
	LEFT JOIN forum_game AS g ON g.id = t.game_id 
WHERE
	o.create_time BETWEEN '2023-02-13 00:00:00' AND '2023-02-19 23:59:59'

获取订单发货

SELECT i.user_name,i.phone,i.province,i.city,i.area,i.street,i.detail_address,o.logistics_id,o.logistics_number,o.logistics_user_id,o.logistics_time
FROM
	forum_market_customer_order AS o
	LEFT JOIN forum_market_customer_order_receive_info AS i ON i.order_id = o.id 
WHERE
	o.order_goods_type IN ( 1, 3 ) 
	AND o.logistics_time BETWEEN '2023-01-01 00:00:00' 
	AND '2023-02-28 23:59:59'

订单分类用户数查询

SELECT 
o.customer_id,
SUM(CASE WHEN g.category_id = 53 THEN 1 ELSE 0 END) AS category_53,
SUM(CASE WHEN g.category_id = 54 THEN 1 ELSE 0 END) AS category_54,
SUM(CASE WHEN g.category_id = 58 THEN 1 ELSE 0 END) AS category_58,
SUM(CASE WHEN g.category_id = 59 THEN 1 ELSE 0 END) AS category_59,
SUM(CASE WHEN g.category_id = 66 THEN 1 ELSE 0 END) AS category_66,
SUM(CASE WHEN g.category_id = 67 THEN 1 ELSE 0 END) AS category_67
FROM
	forum_market_customer_order AS o
	LEFT JOIN forum_market_customer_order_detail AS d ON d.order_id = o.id
	LEFT JOIN forum_market_goods AS g ON g.id = d.goods_id 
WHERE
	o.customer_id IN (
'702082',
	)
	AND o.create_time BETWEEN '2023-03-20 00:00:00' AND '2023-3-31 23:59:59'
	GROUP BY o.customer_id

订单更新发货

UPDATE forum_market_customer_order SET status = '3', logistics_id = 5, logistics_number = '75579928434981', logistics_user_id = '60', logistics_time = NOW(), delivery_time = NOW() WHERE order_num = 'SJSC_202304282939039080';

SELECT id,order_num FROM forum_market_customer_order WHERE order_num IN ()

UPDATE `forum_market_customer_order_detail_new` `d`
	LEFT JOIN `forum_new_market_goods_item` `i` ON i.id = d.item_id
	LEFT JOIN `forum_new_market_gift` `g` ON g.id = i.gift_id
	SET d.delivery_status = 1, d.delivery_time = NOW()
  WHERE d.order_id = '14695' AND d.delivery_status = '0' AND g.type = '1';

商城替换专区（新）

SELECT id FROM forum_market_app ORDER BY id ASC;
DELETE FROM forum_new_market_goods_app WHERE goods_id IN ('1','2','3');
INSERT INTO forum_new_market_goods_app (goods_id,app_id) VALUES ('3','1');

用户订单查询

SELECT o.order_num,d.goods_id,g.goods_name,g.category_id,c.category_name,o.customer_id,cc.nick_name,o.total_price,o.price,o.cost,o.`status`,o.create_time 
FROM forum_market_customer_order AS o
LEFT JOIN forum_market_customer_order_detail AS d on d.order_id = o.id
LEFT JOIN forum_market_goods AS g on g.id = d.goods_id
LEFT JOIN forum_market_category AS c ON c.id = g.category_id
LEFT JOIN forum_customer AS cc ON cc.id = o.customer_id
WHERE o.customer_id IN ('457074','457504')

商城报表

SELECT
	DATE_FORMAT( o.create_time, '%Y-%m-%d' ) AS `date`,
	`d`.`goods_id`,
	count( 0 ) AS `num`,
	SUM( CASE WHEN o.STATUS = 0 THEN 1 ELSE 0 END ) AS `cancel_num`,
	SUM( CASE WHEN o.STATUS = 5 THEN 1 ELSE 0 END ) AS `refund_num`,
	SUM(
	CASE
			
			WHEN o.STATUS = 0 THEN
			0 
			WHEN o.STATUS = 1 THEN
			0 
			WHEN o.STATUS = - 1 THEN
			0 
			WHEN o.STATUS = 5 THEN
			0 ELSE 1 
		END 
		) AS `success_num`,
		SUM(
		CASE
				
				WHEN o.STATUS = 0 THEN
				0 
				WHEN o.STATUS = 1 THEN
				0 
				WHEN o.STATUS = 5 THEN
				0 ELSE o.price 
			END 
			) AS `price`,
			SUM( CASE WHEN o.STATUS = 0 THEN 0 WHEN o.STATUS = 1 THEN 0 ELSE o.price END ) AS `goods_price`,
			SUM(
			CASE
					
					WHEN o.STATUS = 0 THEN
					0 
					WHEN o.STATUS = 1 THEN
					0 
					WHEN o.STATUS = 5 THEN
					0 ELSE i.pay_amount 
				END 
				) AS `actual_price`,
				SUM(
				CASE
						
						WHEN o.STATUS = 0 THEN
						0 
						WHEN o.STATUS = 1 THEN
						0 
						WHEN o.STATUS = - 1 THEN
						0 
						WHEN o.STATUS = 5 THEN
						0 ELSE o.cost 
					END 
					) AS `cost_total_price`,
					SUM( CASE WHEN o.STATUS = 5 THEN o.price ELSE 0 END ) AS `refund_price`,
					SUM(
					CASE
							
							WHEN o.STATUS = 0 THEN
							0 
							WHEN o.STATUS = 1 THEN
							0 
							WHEN o.STATUS = - 1 THEN
							0 
							WHEN o.STATUS = 5 THEN
							0 ELSE d.num 
						END 
						) AS `delivery_num` 
					FROM
						`forum_market_customer_order` `o`
						LEFT JOIN (
						SELECT
							* 
						FROM
							`forum_market_customer_order_detail_new` 
						WHERE
						`id` IN ( SELECT MIN( id ) FROM `forum_market_customer_order_detail_new` GROUP BY `order_id` )) `d` ON d.order_id = o.id
						LEFT JOIN `forum_market_customer_order_pay_info` `i` ON i.order_id = o.id 
					WHERE
						( o.create_time BETWEEN '2023-05-19 00:00:00' AND '2023-05-19 23:59:59' ) 
						AND (
							NOT (
								`o`.`customer_id` IN (
									'1',
									'2',
									'3',
									'4',
									'100003',
									'100006',
									'100026',
									'100121',
									'101775',
									'101776',
									'101810',
									'102058',
									'104950',
									'113652',
									'113753',
									'119059',
									'152778',
									'175980',
									'175994',
									'178447' 
								))) 
						AND ( o.order_type = '2' ) 
					GROUP BY
					DATE_FORMAT( o.create_time, '%Y-%m-%d' ),
	`d`.`goods_id`

删除商品对应专区

DELETE FROM forum_new_market_goods_app 
WHERE app_id IN ( '3', '4', '13', '14', '15' ) AND goods_id IN ( '88', '87', '86' )

删除商品品类cdk

DELETE FROM forum_new_market_gift_cdk WHERE gift_id IN ('75','68') AND is_get != 1;

导出评论

SELECT * FROM forum_customer_topic_comment WHERE create_time BETWEEN '2023-06-13 00:00:00' AND '2023-06-14 00:00:00'

SELECT t.*,c.nick_name,a.role_name,a.role_id,a.server_name,a.server_id
FROM forum_customer_topic_comment AS t
	LEFT JOIN forum_customer AS c ON c.id = t.customer_id 
	LEFT JOIN forum_market_customer_fictitious_address AS a ON a.customer_id = t.customer_id 
WHERE t.topic_id = 1832210 AND t.is_imgs = 1 AND t.create_time BETWEEN '2023-06-26 00:00:00' AND '2023-07-03 00:00:00'

导出app订单

SELECT l.* FROM forum_customer_app_equipment_log AS l LEFT JOIN forum_market_customer_order as o ON o.order_num = l.relation_id WHERE l.type = '4' AND o.create_time BETWEEN '2023-06-12 00:00:00' AND '2023-06-18 23:59:59';

导出关注ip

SELECT f.customer_id, f.create_time, l.ip FROM forum.forum_customer_focus AS f JOIN forum_log.forum_customer_day_login_log_20230602 AS l ON l.customer_id = f.customer_id WHERE f.type = 1 AND f.create_time BETWEEN '2023-06-02 00:00:00' AND '2023-06-02 23:59:59';

历史总关注数

SELECT g.game_name,g.juhe_id, l.focus_id, COUNT(DISTINCT l.customer_id) as num FROM forum_customer_focus_log AS l LEFT JOIN forum_game as g on g.id = l.focus_id WHERE l.type = 1 AND l.action = 1 AND l.focus_id in ('105','85','96','64') GROUP BY l.focus_id

当月用户活跃等级

SELECT
	customer_id ,SUM(active)
FROM
	forum_active_mission_log 
WHERE
	game_id = '105' 
	AND add_time BETWEEN '2023-06-01 00:00:00' AND '2023-06-30 23:59:59' 
GROUP BY
	customer_id 
HAVING
	SUM( active ) > 50;
	
	
SELECT customer_id ,
	CASE 
WHEN active BETWEEN 0 AND 150 THEN '1'
WHEN active BETWEEN 150 AND 350 THEN '2'
WHEN active BETWEEN 350 AND 750 THEN '3'
WHEN active BETWEEN 750 AND 1500 THEN '4'
WHEN active BETWEEN 1500 AND 2250 THEN '5'
WHEN active BETWEEN 2250 AND 3000 THEN '6'
WHEN active BETWEEN 3000 AND 4500 THEN '7'
WHEN active BETWEEN 4500 AND 6000 THEN '8'
WHEN active >=6000 THEN '9'
END AS coust  FROM	forum_customer_active WHERE game_id = '105' AND customer_id IN (
'104106',
'105261')

SELECT id ,
	CASE 
WHEN exp BETWEEN 0 AND 670 THEN 'VIP0'
WHEN exp BETWEEN 670 AND 870 THEN 'VIP1'
WHEN exp BETWEEN 870 AND 1270 THEN 'VIP2'
WHEN exp BETWEEN 1270 AND 2070 THEN 'VIP3'
WHEN exp BETWEEN 2070 AND 3570 THEN 'VIP4'
WHEN exp BETWEEN 3570 AND 5070 THEN 'VIP5'
WHEN exp BETWEEN 5070 AND 6570 THEN 'VIP6'
WHEN exp BETWEEN 6570 AND 9570 THEN 'VIP7'
WHEN exp BETWEEN 9570 AND 12570 THEN 'VIP8'
WHEN exp >=12570 THEN 'VIP9'
END AS coust  FROM	forum_customer WHERE id IN (
'104106',
'105261')

SELECT customer_id, SUM( exp )  
FROM
	forum_customer_exp_log 
WHERE
	add_time BETWEEN '1685548800' AND '1688140799' 
	AND customer_id IN ( '104106', '105261' ) 
GROUP BY
	customer_id


SELECT customer_id, SUM( point )  
FROM
	forum_customer_point_log 
WHERE
	create_time BETWEEN '2023-06-01 00:00:00' AND '2023-06-30 23:59:59' 
	AND customer_id IN ( '104106', '105261' ) 
GROUP BY
	customer_id

时段实例购买金额

SELECT DATE_FORMAT(n.create_time, '%Y-%m-%d %H:00:00') AS hour, SUM(n.price * n.num) AS order_price
FROM forum_market_customer_order_detail_new AS n
LEFT JOIN forum_new_market_goods_item AS i ON i.id = n.item_id
LEFT JOIN forum_new_market_gift AS g ON g.id = i.gift_id
WHERE g.type = '5' AND n.delivery_status = '1' AND n.create_time BETWEEN '2023-07-01 00:00:00' AND '2023-08-02 23:59:59' 
GROUP BY HOUR(n.create_time), DATE(n.create_time)
ORDER BY n.create_time;

购买实例记录

SELECT o.customer_id,y.type,y.bind_time,y.grade_name,y.instance_code,y.create_time
FROM
	forum_market_customer_order_detail_new_yun AS y
	LEFT JOIN forum_market_customer_order_detail_new AS n ON n.id = y.order_detail_id
	LEFT JOIN forum_market_customer_order AS o ON o.id = n.order_id 
WHERE
	y.send_status = '1' AND
	y.create_time BETWEEN '2023-07-17 00:00:00' AND '2023-08-03 23:59:59'

专区用户活跃等级，VIP等级

SELECT
a.customer_id,
CASE 
WHEN active BETWEEN 0 AND 150 THEN '1'
WHEN active BETWEEN 150 AND 350 THEN '2'
WHEN active BETWEEN 350 AND 750 THEN '3'
WHEN active BETWEEN 750 AND 1500 THEN '4'
WHEN active BETWEEN 1500 AND 2250 THEN '5'
WHEN active BETWEEN 2250 AND 3000 THEN '6'
WHEN active BETWEEN 3000 AND 4500 THEN '7'
WHEN active BETWEEN 4500 AND 6000 THEN '8'
WHEN active >=6000 THEN '9'
END AS coust,
CASE 
WHEN exp BETWEEN 0 AND 670 THEN 'VIP0'
WHEN exp BETWEEN 670 AND 870 THEN 'VIP1'
WHEN exp BETWEEN 870 AND 1270 THEN 'VIP2'
WHEN exp BETWEEN 1270 AND 2070 THEN 'VIP3'
WHEN exp BETWEEN 2070 AND 3570 THEN 'VIP4'
WHEN exp BETWEEN 3570 AND 5070 THEN 'VIP5'
WHEN exp BETWEEN 5070 AND 6570 THEN 'VIP6'
WHEN exp BETWEEN 6570 AND 9570 THEN 'VIP7'
WHEN exp BETWEEN 9570 AND 12570 THEN 'VIP8'
WHEN exp >=12570 THEN 'VIP9'
END AS vip
FROM forum_customer_active as a 
LEFT JOIN forum_customer as c on c.id = a.customer_id
WHERE a.game_id IN (85)
GROUP BY a.customer_id

专区活跃度

SELECT
CASE 
WHEN a.active BETWEEN 0 AND 150 THEN '1'
WHEN a.active BETWEEN 150 AND 350 THEN '2'
WHEN a.active BETWEEN 350 AND 750 THEN '3'
WHEN a.active BETWEEN 750 AND 1500 THEN '4'
WHEN a.active BETWEEN 1500 AND 2250 THEN '5'
WHEN a.active BETWEEN 2250 AND 3000 THEN '6'
WHEN a.active BETWEEN 3000 AND 4500 THEN '7'
WHEN a.active BETWEEN 4500 AND 6000 THEN '8'
WHEN a.active >=6000 THEN '9'
END AS coust , a.customer_id,c.last_login_time
FROM forum_customer_active as a 
LEFT JOIN forum_customer as c on c.id = a.customer_id
WHERE a.game_id IN (85) AND c.last_login_time BETWEEN '2024-03-01 00:00:00' AND '2024-04-01 00:00:00'
GROUP BY a.customer_id