sqlserver-sql 个人写过最长sql记录 192行

于 2022-08-23 11:00:54 发布
阅读量354 收藏
点赞数
分类专栏： mysql 文章标签： sql sqlserver 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45774059/article/details/126480559
版权 
#获取当前日期前面30天的日期

select convert(varchar(10),dateadd(dd,number,dateadd(month,-1,getdate())),120) as cr_date
	from master..spt_values 
	where type='P' and 
	dateadd(dd,number,dateadd(month,-1,getdate()))<=getdate()





# 1， Total_call 最近30天每日创建工单数量 【已完成】
declare @kehu VARCHAR(20)
set @kehu ='WTC';


select 
	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date, 
	COUNT(TICKET_ID) Total_call
from 
	VAPP_ITEM
where 
	datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30
	and person1_root_org_name = @kehu
group by 
	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120);
 

 
#2，  Uniclosed 最近30天状态为开着的未关单数量，已创建时间排序 【已完成】
declare @kehu VARCHAR(20)
set @kehu ='WTC';

select 
	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date, 
	COUNT(TICKET_ID) Uniclosed
from 
	VAPP_ITEM
where 
	datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30 and TICKET_STATUS IN ('Active','New','Queued') and person1_root_org_name = @kehu
group by 
	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120);


#3， Scheduled  最近30天每天应到期工单数量且  工单状态为未关 （按照到期时间排序）【已完成】
declare @kehu VARCHAR(20)
set @kehu ='WTC';

SELECT
	a.cr_date,
	COUNT ( TICKET_ID ) Scheduled 
from
		(Select CONVERT (VARCHAR ( 10 ),
		DATEADD(SECOND, (select top 1 sla_due_by from VSLA_AGREEMENT_COMPLIANCE_LIST_UX as vc where vc.item_id=vi.ROW_ID order by threshold_sort_order desc), 	'1970/1/1 08:00:00') ,120) as 'cr_date',
	vi.TICKET_ID
	from VAPP_ITEM as vi where  person1_root_org_name = @kehu and TICKET_STATUS not in ('closed') and datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,vi.CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30) as a
GROUP BY 
	a.cr_date
 


#4， P1_call 最近30天工单优先级最高的数量（按照创建时间排序）【已完成】

declare @kehu VARCHAR(20)
set @kehu ='WTC';


select 
	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
	count(TICKET_ID) P1_call
from
	VAPP_ITEM
where 
	datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30 and Ticket_priority='P2'
	and person1_root_org_name = @kehu
GROUP BY 
	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)



#5 ，Over_SLA 最近30天工单状态已超时工单数量 按照创建时间排序 【已完成】
declare @kehu VARCHAR(20)
set @kehu ='WTC';


select 
	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
	count(TICKET_ID) Over_SLA
from
	VAPP_ITEM
where 
	sla_compliance_status_indicator='Breached SLA' and datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30 and person1_root_org_name = @kehu
GROUP by 
	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)


#6，SLA_Met 最近30天达成率 按照创建时间排序 公式（totalcall - uniclosed - oversla）/(totalcall - uniclosed ) 【已完成】

#每日sla达成数量
declare @kehu VARCHAR(20)
set @kehu ='WTC';

select 
	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date, 
	COUNT(TICKET_ID) SLA_Met
from 
	VAPP_ITEM
where 
	datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30
 AND TICKET_STATUS NOT IN ('Active','New','Queued') AND sla_compliance_status_indicator NOT IN ('Breached SLA')
 and person1_root_org_name = @kehu
group by 
	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120);
 
#sla达成率的分母
declare @kehu VARCHAR(20)
set @kehu ='WTC';

select 
	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date, 
	COUNT(TICKET_ID) SLA_Met
from 
	VAPP_ITEM
where 
	datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30
 and TICKET_STATUS NOT IN ('Active','New','Queued')
 and person1_root_org_name = @kehu
group by 
	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120);
 
#SLA_Met sla达成率 
declare @kehu VARCHAR(20)
set @kehu ='WTC';

select 
	a.cr_date,
	(a.xjsl/b.gdsl) SLA_Met
from
		(select 
		CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date, 
		COUNT(TICKET_ID) xjsl
	from 
		VAPP_ITEM
	where 
		datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30
	 AND TICKET_STATUS NOT IN ('Active','New','Queued') AND sla_compliance_status_indicator NOT IN ('Breached SLA')
	 and person1_root_org_name = @kehu
	group by 
		CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)) as a 
	 join 
	(select 
		CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date, 
		COUNT(TICKET_ID) gdsl
	from 
		VAPP_ITEM
	where 
		datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30
	 and TICKET_STATUS NOT IN ('Active','New','Queued')
	 and person1_root_org_name = @kehu
	group by 
		CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)) as b 
	 on a.cr_date=b.cr_date;





#7，Worst_TAT 最近30天每天工单处理花费最大时间 【已完成】
#原理 1，以创建时间排序 2，关单时间- 创建时间 3，工单状态为关闭 4，当天最大值
declare @kehu VARCHAR(20)
set @kehu ='WTC';



select 
	b.cr_date,
	max(b.zd) Worst_TAT
from 
	(select CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
	convert(varchar(10),(DATEADD(S,CLOSED_DATE,'1970/1/1 08:00:00') -DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),108) zd
	from
	VAPP_ITEM
	where TICKET_STATUS='Closed' and datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30
	and person1_root_org_name = @kehu
	) b
group by 
	b.cr_date;



#8，Avg_onsite_time 最近30天平均上门时间  只计算有第一次上门时间的工单 【已完成】
#单位是小时
declare @kehu VARCHAR(20)
set @kehu ='WTC';



select t.cr_date,avg(t.avg_onsitetime) Avg_onsite_time 
from
(
select 
	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
	DATEDIFF(hh,DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00'),(SELECT TOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00') FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=553)) avg_onsitetime
from
	VAPP_ITEM as vi
where 
	(SELECT TOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00') FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=553) is not null 
	and datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30
	and person1_root_org_name = @kehu
) as t
group by t.cr_date;



#9， No_onsite_time 最近30天没有上门时间的工单数量且ccti=hw 按照创建时间排序 【已完成】
declare @kehu VARCHAR(20)
set @kehu ='WTC';



select 
	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
	COUNT(TICKET_ID) No_onsite_time
from
	VAPP_ITEM as vi
where 
	(SELECT TOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00') FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=553) is null 
	and CCTI_CLASS='HW' and datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30
	and person1_root_org_name = @kehu
GROUP BY 
	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120);

#10，Onsite_1 最近30天 重复上门工单数量 统计有第二次和第三次上门时间的工单 按照创建时间分组 【已完成】
declare @kehu VARCHAR(20)
set @kehu ='WTC';



select 
	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
	COUNT(TICKET_ID) Onsite_1
from
	VAPP_ITEM as vi
where 
	(SELECT TOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00') FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=row_id and va.ATTR_ID=555) is not null and (SELECT TOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00') FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=row_id and va.ATTR_ID=558) is not null and datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30
	and person1_root_org_name = @kehu
GROUP BY 
	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120);





---------------------------------------------------------------------------------------------------------


#综合报表
	t1.cr_date 日期,
	
declare @kehu VARCHAR(20)
set @kehu ='WTC';

select 
	t2.Total_call,
	t3.Uniclosed,
	t4.Scheduled,
	t5.P1_call,
	t6.Over_SLA,
	datename(day,t1.cr_date) 日期,
	t7.SLA_Met,
	t8.Worst_TAT,
	t9.Avg_onsite_time,
	t10.No_onsite_time,
	t11.Onsite_1
from
	(select convert(varchar(10),dateadd(dd,number,dateadd(month,-1,getdate())),120) as cr_date
	from master..spt_values 
	where type='P' and 
	dateadd(dd,number,dateadd(month,-1,getdate()))<=getdate()) 
	as t1 
left join 
	(select 
		CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date, 
		COUNT(TICKET_ID) Total_call
	from 
		VAPP_ITEM
	where 
		datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30
		and person1_root_org_name = @kehu
	group by 
		CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)
	)
	as t2 
	on t1.cr_date=t2.cr_date
left join 
	(
	select 
		CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date, 
		COUNT(TICKET_ID) Uniclosed
	from 
		VAPP_ITEM
	where 
		datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30 and TICKET_STATUS IN ('Active','New','Queued') and person1_root_org_name = @kehu
	group by 
		CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)
	)
	as t3 
	on t1.cr_date=t3.cr_date 
left join 
	(
	SELECT
		a.cr_date,
		COUNT ( TICKET_ID ) Scheduled
	from
			(Select CONVERT (VARCHAR ( 10 ),
			DATEADD(SECOND, (select top 1 sla_due_by from VSLA_AGREEMENT_COMPLIANCE_LIST_UX as vc where vc.item_id=vi.ROW_ID order by threshold_sort_order desc), 	'1970/1/1 08:00:00') ,120) as 'cr_date',
		vi.TICKET_ID
		from VAPP_ITEM as vi where  person1_root_org_name = @kehu and TICKET_STATUS not in ('closed') and datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,vi.CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30) as a
	GROUP BY 
		a.cr_date
	)
	as t4  
	on t1.cr_date=t4.cr_date
left join 
	(select 
	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
	count(TICKET_ID) P1_call
from
	VAPP_ITEM
where 
	datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30 and Ticket_priority='P2'
	and person1_root_org_name = @kehu
GROUP BY 
	CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)
	)
	as t5
	on t1.cr_date=t5.cr_date
left join
	(
	select 
		CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
		count(TICKET_ID) Over_SLA
	from
		VAPP_ITEM
	where 
		sla_compliance_status_indicator='Breached SLA' and datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30 and person1_root_org_name = @kehu
	GROUP by 
		CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)
	)
 as t6
 on t1.cr_date=t6.cr_date
left join
		(
		select 
		a.cr_date,
		(a.xjsl/b.gdsl) SLA_Met
	from
			(select 
			CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date, 
			COUNT(TICKET_ID) xjsl
		from 
			VAPP_ITEM
		where 
			datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30
		 AND TICKET_STATUS NOT IN ('Active','New','Queued') AND sla_compliance_status_indicator NOT IN ('Breached SLA')
		 and person1_root_org_name = @kehu
		group by 
			CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)) as a 
		 join 
		(select 
			CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date, 
			COUNT(TICKET_ID) gdsl
		from 
			VAPP_ITEM
		where 
			datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30
		 and TICKET_STATUS NOT IN ('Active','New','Queued')
		 and person1_root_org_name = @kehu
		group by 
			CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)) as b 
		 on a.cr_date=b.cr_date
		)
	as t7
	on t1.cr_date=t7.cr_date
left join
		(
		select 
		b.cr_date,
		max(b.zd) Worst_TAT
	from 
		(select CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
		convert(varchar(10),(DATEADD(S,CLOSED_DATE,'1970/1/1 08:00:00') -DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),108) zd
		from
		VAPP_ITEM
		where TICKET_STATUS='Closed' and datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30
		and person1_root_org_name = @kehu
		) b
	group by 
		b.cr_date
		)
	as t8
	on t1.cr_date=t8.cr_date
left join
			(
			select t.cr_date,avg(t.avg_onsitetime) Avg_onsite_time
		from
		(
		select 
			CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
			DATEDIFF(hh,DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00'),(SELECT TOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00') FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=553)) avg_onsitetime
		from
			VAPP_ITEM as vi
		where 
			(SELECT TOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00') FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=553) is not null 
			and datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30
			and person1_root_org_name = @kehu
		) as t
		group by t.cr_date
			)
	as t9
	on t1.cr_date=t9.cr_date
left join 
		(
		select 
			CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
			COUNT(TICKET_ID) No_onsite_time
		from
			VAPP_ITEM as vi
		where 
			(SELECT TOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00') FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.		ROW_ID and va.ATTR_ID=553) is null 
			and CCTI_CLASS='HW' and datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30
			and person1_root_org_name = @kehu
		GROUP BY 
			CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)
		)
	as t10
	on t1.cr_date=t10.cr_date
left join
		(
		select 
			CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
			COUNT(TICKET_ID) Onsite_1
		from
			VAPP_ITEM as vi
		where 
			(SELECT TOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00') FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=row_id and va.ATTR_ID=555) is not null and (SELECT TOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00') FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=row_id and va.ATTR_ID=558) is not null and datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30
			and person1_root_org_name = @kehu
		GROUP BY 
			CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)
		)
	as t11
	on t1.cr_date=t11.cr_date;

玄学调参侠
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
数据库备份 SQLServer的备份和灾难恢复
09-11
SQL Server 提供了多种备份和恢复策略来满足不同业务需求。 首先，了解SQL Server中的主要备份类型： 1. 完全备份（Full Backup）：这是最基础的备份类型，包含数据库的所有数据和信息。执行一次全备份后，可以...
SQLServer 性能仪表盘（Performance Dashboard Reports）
03-06
SQLServer 性能仪表盘（Performance Dashboard Reports）是SQL Server管理工具中的一种强大特性，它为数据库管理员提供了直观且详尽的性能监控解决方案。通过这些报告，DBAs能够快速识别和解决性能瓶颈，优化数据库...
记录一下，写的最长的sql
qingfeng2556的博客
04-16 1590
 SELECT  * from (        SELECT        tp.id,        tp.status,        tp.number,        tu.user_id,        tu.login_name,        tub.real_name,        tu.phone,        tp.resale_price_ask,        tp....
有史以来写过最长的sql
qq764136933的专栏
10-16 946
SELECT -- 单据类型 biz_so_out.biz_type, -- 单据日期 biz_so_out.biz_date, -- 单据号 biz_so_out.bill_no, -- 零售单据号 biz_so.bill_no as biz_so_bill_no, -- 品牌 biz_so_detail_01.brand_uuid, -- 商品名称 biz_so_de...
我迄今为止写的最长的SQL
u010815305的专栏
05-03 708
   今天看我14年实习的时候，当时我的导师在博客中写到了一段差不多50多行的sql，说是他写的最长的sql， 想想工作的这一年，其实我也写了不少sql，因为自身是做数据开发方面的工作，写sql是免不了的，哈哈~ 今天刚好是五一假后上班的第一天，也不想干什么事，就写写博客吧，好久没有记录啦。好，言归正传， 就说sql吧， 去年我们数据组要把之前的从mysql中的提取基础数据的工作给...
迄今为止，我写过的最长的SQL语句
Zzz's Blog
12-29 1529
SELECT   wap_user.u_name, NVL (f_pv, 0), NVL (m_pv, 0), NVL (f_se, 0),          NVL (m_se, 0)     FROM (SELECT *             FROM (SELECT   user_id AS fuser1, COUNT (1) AS f_pv
纪念一下写过的最长SQL
最新发布
ChenX9821的博客
07-27 110
SQL撰写
在SQLServer上查看SQL语句的执行时间的方法
09-11
在SQL Server中，监控SQL语句的执行时间是优化数据库性能和提高代码效率的重要环节。以下是如何在SQL Server上查看SQL语句执行时间的几种方法： 1. **简单时间差计算法**： 这是最基础的方法，通过记录查询开始和...
教你如何下载、安装、使用SQL Server
12-15
SQL Server是一款由微软公司开发的关系型数据库管理系统，广泛应用于企业级数据存储和管理。本文将指导你如何下载、安装和使用SQL Server，以及如何进行基本的数据库和表的操作。 **一、下载与安装SQL Server** 1....
SQL Server中的SELECT会阻塞SELECT吗
09-09
在SQL Server中，通常情况下，一个`SELECT`语句不会阻塞另一个`SELECT`语句。这是因为`SELECT`操作通常只请求意向共享锁(IS)或共享锁(S)，这两种锁模式是兼容的，允许并发的读取操作。然而，特定场景下可能会出现...
哥见过最长的sql语句
发哥
10-18 445
select        accusation0_.accusation_id as accusation1_2_12_,        accusation0_.bId as bId2_12_,        accusation0_.rId as rId2_12_,        accusation0_.reason as reason2_12_,        accusation0_....
全世界最长的统计SQL语句，可拆分使用
暴风城基地
06-09 359
全世界最长的统计SQL语句，可拆分使用
sql中字段入库超长
ZhaoHaiTang的博客
02-19 576
Caused by: java.sql.SQLException: ORA-12899: 列 "WEB_DB"."WALLET_DETAILS"."RETMSG" 的值太大 (实际值: 233, 最大值: 150) 我自己写了一个工具类，精确截取字段的长度，防止入库时字段超长。 /** * Created by Administrator on 20-2-19. */ publ...
我到目前为止写的最长的一个SQL语句
勤奋的asialee的博客
03-31 210
[size=large]这个是我昨天写的一个SQL语句，是mysql的，本身想用程序来写的，最后还是用sql写出来了，小有自豪感。但不知道效率如何，还的请高人指点。[/size] [code="sql"] select na.source_node_id as project_id, p.pname, (select pc.cname ...
写过最长的一次sql
喜哥的点滴积累
08-09 1195
<br />SELECT (NVL(SJTOTAL, 0) - NVL(TOULIAN, 0) - NVL(WANNENG, 0)) * 0.1 +<br />       (NVL(TOULIAN, 0)) + (NVL(WANNENG, 0) * 0.8) AS KTZ<br />  FROM (SELECT (SELECT (CASE<br />                         WHEN SEASON = 0 THEN<br />                          CA
见过史上最长的SQL语句（惊）
lexiyao的专栏
05-05 4095
这是同事写的一个进销存的SQL语句，一条语句这么长，大吓人了（数据少的话速度还很快，佩服）。  SELECT Id, GName, Model1, Model2, unit, AverageCost, (Select sum(ActualQty) from StockBill inner join StockBillItems on StockBill.Id=StockBillItem
个人目前最长sql语句（美化之后）
m0_37997811的博客
04-15 1626
SELECT a.id AS id, a.departmentId AS departmentId, a.rank AS rank, ( SELECT SUM(d.studyTime) FROM tbl_User_Learn_Record AS d WHERE d.userId = a.userId AND d.startTime >= : startTime AND d.startTime...
sql 语句查询今天、昨天、近7天、近30天、一个月内、上一月 数据（本周本月本年第一天及最后一天）（MySQL 与 SQL SERVER）
热门推荐
zgphacker2010的专栏
08-05 2万+
sql 语句查询今天、昨天、近7天、近30天、一个月内、上一月 数据 MySQL 查询当天的所有数据 SELECT * FROM 表名 WHERE DATEDIFF(字段,NOW()) = 0 SELECT * FROM 表名 WHERE to_days(时间字段名) = to_days(now()); 查询昨天的所有数据 SELECT * FROM 表名 WHERE DATEDIFF(字段,NOW())=-1 SELECT * FROM 表名 WHERE TO_DAYS(NOW()) -
SqlServer SQL总结.pdf
12-10
本文档是一份关于SQL Server数据库中SQL语言的详细总结，涵盖了数据类型的基础知识。主要内容分为五个部分： 1. 整数型：包括bigint（大整数）、int、smallint、tinyint和bit。这些数据类型用于存储整数值，按照...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值