[MSSQL]关于SQL累积计算问题

最新推荐文章于 2022-09-14 22:24:11 发布
最新推荐文章于 2022-09-14 22:24:11 发布
阅读量501 收藏
点赞数
分类专栏： MSSQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/david_520042/article/details/104754525
版权

通常计算制程直通率的时，需要用到累乘积计算功能，在EXCEL表中比较容易，在SQL中如何使用呢？
当前直通率=上一制程直通率*本制程直通率。
在这里插入图片描述
(1)循环遍历,游标(采用游标代码量比较多，而且效率低)
(2)SQL执行(需要08R2以上，我司目前是14版)EXP(sum(log([A])) over(order by XX ASC))

USE [OA]
GO
/****** Object:  StoredProcedure [dbo].[DZ_ZRInspectionItemsDailyRpt]    Script Date: 2020/3/9 9:09:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================      
-- Author: <David Gong>      
-- Create date: <2020-03-06>      
-- Description: <动态生成检验报表>      
-- ============================================= 
ALTER Procedure [dbo].[DZ_ZRInspectionItemsDailyRpt]
(
	@ProductSeries AS NVARCHAR(20) ,
	@YearMonth AS CHAR(6)
)
AS
 BEGIN

IF OBJECT_ID('tempdb..#T1') IS NOT NULL
    BEGIN
        DROP TABLE #T1
    END
IF OBJECT_ID('tempdb..#T2') IS NOT NULL
    BEGIN
        DROP TABLE #T2
    END

IF OBJECT_ID('tempdb..#T3') IS NOT NULL
    BEGIN
        DROP TABLE #T3
    END
IF OBJECT_ID('tempdb..#T4') IS NOT NULL
    BEGIN
        DROP TABLE #T4
    END
IF OBJECT_ID('tempdb..#T5') IS NOT NULL
    BEGIN
        DROP TABLE #T5
    END

IF OBJECT_ID('tempdb..#T6') IS NOT NULL
    BEGIN
        DROP TABLE #T6
    END

IF OBJECT_ID('tempdb..#T7') IS NOT NULL
    BEGIN
        DROP TABLE #T7
    END

IF OBJECT_ID('tempdb..#ZT') IS NOT NULL
    BEGIN
        DROP TABLE #ZT
    END
------产品品品收集---------------
CREATE TABLE #T1
(
    RouteCode  CHAR(20),
	RouteName  CHAR(20),
	BadItem   CHAR(20),
	[01]  CHAR(100),
	[02]  CHAR(100),
	[03]  CHAR(100),
    [04]  CHAR(100),
	[05]  CHAR(100),
	[06]  CHAR(100),
	[07]  CHAR(100),
	[08]  CHAR(100),
	[09]  CHAR(100),
	[10]  CHAR(100),
	[11]  CHAR(100),
	[12]  CHAR(100),
	[13]  CHAR(100),
	[14]  CHAR(100),
	[15]  CHAR(100),
	[16]  CHAR(100),
	[17]  CHAR(100),
	[18]  CHAR(100),
	[19]  CHAR(100),
	[20]  CHAR(100),
	[21]  CHAR(100),
	[22]  CHAR(100),
	[23]  CHAR(100),
	[24]  CHAR(100),
	[25]  CHAR(100),
	[26]  CHAR(100),
	[27]  CHAR(100),
	[28]  CHAR(100),
	[29]  CHAR(100),
	[30]  CHAR(100),
	[31]  CHAR(100)
)

------产品品品收集---------------
CREATE TABLE #T2
(
    RouteCode  CHAR(20),
	RouteName  CHAR(20),
	BadItem   CHAR(20),
	[01]  DECIMAL(18,4) DEFAULT(0),
	[02]  DECIMAL(18,4) DEFAULT(0),
	[03]  DECIMAL(18,4) DEFAULT(0),
    [04]  DECIMAL(18,4) DEFAULT(0),
	[05]  DECIMAL(18,4) DEFAULT(0),
	[06]  DECIMAL(18,4) DEFAULT(0),
	[07]  DECIMAL(18,4) DEFAULT(0),
	[08]  DECIMAL(18,4) DEFAULT(0),
	[09]  DECIMAL(18,4) DEFAULT(0),
	[10]  DECIMAL(18,4) DEFAULT(0),
	[11]  DECIMAL(18,4) DEFAULT(0),
	[12]  DECIMAL(18,4) DEFAULT(0),
	[13]  DECIMAL(18,4) DEFAULT(0),
	[14]  DECIMAL(18,4) DEFAULT(0),
	[15]  DECIMAL(18,4) DEFAULT(0),
	[16]  DECIMAL(18,4) DEFAULT(0),
	[17]  DECIMAL(18,4) DEFAULT(0),
	[18]  DECIMAL(18,4) DEFAULT(0),
	[19]  DECIMAL(18,4) DEFAULT(0),
	[20]  DECIMAL(18,4) DEFAULT(0),
	[21]  DECIMAL(18,4) DEFAULT(0),
	[22]  DECIMAL(18,4) DEFAULT(0),
	[23]  DECIMAL(18,4) DEFAULT(0),
	[24]  DECIMAL(18,4) DEFAULT(0),
	[25]  DECIMAL(18,4) DEFAULT(0),
	[26]  DECIMAL(18,4) DEFAULT(0),
	[27]  DECIMAL(18,4) DEFAULT(0),
	[28]  DECIMAL(18,4) DEFAULT(0),
	[29]  DECIMAL(18,4) DEFAULT(0),
	[30]  DECIMAL(18,4) DEFAULT(0),
	[31]  DECIMAL(18,4) DEFAULT(0)
)


DECLARE @days INT ,@everyday AS CHAR(8)
DECLARE @ItemName AS CHAR(100)
INSERT INTO #T1
        ( 
		  RouteCode ,
		  RouteName ,
          BadItem ,
          [01] ,
          [02] ,
          [03] ,
          [04] ,
          [05] ,
          [06] ,
          [07] ,
          [08] ,
          [09] ,
          [10] ,
          [11] ,
          [12] ,
          [13] ,
          [14] ,
          [15] ,
          [16] ,
          [17] ,
          [18] ,
          [19] ,
          [20] ,
          [21] ,
          [22] ,
          [23] ,
          [24] ,
          [25] ,
          [26] ,
          [27] ,
          [28] ,
          [29] ,
          [30] ,
          [31]
        )
VALUES  ( '0.工序代码' , -- RouteName - char(20)
          '0.工序名称' , -- RouteName - char(20)
          '产品品名' , -- BadItem - char(20)
          '' , -- 01 - char(100)
          '' , -- 02 - char(100)
          '' , -- 03 - char(100)
          '' , -- 04 - char(100)
          '' , -- 05 - char(100)
          '' , -- 06 - char(100)
          '' , -- 07 - char(100)
          '' , -- 08 - char(100)
          '' , -- 09 - char(100)
          '' , -- 10 - char(100)
          '' , -- 11 - char(100)
          '' , -- 12 - char(100)
          '' , -- 13 - char(100)
          '' , -- 14 - char(100)
          '' , -- 15 - char(100)
          '' , -- 16 - char(100)
          '' , -- 17 - char(100)
          '' , -- 18 - char(100)
          '' , -- 19 - char(100)
          '' , -- 20 - char(100)
          '' , -- 21 - char(100)
          '' , -- 22 - char(100)
          '' , -- 23 - char(100)
          '' , -- 24 - char(100)
          '' , -- 25 - char(100)
          '' , -- 26 - char(100)
          '' , -- 27 - char(100)
          '' , -- 28 - char(100)
          '' , -- 29 - char(100)
          '' , -- 30 - char(100)
          ''  -- 31 - char(100)
        )

SET @days=0
DECLARE @SQL CHAR(200),@2day AS CHAR(2)
--遍历当月每天
while @days<31 
BEGIN   
     SET @days=@days+1
	 SET @2day=RIGHT(@days+100,2)
	 SET @everyday=@YearMonth+@2day
	 --汇总每天产品型号 
     SELECT @ItemName=STUFF((select '、' + ItemName from R_RouteMaster where  CONVERT(CHAR(8),InspectionDate,112)=@everyday  FOR XML PATH('')),1,1,'');
	 --更新每天的产品型号
	 IF(@ItemName<>'')
	 BEGIN

	 SET @SQL='UPDATE #T1 SET ['+@2day+']='''+RTRIM(@ItemName)+''' where BadItem='+'''产品品名'''
	 --PRINT @SQL
	 EXECUTE (@SQL)	
	 END

END

--1.实际输出数量
SELECT RouteCode,Route AS RouteName,'1.实际输出数量' AS BadItem,SUM(InspectionQty) InspectionQty,SUBSTRING(CONVERT(CHAR(8),InspectionDate,112),7,2) AS eday  INTO #T3 
FROM dbo.R_RouteDetail  LEFT JOIN dbo.R_RouteMaster ON dbo.R_RouteMaster.Id = R_RouteDetail.MasterId
WHERE ProductType=@ProductSeries AND  CONVERT(CHAR(6),InspectionDate,112)=@YearMonth   AND R_RouteDetail.InspectionQty<>0
GROUP BY RouteCode,Route,SUBSTRING(CONVERT(CHAR(8),InspectionDate,112),7,2)

--2.不良数量
SELECT RouteCode,Route AS RouteName,'2.不良数量' AS BadItem,SUM(BadQty) BadQty,SUBSTRING(CONVERT(CHAR(8),InspectionDate,112),7,2) AS eday  INTO #T4 
FROM dbo.R_RouteDetail  LEFT JOIN dbo.R_RouteMaster ON dbo.R_RouteMaster.Id = R_RouteDetail.MasterId
WHERE ProductType=@ProductSeries AND  CONVERT(CHAR(6),InspectionDate,112)=@YearMonth   AND R_RouteDetail.InspectionQty<>0
GROUP BY RouteCode,Route,SUBSTRING(CONVERT(CHAR(8),InspectionDate,112),7,2)

--3.不良数量cast(cast(SUM(R_RouteDetail.BadQty)*1.0/SUM(R_RouteDetail.InspectionQty)*100 as decimal(10,2)) as varchar)+'%'  AS BadRate
SELECT RouteCode,Route AS RouteName,'3.不良率' AS BadItem,  SUM(R_RouteDetail.BadQty)*1.0000/SUM(R_RouteDetail.InspectionQty)  BadRate,SUBSTRING(CONVERT(CHAR(8),InspectionDate,112),7,2) AS eday  INTO #T5 
FROM dbo.R_RouteDetail  LEFT JOIN dbo.R_RouteMaster ON dbo.R_RouteMaster.Id = R_RouteDetail.MasterId
WHERE ProductType=@ProductSeries AND  CONVERT(CHAR(6),InspectionDate,112)=@YearMonth   AND R_RouteDetail.InspectionQty<>0
GROUP BY RouteCode,Route,SUBSTRING(CONVERT(CHAR(8),InspectionDate,112),7,2)

--4. 直通率先占位默认值
SELECT RouteCode,Route AS RouteName,'4.直通率' AS BadItem,CASE WHEN  SUM(R_RouteDetail.InspectionQty) =0 THEN 1 ELSE  (SUM(R_RouteDetail.InspectionQty)-SUM(R_RouteDetail.BadQty)*1.0000)/SUM(R_RouteDetail.InspectionQty) END AS ZTRate,
SUBSTRING(CONVERT(CHAR(8),InspectionDate,112),7,2) AS eday  INTO #T6
FROM dbo.R_RouteDetail  LEFT JOIN dbo.R_RouteMaster ON dbo.R_RouteMaster.Id = R_RouteDetail.MasterId
WHERE ProductType=@ProductSeries AND  CONVERT(CHAR(6),InspectionDate,112)=@YearMonth   AND R_RouteDetail.InspectionQty<>0
GROUP BY RouteCode,Route,SUBSTRING(CONVERT(CHAR(8),InspectionDate,112),7,2)

---不良项目按工序和日期汇总
SELECT R_RouteDetail.RouteCode,R_RouteDetail.Route,InspectionItems AS InspectionItems,SUM(R_RouteInspection.BadQty) AS BadQty ,SUBSTRING(CONVERT(CHAR(8),InspectionDate,112),7,2) AS eday  INTO #T7 
FROM dbo.R_RouteMaster LEFT JOIN dbo.R_RouteDetail ON dbo.R_RouteMaster.Id = R_RouteDetail.MasterId 
                        LEFT JOIN dbo.R_RouteInspection ON R_RouteInspection.DetailId = R_RouteDetail.Id
WHERE ProductType=@ProductSeries AND  CONVERT(CHAR(6),InspectionDate,112)=@YearMonth  AND R_RouteInspection.BadQty<>0
GROUP BY R_RouteDetail.RouteCode,R_RouteDetail.Route,SUBSTRING(CONVERT(CHAR(8),InspectionDate,112),7,2),InspectionItems 




INSERT INTO #T2 SELECT * FROM #T3 /*数据源*/
AS P
PIVOT
(
    MAX(InspectionQty) FOR
    P.eday IN ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])
) AS T
INSERT INTO #T2 
SELECT * FROM #T4 /*数据源*/
	AS P
	PIVOT
	(
	    MAX(BadQty)   FOR
	    P.eday/*需要行转列的列*/ IN ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])
	) AS T
INSERT INTO #T2
 SELECT * FROM #T5 /*数据源*/
	AS P
	PIVOT
	(
	    MAX(BadRate)   FOR
	    P.eday/*需要行转列的列*/ IN ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])
	) AS T
INSERT INTO #T2
 SELECT * FROM #T6 /*数据源*/
	AS P
	PIVOT
	(
	    MAX(ZTRate)   FOR
	    P.eday/*需要行转列的列*/ IN ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])
	) AS T


INSERT INTO #T2
 SELECT * FROM #T7 /*数据源*/
	AS P
	PIVOT
	(
	    MAX(BadQty)   FOR
	    P.eday/*需要行转列的列*/ IN ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])
	) AS T




----直通率临时表
SELECT row_number() over(ORDER BY RouteCode,BadItem) AS id ,RouteCode,RouteName,BadItem,
ISNULL([01],1) [01],ISNULL([02],1) [02],ISNULL([03],1) [03],ISNULL([04],1) [04],ISNULL([05],1) [05],ISNULL([06],1) [06],ISNULL([07],1) [07],ISNULL([08],1) [08],
ISNULL([09],1) [09],ISNULL([10],1) [10],ISNULL([11],1) [11],ISNULL([12],1) [12],ISNULL([13],1) [13],ISNULL([14],1) [14],ISNULL([15],1) [15],ISNULL([16],1) [16],
ISNULL([17],1) [17],ISNULL([18],1) [18],ISNULL([19],1) [19],ISNULL([20],1) [20],ISNULL([21],1) [21],ISNULL([22],1) [22],ISNULL([23],1) [23],ISNULL([24],1) [24],
ISNULL([25],1) [25],ISNULL([26],1) [26],ISNULL([27],1) [27],ISNULL([28],1) [28],ISNULL([29],1) [29],ISNULL([30],1) [30],ISNULL([31],1) [31]
INTO #ZT
FROM #T2
WHERE BadItem='4.直通率'
ORDER BY RouteCode, RouteName,BadItem ASC

---更新累积----
UPDATE  #T2
SET 
#T2.[01]=B.[01],#T2.[02]=B.[02],#T2.[03]=B.[03],#T2.[04]=B.[04],#T2.[05]=B.[05],#T2.[06]=B.[06],#T2.[07]=B.[07],#T2.[08]=B.[08],
#T2.[09]=B.[09],#T2.[10]=B.[10],#T2.[11]=B.[11],#T2.[12]=B.[12],#T2.[13]=B.[13],#T2.[14]=B.[14],#T2.[15]=B.[15],#T2.[16]=B.[16],
#T2.[17]=B.[17],#T2.[18]=B.[18],#T2.[19]=B.[19],#T2.[20]=B.[20],#T2.[21]=B.[21],#T2.[22]=B.[22],#T2.[23]=B.[23],#T2.[24]=B.[24],
#T2.[25]=B.[25],#T2.[26]=B.[26],#T2.[27]=B.[27],#T2.[28]=B.[28],#T2.[29]=B.[29],#T2.[30]=B.[30],#T2.[31]=B.[31]
FROM #T2  INNER JOIN 
(
select id,RouteCode,RouteName,BadItem,
EXP(sum(log([01])) over(order by id ASC)) AS [01],
EXP(sum(log([02])) over(order by id ASC)) AS [02],
EXP(sum(log([03])) over(order by id ASC)) AS [03],
EXP(sum(log([04])) over(order by id ASC)) AS [04],
EXP(sum(log([05])) over(order by id ASC)) AS [05],
EXP(sum(log([06])) over(order by id ASC)) AS [06],
EXP(sum(log([07])) over(order by id ASC)) AS [07],
EXP(sum(log([08])) over(order by id ASC)) AS [08],
EXP(sum(log([09])) over(order by id ASC)) AS [09],
EXP(sum(log([10])) over(order by id ASC)) AS [10],
EXP(sum(log([11])) over(order by id ASC)) AS [11],
EXP(sum(log([12])) over(order by id ASC)) AS [12],
EXP(sum(log([13])) over(order by id ASC)) AS [13],
EXP(sum(log([14])) over(order by id ASC)) AS [14],
EXP(sum(log([15])) over(order by id ASC)) AS [15],
EXP(sum(log([16])) over(order by id ASC)) AS [16],
EXP(sum(log([17])) over(order by id ASC)) AS [17],
EXP(sum(log([18])) over(order by id ASC)) AS [18],
EXP(sum(log([19])) over(order by id ASC)) AS [19],
EXP(sum(log([20])) over(order by id ASC)) AS [20],
EXP(sum(log([21])) over(order by id ASC)) AS [21],
EXP(sum(log([22])) over(order by id ASC)) AS [22],
EXP(sum(log([23])) over(order by id ASC)) AS [23],
EXP(sum(log([24])) over(order by id ASC)) AS [24],
EXP(sum(log([25])) over(order by id ASC)) AS [25],
EXP(sum(log([26])) over(order by id ASC)) AS [26],
EXP(sum(log([27])) over(order by id ASC)) AS [27],
EXP(sum(log([28])) over(order by id ASC)) AS [28],
EXP(sum(log([29])) over(order by id ASC)) AS [29],
EXP(sum(log([30])) over(order by id ASC)) AS [30],
EXP(sum(log([31])) over(order by id ASC)) AS [31]

FROM #ZT
) AS B ON #T2.RouteCode=B.RouteCode AND #T2.BadItem=B.BadItem


SELECT * FROM #T1
UNION ALL
SELECT 
RouteCode,RouteName,BadItem,
CAST(ISNULL([01],0) AS CHAR(100)) [01],CAST(ISNULL([02],0) AS CHAR(100)) [02],CAST(ISNULL([03],0) AS CHAR(100)) [03],CAST(ISNULL([04],0) AS CHAR(100)) [04],CAST(ISNULL([05],0) AS CHAR(100)) [05],CAST(ISNULL([06],0) AS CHAR(100)) [06],CAST(ISNULL([07],0) AS CHAR(100)) [07],CAST(ISNULL([08],0) AS CHAR(100)) [08],
CAST(ISNULL([09],0) AS CHAR(100)) [09],CAST(ISNULL([10],0) AS CHAR(100)) [10],CAST(ISNULL([11],0) AS CHAR(100)) [11],CAST(ISNULL([12],0) AS CHAR(100)) [12],CAST(ISNULL([13],0) AS CHAR(100)) [13],CAST(ISNULL([14],0) AS CHAR(100)) [14],CAST(ISNULL([15],0) AS CHAR(100)) [15],CAST(ISNULL([16],0) AS CHAR(100)) [16],
CAST(ISNULL([17],0) AS CHAR(100)) [17],CAST(ISNULL([18],0) AS CHAR(100)) [18],CAST(ISNULL([19],0) AS CHAR(100)) [19],CAST(ISNULL([20],0) AS CHAR(100)) [20],CAST(ISNULL([21],0) AS CHAR(100)) [21],CAST(ISNULL([22],0) AS CHAR(100)) [22],CAST(ISNULL([23],0) AS CHAR(100)) [23],CAST(ISNULL([24],0) AS CHAR(100)) [24],
CAST(ISNULL([25],0) AS CHAR(100)) [25],CAST(ISNULL([26],0) AS CHAR(100)) [26],CAST(ISNULL([27],0) AS CHAR(100)) [27],CAST(ISNULL([28],0) AS CHAR(100)) [28],CAST(ISNULL([29],0) AS CHAR(100)) [29],CAST(ISNULL([30],0) AS CHAR(100)) [30],CAST(ISNULL([31],1) AS CHAR(100)) [31]
FROM #T2
ORDER BY RouteCode, RouteName,BadItem ASC

END

效果：
在这里插入图片描述

厦门德仔
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
订阅专栏
巧用sql计算累计值
当一堆用不熄灭的柴火
03-31 1万+
业务：如下图所示，下图是每日公司故障数和行车里程数统计，要得到累计故障率的数据，举个例子，我想要看3月4日的累计故障率，就要将这天之前所有的故障数加起来，除以里程数之和，得到这一天的累计故障率 套用的累计sql: select id,time,(select SUM(X)from table as i where i.time&lt;=o.time) as total from table...
SQL积累--统计某年某月中每天的知识数量
竹叶戏雨的博客
03-21 1091
需求：查询某个月每天的工单数量，用作统计。 效果图 笨方法： 数据库根据输入的年月查出已有工单数list， java程序中判断输入的年月有多少天，并生成对应天数的集合daylist 将SQL查出的数据和天数的集合做比较，有则放入集合，没有则补零。 select to_char(ASK_TIME, 'dd') day,count(*) num from KWMIS_ZHISHIKU...
SQL-按日期不间断统计
qq_34926773的博客
04-04 2137
一、数据库 日期数据不间断 SELECT COUNT(1) AS count, SUM(age) AS age FROM `user` GROUP BY DATE_FORMAT(create_time,'%Y-%m-%d'); 二、数据库 日期数据不连续 间断补0 1.不创建表方式 （临时表 -- 只读库也...
如何用计算机计算log除法,电脑系统自带科学计算器使用方法
weixin_34603814的博客
06-17 2140
电脑的科学计算器函数名:ln在数学中ln(x)是以e为底的x的对数。e是一个常数函数名: exp功能: 指数函数exp就是以e为底的指数，exp(0)=1, exp(1)=e, exp(2)=en!代表一个数的阶乘，例如：5！就是5*4*3*2*1；sta代表统计算法；x^3代表x的3次方；x^y代表x的y次方。sin正弦，cos余弦，tan正切，dms度分秒切换Lsh左移ln自然对数，F-E科学...
使用SQL查询累计值
三十七计
01-13 3607
有这样一个要求，它要创建一个SQL Server查询，其中包括基于事件时刻的累计值。典型的例子就是一个银行账户，因为你每一次都是在不同的时间去存钱和取钱。对任意一个账户来说，在某个时间点都要算出它的借（存款）和贷（取款）之和。每一笔交易之后，你都想知道当前的余额。列表A创建了这样一个表格的简单的例子。  
MSSQL数据库SQL注入手册1
08-08
MSSQL数据库SQL注入手册1
获取MSSQL数据字典的SQL语句
09-11
获取MSSQL数据字典的实现方法。
php mssql 分页SQL语句优化 持续影响
10-29
经过SQL优化后的分面查询速度能够得到大幅提高。
MSSQL存储过程解密工具SQL SERVER
最新发布
04-10
SQL SERVER存储过程解密工具，sqldecryptor可以将被加密的存储过程和函数等直接解密
php mssql扩展SQL查询中文字段名解决方法
12-18
一、问题： 数据库是MS SQLServer2000，要把SQLServer2000里的一张表的数据导入MySQL5，其中SQLServer2000表的字段以简体中文命名(强烈建议不要以中文做为字段名)。其实操作就是对SQLServer查询记录，插入到MySQL里...
【SQL】累计统计运算
Insist
05-18 2307
表名：table_test_v1 字段说明：id-唯一标识；date_id-日期；earnings-收益 数据如下： 需求：计算往年累计收益 方法一： select b.date_id as '年份',sum(a.earnings) as '累计收益' from table_test_v1 a,table_test_v1 b where a.date_id<=b.date_id group by b.date_id order by b.date_id; 方法二： sel...
HIVE/SQL 实现同一列数据累加和累乘
TRX的博客
09-14 4257
以上是对数的一些运算性质，其中我重点圈出了两个性质，这将是我们使用 hive sql 实现同一列数据累乘的关键；1）左边的红框中，两个底数(a)相同的对数相加 = 以a为底(N*M)的对数，其中(N*M)就是我们想要的计算结果，应该如何获取(N*M)呢？2）看右边红框的性质，我们可以利用这个性质获取(N*M)映射到 hive ，可以将同一列的相乘转为同一列的对数相加，在求真数即可;具体做法：
MSSQL 查询统计某状态出现的次数及累计时间
weixin_30576859的博客
10-23 638
1、问题来源 最近客户需要统计某个设备，某状态，在某一个时间段内出现的次数，并计算累计出现的时间。 数据源如下： 现在如果要统计UPSCTSTA状态为D出现的次数（同一状态，连续出现的认为是一次），并计算累计出现的时间 解决方法 方案1，采用存储过程 CREATE PROCEDURE [dbo].[Proc_UPSState_Statistics] @G...
mysql求累计值_SQL语句-计算累加值
weixin_31975689的博客
01-20 2782
问题：计算某个列中所有值的累计和解决方案下面给出了一种解决方案，它展示了如何计算所有职员工资的累计和。为增加可读性，其结果是按SAL排序的，这样就能够很容易地观察到累计和变化的过程。DB2和Oracle使用窗口版本的SUM函数计算累计和：1 select ename, sal,2 sum(sal) over (order by sal,empno) as running_total...
SQL的乘积
MICRO BI
02-08 3639
看个例子：   CREATE TABLE #tmp (RowID INT, RowValue INT); INSERT #tmp( RowID, RowValue ) VALUES ( 1, 10); INSERT #tmp( RowID, RowValue ) VALUES ( 1, 5); INSERT #tmp( RowID, RowValue ) VALUES ( 2,
Sql Server 指定列的乘积
waterstar50的专栏
11-10 5606
需要统计指定列的乘积 SQL SERVER中有EXP(SUM(LOG(字段)))的函数 如下例子：SELECT WorkOrder , LineId , ROUND(EXP(SUM(LOG(ThroughRate))), 4) * 100 AS ThroughRate FROM #ThroughRate G
mssql任意连续整数之间的的乘积
weixin_30794851的博客
10-15 66
任意连续整数之间的的乘积 代码 --任意连续整数之间的的乘积 修改 exce p_test ?,? 数值改变 -- author ： 【DBA】小七if Exists(Select name From sysobjects Where name='p_test' And type='P')Drop Procedure p_test Gocreate proc ...
how to record mssql 2000 sql execution history
04-02
Unfortunately, Microsoft SQL Server 2000 does not have built-in functionality to record SQL execution history. However, there are a few workarounds you can try: 1. Use SQL Profiler: SQL Profiler is a...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

厦门德仔

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值