SQL学习
SELECT (SELECT WXName FROM UserInfo where id=[UserMoneyLog].userid) as NickName, Money FROM [dbo].[UserMoneyLog] where Money>0 and ActivityId=21
查询订单所属员工 SELECT * FROM [dbo].[UserInfo] WHERE ID=(SELECT USERID FROM KPITrade WHERE TradeNo='156017267120200212194613119148')
查询提现数据： select b.NickName,b.WeixinOpenId,a.Money,a.IsShenHe,a.CreateTime from MoneyLogs a left join Accounts b on a.AccountsId=b.Id where a.ActivityId=131 and a.IsShenHe=1 -- 0 未审核 1 已审核
查询 微信名 属于那个员工的浏览 SELECT ActivityId AS 活动ID, ( SELECT Name FROM UserInfo WHERE id = KPIFission.UserId ) AS 员工名, ( SELECT Phone FROM UserInfo WHERE id = KPIFission.UserId ) AS 员工手机号, VisitorName AS 浏览人微信名 FROM [dbo].[KPIFission] WHERE VisitorName LIKE '%浏览人微信名%' AND Status = 1 ORDER BY id
远程服务器非本机服务器地址写法
39.105.136.137\SQLEXPRESS,1433
KPI查询微信用户隶属员工
SELECT ActivityId AS 活动ID, ( SELECT Name FROM UserInfo WHERE id = KPIFission.UserId ) AS 员工名, ( SELECT Phone FROM UserInfo WHERE id = KPIFission.UserId ) AS 员工手机号, VisitorName AS 浏览人微信名 FROM [dbo].[KPIFission] WHERE VisitorName LIKE '%浏览人微信名%' AND Status = 1 ORDER BY id
完成积攒
select * from Accounts where id in ( select AccountsId from JZLogs where ActivityId=119 group by ActivityId, AccountsId having COUNT(AccountsId)>10 )
导出tx数据
SELECT (select WXName FROM UserInfo where id=[Trade].UserId) AS WXName, Money, PayedTime, 'succeed' FROM [dbo].[Trade] where TradeType=2 and Status=2 and typeid=46 order by PayedTime desc
KPI导出
SELECT (SELECT WXName FROM UserInfo where id=[UserMoneyLog].userid) as NickName, Money,CreateTime , 'succeed' FROM [dbo].[UserMoneyLog] where Money>0 and ActivityId=53
分享导出
select b.NickName,a.Points, a.AddTime ,'succeed' from PointsLogs a left join Accounts b on a.AccountId=b.Id where a.ActivityId=41
多任务奖励查下
/****** 浏览查询 ******/ SELECT UserId,(select name from userinfo where id=userid), COUNT ( * ) FROM [KPIFission] WHERE ActivityId = 103 AND Status = 1 AND UserId IN ( SELECT UserId FROM [UserInActivity] WHERE ActivityId = 103 AND StaffFlag = 1 ) AND CreateTime BETWEEN '2020-01-11 00:01' AND '2020-01-14 23:59' GROUP BY UserId ORDER BY COUNT ( * ) DESC /****** 转发查询 ******/ SELECT T.userid ,(select name from userinfo where id=userid), COUNT ( * ) FROM ( SELECT userid, ShareUnionId FROM [KPIFission] WHERE ActivityId = 103 AND UnionId != ShareUnionId AND CreateTime BETWEEN '2020-01-11 00:01' AND '2020-01-14 23:59' AND UserId IN ( SELECT UserId FROM [UserInActivity] WHERE ActivityId = 103 AND StaffFlag = 1 ) GROUP BY userid, ShareUnionId ) T GROUP BY T.userid ORDER BY COUNT ( * ) DESC /****** 售卡查询 ******/ SELECT UserId,(select name from userinfo where id=userid), COUNT ( * ) FROM [KPITrade] WHERE ActivityId = 103 AND ProductName = '优惠券' AND CreateTime BETWEEN '2020-01-11 00:01' AND '2020-01-14 23:59' AND UserId IN ( SELECT UserId FROM [UserInActivity] WHERE ActivityId = 103 AND StaffFlag = 1 ) GROUP BY UserId ORDER BY COUNT ( * ) DESC
秒杀归属到品牌门店
select a.OrderNo 订单号,a.Title 产品名称,a.Price 费用,a.Status 支付成功,b.Phone 电话,b.NickName 微信名,b.RealName 姓名, (select top 1 NickName from Accounts where WeixinUnionId=b.FromOpenId and ActivityId=b.ActivityId) 推荐人, (select top 1 Phone from Accounts where WeixinUnionId = b.FromOpenId and ActivityId = b.ActivityId) 推荐人电话, CONVERT(nvarchar(20), a.AddTime, 120) 时间, (select top 1 NickName from Accounts where WeixinUnionId=a.ShareUnion and ActivityId=b.ActivityId) 分享者,c.StoreName 店铺, c.BrandName 品牌,c.Name 员工名 from orders a left join Accounts b on a.AccountsId = b.Id left join KPI.dbo.UserInActivityView c on b.FromOpenId=c.UnionId where a.Status = '支付成功' and a.ProductId <> 0 and a.ActivityId=126 and c.ActivityId=(select top 1 KpiActivityId from Activity where id=126) --select * from Orders where ActivityId=58 and Status = '支付成功' and ProductId=0 select a.OrderNo 订单号,a.Price 费用,a.Status 支付成功,b.Phone 电话,b.NickName 微信名,b.RealName 姓名,(select top 1 NickName from Accounts where WeixinUnionId=b.FromOpenId and ActivityId=b.ActivityId) 推荐人, (select top 1 Phone from Accounts where WeixinUnionId = b.FromOpenId and ActivityId = b.ActivityId) 推荐人电话,CONVERT(nvarchar(20), a.AddTime, 120) 时间, (select top 1 NickName from Accounts where WeixinUnionId=a.ShareUnion and ActivityId=b.ActivityId) 分享者 ,c.StoreName 店铺, c.BrandName 品牌,c.Name 员工名 from orders a left join Accounts b on a.AccountsId = b.Id left join KPI.dbo.UserInActivityView c on b.FromOpenId=c.UnionId where a.Status = '支付成功' and a.ProductId = 0 and a.ActivityId=126 and c.ActivityId=(select top 1 KpiActivityId from Activity where id=126)
报名导出到门店
select a.OrderNo 订单号,a.Title 产品名称,a.Price 费用,a.Status 支付成功,b.Phone 电话,b.NickName 微信名,b.RealName 姓名,
(select top 1 NickName from Accounts where WeixinUnionId=b.FromOpenId and ActivityId=b.ActivityId) 推荐人,
(select top 1 Phone from Accounts where WeixinUnionId = b.FromOpenId and ActivityId = b.ActivityId) 推荐人电话,
CONVERT(nvarchar(20), a.AddTime, 120) 时间,
(select top 1 NickName from Accounts where WeixinUnionId=a.ShareUnion and ActivityId=b.ActivityId) 分享者,c.StoreName 店铺, c.BrandName 品牌,c.Name 员工名
from orders a
left join Accounts b on a.AccountsId = b.Id
left join KPI.dbo.UserInActivityView c on b.FromOpenId=c.UnionId
where a.Status = '支付成功' and a.ProductId <> 0 and a.ActivityId=121
and c.ActivityId=(select top 1 KpiActivityId from Activity where id=121)
--select * from Orders where ActivityId=58 and Status = '支付成功' and ProductId=0
select a.OrderNo 订单号,a.Price 费用,a.Status 支付成功,b.Phone 电话,b.NickName 微信名,b.RealName 姓名,(select top 1 NickName from Accounts where WeixinUnionId=b.FromOpenId and ActivityId=b.ActivityId) 推荐人,
(select top 1 Phone from Accounts where WeixinUnionId = b.FromOpenId and ActivityId = b.ActivityId) 推荐人电话,CONVERT(nvarchar(20), a.AddTime, 120) 时间,
(select top 1 NickName from Accounts where WeixinUnionId=a.ShareUnion and ActivityId=b.ActivityId) 分享者 ,c.StoreName 店铺, c.BrandName 品牌,c.Name 员工名
from orders a
left join Accounts b on a.AccountsId = b.Id
left join KPI.dbo.UserInActivityView c on b.FromOpenId=c.UnionId
where a.Status = '支付成功' and a.ProductId = 0 and a.ActivityId=121
and c.ActivityId=(select top 1 KpiActivityId from Activity where id=121)
select a.NickName 微信名,a.RealName 姓名,a.Phone 电话
,
(select top 1 NickName from Accounts where WeixinUnionId=a.FromOpenId and ActivityId=a.ActivityId) 推荐人,
(select top 1 Phone from Accounts where WeixinUnionId=a.FromOpenId and ActivityId=a.ActivityId) 推荐人电话,CONVERT(nvarchar(20),a.AddTime,120) 时间
,c.StoreName 店铺, c.BrandName 品牌,c.Name 员工名
from Accounts a
left join KPI.dbo.UserInActivityView c on a.FromOpenId=c.UnionId
where a.Phone<>'' and a.IsYuanGong=1 and a.ActivityId=85
and c.ActivityId=(select top 1 KpiActivityId from Activity where id=85)
积攒到门店
select b.StoreName 店铺, b.BrandName 品牌,b.Name 员工名,* from Accounts a left join KPI.dbo.UserInActivityView b on a.FromOpenId=b.UnionId where a.id in ( select AccountsId from JZLogs where ActivityId=109 group by ActivityId, AccountsId having COUNT(AccountsId)>10 )
活动数据清理
delete Accounts where ActivityId in (93,94,95,96,97,98,99,100,101,102,103,107,109,110,111,112,113,114,115,116,117,118,119) delete ActivityBargainirg where ActivityId in (93,94,95,96,97,98,99,100,101,102,103,107,109,110,111,112,113,114,115,116,117,118,119) delete ActivityBargainList where ActivityId in (93,94,95,96,97,98,99,100,101,102,103,107,109,110,111,112,113,114,115,116,117,118,119) delete JZLogs where ActivityId in (93,94,95,96,97,98,99,100,101,102,103,107,109,110,111,112,113,114,115,116,117,118,119) delete KpiErrorLog where ActivityId in (93,94,95,96,97,98,99,100,101,102,103,107,109,110,111,112,113,114,115,116,117,118,119) delete Orders where ActivityId in (93,94,95,96,97,98,99,100,101,102,103,107,109,110,111,112,113,114,115,116,117,118,119) delete PointsLogs where ActivityId in (93,94,95,96,97,98,99,100,101,102,103,107,109,110,111,112,113,114,115,116,117,118,119) delete ShareAdLog where ActivityId in (93,94,95,96,97,98,99,100,101,102,103,107,109,110,111,112,113,114,115,116,117,118,119) delete UserCoupon where ActivityId in (93,94,95,96,97,98,99,100,101,102,103,107,109,110,111,112,113,114,115,116,117,118,119) delete UserLocationInfo where ActivityId in (93,94,95,96,97,98,99,100,101,102,103,107,109,110,111,112,113,114,115,116,117,118,119) delete ViewLogs where ActivityId in (93,94,95,96,97,98,99,100,101,102,103,107,109,110,111,112,113,114,115,116,117,118,119)
