sql 整理_convert(varchar(100), workoverapplystarttime, 111)-优快云博客

本文链接：https://blog.youkuaiyun.com/Android_Mrchen/article/details/79864052
 导入：  2 非目标 1目标号码   NEWID() 创建uuid

--添加黑名单  2 是黑名单 
insert into tb_config_number_info(id,privilege_id,custom_mobile,roster_type,config_time)
select NEWID(),'privilege_id',mobile,'2',GETDATE() from tempmobile    

--删除非目标号码或目标号码 
delete from tb_config_number_info where privilege_id='privilege_id'

truncate table tempmobile  清空表
TB_PRIVILEGE  优惠表

TB_SCHEME  方案表

tb_config_number_info 目标号码表

tb_activity_info  活动表

tb_activity_my_prize_info 奖品记录

tb_activity_lottery_log  办理时间查看
tb_activity_mobile       活动的目标号码群
tb_prize_mobile          奖品的目标号码群

tb_account_info   抽奖记录用的此表

tb_activity_prize_info   奖品信息表
TB_STARTPAGE_INFO       宣传图

tb_activity_newcomer_sign_record  新人签到表 

在互斥号码当中剔除
delete  FROM   tb_config_number_info WHERE privilege_id='privilege_id' and custom_mobile IN(
'13923683136',
'13923683360',
'13923686018',
'13923688110',
'13923683781',
'13923689768',
'13923683837',
'13923683199',
'13923683173',
'18807629667',
'13923683909',
'13923682305',
'13923686823',
'18898554821',
'13829390927',
'13650697614'
)

TB_STARTPAGE_INFO  广告表

流量会员日办理情况
select tc.scheme_name,ol.mobile,ol.trans_state,CONVERT(varchar(100), ol.create_time, 120),ol.transact_detail from TB_HY_ORDER_LOG ol,TB_SCHEME tc,TB_PRIVILEGE tp where tp.id =tc.privilege_Id and ol.plan_id=tc.id and tp.id ='privilege_id' order by ol.create_time desc


查询某个优惠的目标号码   
SELECT * FROM tb_config_number_info WHERE PRIVILEGE_id='privilege_id'


删除某个优惠的目标号码
DELETE FROM tb_config_number_info   WHERE PRIVILEGE_id='privilege_id'


上行表数据查询
select * from tb_sms_upload_message u2 where 1=1
and u2.MOBILE_NO = '13829380947';

删除某个优惠的某个号码
DELETE from tb_config_number_info  where  custom_mobile ='13829390927' and PRIVILEGE_id='privilege_id'
查询某个活动某个号码
select * from tb_config_number_info  where  custom_mobile ='13829390927' and PRIVILEGE_id='privilege_id'


各个活动办理情况
--select tp.privilege_name,tc.scheme_name,ol.mobile,CONVERT(varchar(100), ol.create_time, 120),ol.transact_detail from TB_HY_ORDER_LOG ol,TB_SCHEME tc,TB_PRIVILEGE tp 
--WHERE  ol.plan_id=tc.id and tc.privilege_Id=tp.id and tp.id ='6276f1df-5b53-4ca5-a1b3-0e927ba4d633' ORDER BY ol.create_time desc
--select tp.privilege_name,tc.scheme_name,ol.mobile,CONVERT(varchar(100), ol.create_time, 120),ol.transact_detail from TB_HY_ORDER_LOG ol,TB_SCHEME tc,TB_PRIVILEGE tp 
--WHERE  ol.plan_id=tc.id and tc.privilege_Id=tp.id and tp.id ='22bc93ef-7c77-4181-913e-ab92ae3b59a0' ORDER BY ol.create_time desc
select tp.privilege_name,tc.scheme_name,ol.mobile,CONVERT(varchar(100), ol.create_time, 120),ol.transact_detail from TB_HY_ORDER_LOG ol,TB_SCHEME tc,TB_PRIVILEGE tp 
WHERE  ol.plan_id=tc.id and tc.privilege_Id=tp.id and tp.id ='46e588ad-f4f5-4dac-bcb8-39e3b4b0605b' ORDER BY ol.create_time desc


删除某些号码的办理信息
delete from tb_activity_lottery_log where account in ('13650697614') and activity_id='65122fd7-e3a7-444f-ab00-cbbb934c79f6'
GO
delete from  tb_activity_my_prize_info where mobile in ('13650697614') and activity_id='65122fd7-e3a7-444f-ab00-cbbb934c79f6'




查询出放弃领取奖品数量 
select COUNT(0) from tb_activity_my_prize_info WHERE  is_giveup='1'



确认领取奖品   0是领取  1是放弃
select COUNT(0) from tb_activity_my_prize_info WHERE  is_giveup='0' and prize_code IN ('AQY','PPTV','YK')

视频错误情况查询

select  t.mobile   from (select mobile,count(1) num from tb_activity_my_prize_info WHERE  prize_code in('AQY','PPTV','YK')  group by mobile,prize_code) t where t.num>1


select mobile,count(1) num from tb_activity_my_prize_info WHERE  prize_code in('AQY','PPTV','YK')  group by mobile,prize_code having count(1)>1 
ORDER BY mobile,prize_code



删除重复错误数据
delete from tb_activity_my_prize_info where id in (
select id from tb_activity_my_prize_info mp,
(select mobile,prize_code  from tb_activity_my_prize_info where prize_code in('PPTV','AQY','YK') group by mobile,prize_code having count(*)>1) t 
where mp.mobile=t.mobile and mp.prize_code=t.prize_code)


insert into tb_activity_my_prize_info_copy 
select NEWID(),mobile,mobile,max(activity_id),NULL,MIN(create_time),max(is_handle),max(prize_deadline),prize_code,max(is_giveup)  from tb_activity_my_prize_info where prize_code in('PPTV','AQY','YK') group by mobile,prize_code having count(*)>1



-----
修改某个字段某一部分内容
update tb_activity_lottery_log set remark = REPLACE(remark ,SUBSTRING(remark,0,17),'')

remark 字段名   SUBSTRING(remark,0,17) 替换部分      
=====================================================================
截止某日视频包抽奖数据
SELECT account 电话,remark 备注,CONVERT(varchar(100),create_time, 111) 时间  from  tb_activity_lottery_log WHERE prize_code in('AQY','YK','PPTV') AND  create_time <CONVERT(varchar(100),'2018-02-06', 1) order BY  create_time ASC 



==================================================================
查询字段为空的数据
SELECT * from  tb_activity_lottery_log WHERE account is null

update set
=================================================================== 
 优酷：016822f9-4182-4525-a49e-38a8d7cd82bd
 爱奇艺：d1777d89-0832-468b-be50-9f9edf46871f
 pptv：5798c529-310b-4657-bb17-e904f2c294f7
导入奖品目标号码 
insert into tb_prize_mobile(id,prize_id,mobile,roster_type,create_time)
select NEWID(),'016822f9-4182-4525-a49e-38a8d7cd82bd',mobile,'1',GETDATE() from tempmobile


http://10086.hygmcc.net/hyyd/h5/activity2/ActivityAction!goLottery.action

==================================================================
 排序 order by
=================================================================
-- 查询死锁
select    
    request_session_id spid,   
    OBJECT_NAME(resource_associated_entity_id) tableName    
from    
    sys.dm_tran_locks   
where    
    resource_type='OBJECT' 
 --杀死死锁进程
kill 354 
===============================================================
插入奖品组表
--insert into tb_activity_prize_group(id,group_name,winning_cycle,group_type,must_win,activity_id)
--VALUES(NEWID(),'自选领取奖项(六月中一次)','6','3','1','be7dc72f-2870-42e5-be81-50eb2edbe46e')
===============================================================

添加商品
insert into tb_activity_prize_info(id,prize_name,prize_location,start_time,end_time,priority,chance,prize_desc,amount,creator,create_time,activity_id,remain_amount,type,PRIZE_CODE,scheme_id,transaction_hint,handle_day,prize_hint,img_url,group_id)
VALUES(NEWID(),'咪咕视频','220',GETDATE(),'2018-03-31 23:59:59.000','','1','','10000','admin',GETDATE(),'be7dc72f-2870-42e5-be81-50eb2edbe46e','10000','4','MG','','恭喜你获得15元咪咕视频会员优惠。','','感谢您参与本次抽奖活动，即日起赠送给您6个月咪咕视频会员（每月扣15元，同时赠送15元话费），优惠到期后自动续订（资费15元/月），如无需继续使用发0000到10086取消','','DB38D9F5-6D81-4ECA-AB4D-465E22AD0505')

=======================================================================

  优惠办理日志
select * FROM TB_HY_ORDER_INFO WHERE mobile ='13650697614' ORDER BY create_time DESC   1 办理成功   0办理失败


某日到某日
and ol.create_time >CONVERT(varchar(100),'2018-02-01', 1) and ol.create_time <=CONVERT(varchar(100),'2018-03-01', 1)


===============================================================
查询目标号码 

  select * from TB_PRIVILEGE tp where tp.is_putaway='1' 
 and (GETDATE()<tp.privilege_end_time or tp.privilege_end_time is null) 
 and (not exists (select id from TB_CONFIG_NUMBER_INFO where privilege_id=tp.id) 
 or exists (select id from TB_CONFIG_NUMBER_INFO where privilege_id=tp.id and custom_mobile='18813260403' and roster_type='1') 
 or exists (select id from TB_CONFIG_NUMBER_INFO where privilege_id=tp.id and roster_type='2' and not exists(select id from TB_CONFIG_NUMBER_INFO where privilege_id=tp.id and custom_mobile='18813260403' and roster_type='2'))) 
 order by privilege_sort asc 


  select * from TB_PRIVILEGE tp where tp.is_putaway='1' 
 and (GETDATE()<tp.privilege_end_time or tp.privilege_end_time is null) 
 and id in (
select id from TB_PRIVILEGE tp where not exists (select id from TB_CONFIG_NUMBER_INFO where privilege_id=tp.id)
UNION ALL 
select id from TB_PRIVILEGE tp where exists (select id from TB_CONFIG_NUMBER_INFO where privilege_id=tp.id and custom_mobile='18813260403' and roster_type='1')
UNION ALL 
select id from TB_PRIVILEGE tp where exists (select id from TB_CONFIG_NUMBER_INFO where privilege_id=tp.id and roster_type='2' and not exists(select id from TB_CONFIG_NUMBER_INFO where privilege_id=tp.id and custom_mobile='18813260403' and roster_type='2')) 
)

=============================================================
某个时间段
select remark,COUNT(*) from tb_activity_lottery_log WHERE create_time >CONVERT(varchar(100),'2018-02-01', 111) and create_time <=CONVERT(varchar(100),'2018-03-01', 1) and remark LIKE '%红包%' GROUP BY remark
==========================================================
浏览次数查询
select name  ,view_number  from TB_STARTPAGE_INFO  WHERE STATUS='1'

SELECT activity_name ,view_number ,ad_number from  tb_activity_info where status='1'

SELECT privilege_name ,view_number  from TB_PRIVILEGE where is_putaway='1'


当月累计登陆量    distinct 去重电话  
SELECT  COUNT(distinct mobile)     FROM tb_login_log WHERE  create_time>CONVERT(varchar(100),'2018-03-01', 1)


========================================================
每天抽奖人数
select CONVERT(varchar(100),create_time, 111),COUNT(*) from tb_activity_lottery_log  GROUP by CONVERT(varchar(100),create_time, 111)  ORDER BY  CONVERT(varchar(100),create_time, 111) ASC

=========================================================
  select * FROM tb_activity_lottery_log WHERE remark='咪咕视频会员（体验半年）(办理中)'

不等于 <>


=========================================================

查询新人签到出错情况

select * from tb_login_log WHERE  create_time<CONVERT(varchar(100),'2018-04-01', 1) and mobile in(SELECT mobile from tb_activity_newcomer_sign_record)

=========================================================
查询ecop


select * from hyecop.tb_ecop_interface_log where create_time > sysdate-1
and request_xml like '%15220954883%'
and interface_name ='ccqrysubselectprods' order by create_time desc




=======================================================
  时间大于某秒
  create_time>'2018-04-23 22:53:11'
  


======================================================
自增序列
SELECT
    @rownum :=@rownum + 1 AS rownum,
    TEST_Q_SNAP_ID
FROM
    test_question,
    (SELECT @rownum := 0) r
GROUP BY
    TEST_Q_SNAP_ID