SQL 计算公式

2017-08-23 02:13:37 1.2k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

Linux系统下SAPGUI安装包选择与避坑指南本文详细介绍了在Linux系统下选择和安装SAPGUI安装包的策略与避坑指南。重点解析了不同版本安装包的关键区别，特别是Linux版必须包含linux-lib.jar和linux64-lib.jar文件，避免误用Mac或Windows版导致的兼容性问题。文章还提供了针对Ubuntu、RHEL等不同Linux发行版的安装实战指南，以及常见报错的解决方案，帮助用户顺利完成SAPGUI的安装与配置。阅读详情

Material：物品名称
Project：检测项
Calculate：是否计算
Data：值
Formula：公式
Tolerance：显示
Standard：显示
用SQL计算出需要计算的值
游标
replace替换公式,执行公式.

...全文

保姆级教程：在RK3568上调试RGMII网口，从设备树到PHY驱动的完整避坑指南本文提供RK3568开发板上RGMII网口调试的完整指南，涵盖硬件检查、设备树配置、驱动加载及性能优化。通过详细步骤和实战案例，帮助开发者避开常见陷阱，确保以太网功能稳定运行，特别关注PHY驱动和MDIO总线的调试技巧。阅读详情

som@''

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

gw6328 2017-08-28

打赏
举报

引用 19 楼 qq_37889561 的回复:

[quote=引用 17 楼 jinfengyiye 的回复:] 确实可以用replace方法，按我上的已经出来了公式，项。那么只公式前面加项个数的replace( ，在后面加项,值), 然后拼起来就可以了。也是批量处理。相当于有几个项就有几个replace.

还是写不出来，replace不会用。[/quote] DECLARE @t TABLE(material NVARCHAR(100),project NVARCHAR(100),VALUE NUMERIC(10,2) ,formula NVARCHAR(100)); INSERT INTO @t (material,project,value,formula) VALUES ('a','质量',10,''), ('a','体积',20,''), ('a','标液',30,''), ('a','含量',0,'体积*标液*0.27801/质量'), ('b','质量1',0.25,''), ('b','m',10,''), ('b','含量',0,'5*m/质量1') ;WITH cte AS ( SELECT a.*,b.project AS pb,b.VALUE AS bv FROM @t a JOIN @t b ON a.material=b.material AND a.value=0 AND b.value<>0 ) SELECT x.material,x.formula ,REPLICATE('replace(',COUNT(1))+''''+x.formula+''''+ (SELECT ','+y.pb+','+CAST(y.bv AS VARCHAR(100))+')' FROM cte y WHERE y.material=x.material FOR XML PATH('')) FROM cte x GROUP BY x.material,x.formula 后面你还是要执行一次啊。所以我建议还是程序处理吧。

som@'' 2017-08-28

打赏
举报

引用 22 楼 jinfengyiye 的回复:

[quote=引用 19 楼 qq_37889561 的回复:] [quote=引用 17 楼 jinfengyiye 的回复:] 确实可以用replace方法，按我上的已经出来了公式，项。那么只公式前面加项个数的replace( ，在后面加项,值), 然后拼起来就可以了。也是批量处理。相当于有几个项就有几个replace.

declare
       --游标类型定义
       cursor table_cur
       is
       select Material,Formula from Table_1 
       WHERE Calculate='是';
			 cursor table_str
			 IS
			 select projects,datass from  TABLE_1 
			 where  calculate<>'是';		
       --定义一个游标变量table_row table_cur%ROWTYPE ，该类型为游标table_cur中的一行数据类型
       table_row table_cur%rowtype;
			 table_cow table_str%rowtype;
			 Formula table_1.Formula%type;
			 
begin
       --游标for循环开始
       for table_row in table_cur loop
					for table_cow in table_str loop
									
							Formula:=replace(table_row.Formula,table_cow.Projects,table_cow.Datass);
							
							dbms_output.put_line(Formula);				
					end loop;
       end loop;

       ----游标for循环结束           
end;

这是我根据前面 ”雨停了“的方法用Orcale写出来的，但是显示出来的结果却是

使用了很多合并重复行的函数都是报错，有什么好的办法吗？

听雨停了 2017-08-25

打赏
举报

引用 18 楼 qq_37889561 的回复:

[quote=引用 16 楼 qq_37170555 的回复:] DECLARE @Material VARCHAR(20),@Project VARCHAR(20),@Data NUMERIC(6,4),@Formula VARCHAR(50),@Sql VARCHAR(50) DECLARE my_cur CURSOR FOR --第一个游标读取Material对应的公式 SELECT material FROM #Temp_1 WHERE Calcule='是' OPEN my_cur FETCH NEXT FROM my_cur INTO @Material WHILE @@FETCH_STATUS=0 BEGIN SELECT @Formula = Formula FROM #Temp_1 WHERE Material=@Material DECLARE my_cur2 CURSOR FOR --第二个游标读取Project的值用以替换 SELECT Project FROM #Temp_1 WHERE Material=@Material AND Calcule<>'是' OPEN my_cur2 FETCH NEXT FROM my_cur2 INTO @Project WHILE @@FETCH_STATUS=0 BEGIN SELECT @Data=data FROM #Temp_1 WHERE Material=@Material AND Project=@Project SET @Formula=REPLACE(@Formula,@Project,@Data) --replace替换公式 FETCH NEXT FROM my_cur2 INTO @Project END PRINT @Material PRINT @Formula SET @Sql='select '+@Formula EXEC (@Sql) PRINT @Data CLOSE my_cur2 DEALLOCATE my_cur2 FETCH NEXT FROM my_cur INTO @Material END CLOSE my_cur DEALLOCATE my_cur --运行以上脚本打印出来的结果，运用到了你说的游标，replace替换公式硫酸亚铁_含量 20.0000*30.0000*0.27801/10.0000 (1 行受影响) 30.0000 硫酸亚铁_二氧化钛 5*0.4500/0.2500 (1 行受影响) 0.4500 硫酸亚铁_水不溶物 (0.8800-0.6600)/0.1100 (1 行受影响) 0.8800 -------------------------------写在最后，虽然我只是一个小菜，哈哈-----------------------------

我用你的测试出来了，怎么把值放到Data里面去呢？[/quote] 如果你是要修改这个表，就直接update就行了，如果只是查询出来看，那你把结果插入一个零时表就可以了

顺势而为1 2017-08-25

打赏
举报

樓主參考一下這個貼子,大同小異. http://bbs.csdn.net/topics/392207318

gw6328 2017-08-24

打赏
举报

确实可以用replace方法，按我上的已经出来了公式，项。那么只公式前面加项个数的replace( ，在后面加项,值), 然后拼起来就可以了。也是批量处理。相当于有几个项就有几个replace.

听雨停了 2017-08-24

打赏
举报

DECLARE @Material VARCHAR(20),@Project VARCHAR(20),@Data NUMERIC(6,4),@Formula VARCHAR(50),@Sql VARCHAR(50) DECLARE my_cur CURSOR FOR --第一个游标读取Material对应的公式 SELECT material FROM #Temp_1 WHERE Calcule='是' OPEN my_cur FETCH NEXT FROM my_cur INTO @Material WHILE @@FETCH_STATUS=0 BEGIN SELECT @Formula = Formula FROM #Temp_1 WHERE Material=@Material DECLARE my_cur2 CURSOR FOR --第二个游标读取Project的值用以替换 SELECT Project FROM #Temp_1 WHERE Material=@Material AND Calcule<>'是' OPEN my_cur2 FETCH NEXT FROM my_cur2 INTO @Project WHILE @@FETCH_STATUS=0 BEGIN SELECT @Data=data FROM #Temp_1 WHERE Material=@Material AND Project=@Project SET @Formula=REPLACE(@Formula,@Project,@Data) --replace替换公式 FETCH NEXT FROM my_cur2 INTO @Project END PRINT @Material PRINT @Formula SET @Sql='select '+@Formula EXEC (@Sql) PRINT @Data CLOSE my_cur2 DEALLOCATE my_cur2 FETCH NEXT FROM my_cur INTO @Material END CLOSE my_cur DEALLOCATE my_cur --运行以上脚本打印出来的结果，运用到了你说的游标，replace替换公式硫酸亚铁_含量 20.0000*30.0000*0.27801/10.0000 (1 行受影响) 30.0000 硫酸亚铁_二氧化钛 5*0.4500/0.2500 (1 行受影响) 0.4500 硫酸亚铁_水不溶物 (0.8800-0.6600)/0.1100 (1 行受影响) 0.8800 -------------------------------写在最后，虽然我只是一个小菜，哈哈-----------------------------

Pact_Alice 2017-08-24

打赏
举报

--DROP TABLE #Temp_1
--创建临时表数据
CREATE TABLE #Temp_1
(
ID INT,
Material VARCHAR(200),
Project  VARCHAR(200),
Calcule	VARCHAR(200),
Data		VARCHAR(20),
Formula		VARCHAR(200),
Tolerance	VARCHAR(200),
Standrad	VARCHAR(200)
)

INSERT INTO #Temp_1
SELECT 1,'硫酸亚铁_含量','质量','否',10,'','','' UNION ALL
SELECT 1,'硫酸亚铁_含量','体积','否',20,'','','' UNION ALL
SELECT 1,'硫酸亚铁_含量','标液','否',30,'','','' UNION ALL
SELECT 1,'硫酸亚铁_含量','含量','否',0,'体积*标液*0.27801/质量','≤0.4%','' 

--先动态的将行转列方便数据的获取
DECLARE @sql VARCHAR(max)
SET @sql='select Material,max(Formula)Formula '
SELECT @sql=@sql+','+QUOTENAME(project)+N'= max(case project when '+QUOTENAME(project,N'''')+N' then  cast(data as numeric(18,2)) end)'
FROM #Temp_1
GROUP BY project
exec(@sql+N'  into Temp_2 from   #Temp_1 GROUP BY Material')
--将数据放入实体临时表中

--动态的执行公式
declare @rule nvarchar(MAX) 
select @rule=STUFF((
SELECT ' UNION ALL SELECT '''+LTRIM(Material)+''' AS Material,'''+
		Formula+'''as Formula,'+
		CAST([体积]AS VARCHAR(20))+' as [体积] ,'+
		CAST([质量]AS VARCHAR(20))+' as [质量] ,'+
		CAST([标液] AS VARCHAR(20))+'as [标液] ,'+
		Formula+' AS Formula ' from Temp_2 FOR XML PATH('')),1,10,'')+'from Temp_2'
exec (@rule)

--删除临时表
DROP TABLE Temp_2

gw6328 2017-08-24

打赏
举报

引用 10 楼 qq_37889561 的回复:

[quote=引用 6 楼 jinfengyiye 的回复:]



DECLARE @t TABLE(material NVARCHAR(100),project NVARCHAR(100),VALUE NUMERIC(10,2) ,formula NVARCHAR(100));
INSERT INTO @t (material,project,value,formula) VALUES
('a','质量',10,''),
('a','体积',20,''),
('a','标液',30,''),
('a','含量',0,'体积*标液*0.27801/质量'),
('b','质量1',0.25,''),
('b','m',10,''),
('b','含量',0,'5*m/质量1')

;WITH cte AS (
SELECT a.*,b.project AS pb,b.VALUE AS bv FROM @t a JOIN @t b ON a.material=b.material AND a.value=0 AND b.value<>0
)
SELECT x.material,x.formula,(SELECT ','+y.pb+':'+CAST(y.bv AS VARCHAR(100)) FROM cte y WHERE y.material=x.material FOR XML PATH('')) FROM cte x GROUP BY x.material,x.formula
/*
a	体积*标液*0.27801/质量	,质量:10.00,体积:20.00,标液:30.00
b	5*m/质量1	,质量1:0.25,m:10.00
*/

没时间了，我要下班了。只能到这里了。考虑在上面代码读取一个正常，前台一个eval()就可以出结果了。也不会太复杂。如果要在sql里计算确实会复杂很多。

前面复制错了是这个

CREATE TABLE [6tb] ([fitemid] [int],[FID_V] [nvarchar](10),[FName] [nvarchar](10),[FValue] [numeric](14,10),[FExpression] [nvarchar](30))
INSERT INTO [6tb]
SELECT '8903','HBase','红本价','740.0000000000','HBase' UNION ALL
SELECT '8905','HBase','红本价','917.0000000000','HBase' UNION ALL
SELECT '8929','T1003','1KV','.0000000000','HBase+T1003+T1005' UNION ALL
SELECT '8929','T1005','1000欧姆','.0000000000','HBase+T1003+T1005' UNION ALL
SELECT '8929','HBase','红本价','200.0000000000','HBase+T1003+T1005' UNION ALL
SELECT '8931','HBase','红本价','288.0000000000','HBase+T1003+T1005' UNION ALL
SELECT '8931','T1005','1000欧姆','.0000000000','HBase+T1003+T1005' UNION ALL
SELECT '8931','T1003','1KV','.0000000000','HBase+T1003+T1005' UNION ALL
SELECT '8933','HBase','红本价','450.0000000000','HBase' UNION ALL
SELECT '8935','HBase','红本价','600.0000000000','HBase' UNION ALL
SELECT '9015','HBase','红本价','740.0000000000','(HBase+T1003+T1006)*T1009' UNION ALL
SELECT '9015','T1003','1KV','.0000000000','(HBase+T1003+T1006)*T1009' UNION ALL
SELECT '9015','T1006','100欧姆','.0000000000','(HBase+T1003+T1006)*T1009' UNION ALL
SELECT '9015','T1009','红绿色','11.0000000000','(HBase+T1003+T1006)*T1009' UNION ALL
SELECT '9017','T1009','红绿色','11.0000000000','HBase' UNION ALL
SELECT '9017','T1005','1000欧姆','.0000000000','HBase' UNION ALL
SELECT '9017','T1003','1KV','.0000000000','HBase' UNION ALL
SELECT '9017','HBase','红本价','965.0000000000','HBase' UNION ALL
SELECT '9019','HBase','红本价','1270.0000000000','HBase' UNION ALL
SELECT '9021','HBase','红本价','1790.0000000000','HBase' UNION ALL
SELECT '9029','HBase','红本价','200.0000000000','HBase' UNION ALL
SELECT '9031','HBase','红本价','288.0000000000','HBase'
 
CREATE TABLE GGGUU(fitemid INT,R NUMERIC(14,10))
GO 
CREATE FUNCTION GetVb(@Fitemid AS INT,@FExpression nvarchar(100) )
RETURNS nvarchar(100)
AS 
BEGIN
    DECLARE @sql varchar(100)
    SELECT @FExpression=replace(@FExpression,FId_V,ltrim(FValue)) FROM [6tb] WHERE fitemid=@Fitemid
    SET @sql='INSERT GGGUU select '+LTRIM(@Fitemid)+','+@FExpression+' G'
    RETURN @sql
END
GO 
EXEC MASTER.dbo.xp_execresultset 'SELECT dbo.getVB(Fitemid,FExpression) FROM [6tb]',N'test'
 
SELECT * FROM [6TB] A INNER JOIN (SELECT DISTINCT * FROM GGGUU) B
ON A.fitemid=B.fitemid
 
--RESULT
/*fitemid     FID_V      FName      FValue           FExpression                    fitemid     R                
----------- ---------- ---------- ---------------- ------------------------------ ----------- ---------------- 
8903        HBase      红本价        740.0000000000   HBase                          8903        740.0000000000
8905        HBase      红本价        917.0000000000   HBase                          8905        917.0000000000
8929        T1003      1KV        .0000000000      HBase+T1003+T1005              8929        200.0000000000
8929        T1005      1000欧姆     .0000000000      HBase+T1003+T1005              8929        200.0000000000
8929        HBase      红本价        200.0000000000   HBase+T1003+T1005              8929        200.0000000000
8931        HBase      红本价        288.0000000000   HBase+T1003+T1005              8931        288.0000000000
8931        T1005      1000欧姆     .0000000000      HBase+T1003+T1005              8931        288.0000000000
8931        T1003      1KV        .0000000000      HBase+T1003+T1005              8931        288.0000000000
8933        HBase      红本价        450.0000000000   HBase                          8933        450.0000000000
8935        HBase      红本价        600.0000000000   HBase                          8935        600.0000000000
9015        HBase      红本价        740.0000000000   (HBase+T1003+T1006)*T1009      9015        8140.0000000000
9015        T1003      1KV        .0000000000      (HBase+T1003+T1006)*T1009      9015        8140.0000000000
9015        T1006      100欧姆      .0000000000      (HBase+T1003+T1006)*T1009      9015        8140.0000000000
9015        T1009      红绿色        11.0000000000    (HBase+T1003+T1006)*T1009      9015        8140.0000000000
9017        T1009      红绿色        11.0000000000    HBase                          9017        965.0000000000
9017        T1005      1000欧姆     .0000000000      HBase                          9017        965.0000000000
9017        T1003      1KV        .0000000000      HBase                          9017        965.0000000000
9017        HBase      红本价        965.0000000000   HBase                          9017        965.0000000000
9019        HBase      红本价        1270.0000000000  HBase                          9019        1270.0000000000
9021        HBase      红本价        1790.0000000000  HBase                          9021        1790.0000000000
9029        HBase      红本价        200.0000000000   HBase                          9029        200.0000000000
9031        HBase      红本价        288.0000000000   HBase                          9031        288.0000000000

[/quote] replace不行，因为你列不固定，上面的行转列也不行的，因为不固定。如果你选一项当然也是可以的，我说的是批量。我建议还是程序处理。不是说某种能实现不能实现，只是选一种比较简单的方式而已。

Pact_Alice 2017-08-24

打赏
举报

大致做法如下，需要的字段自己拉取出来 --创建临时表数据 CREATE TABLE #Temp_1 ( ID INT, Material VARCHAR(200), Project VARCHAR(200), Calcule VARCHAR(200), Data VARCHAR(20), Formula VARCHAR(200), Tolerance VARCHAR(200), Standrad VARCHAR(200) ) INSERT INTO #Temp_1 SELECT 1,'硫酸亚铁_含量','质量','否',10,'','','' UNION ALL SELECT 1,'硫酸亚铁_含量','体积','否',20,'','','' UNION ALL SELECT 1,'硫酸亚铁_含量','标液','否',30,'','','' UNION ALL SELECT 1,'硫酸亚铁_含量','含量','否',0,'体积*标液*0.27801/质量','≤0.4%','' --先动态的将行转列方便数据的获取 DECLARE @sql VARCHAR(max) SET @sql='select Material,max(Formula)Formula ' SELECT @sql=@sql+','+QUOTENAME(project)+N'= max(case project when '+QUOTENAME(project,N'''')+N' then cast(data as numeric(18,2)) end)' FROM #Temp_1 GROUP BY project exec(@sql+N' into Temp_2 from #Temp_1 GROUP BY Material') --将数据放入实体临时表中 SELECT * FROM Temp_2 --动态的执行公式 declare @rule nvarchar(MAX) select @rule=STUFF((SELECT ' UNION ALL SELECT '''+LTRIM(Material)+''' AS Material,'''+Formula+'''as Formula,'+Formula+' AS Formula ' from Temp_2 FOR XML PATH('')),1,10,'')+'from Temp_2' exec (@rule) --删除临时表 DROP TABLE Temp_2 希望能帮到您

som@'' 2017-08-24

打赏
举报

引用 17 楼 jinfengyiye 的回复:

确实可以用replace方法，按我上的已经出来了公式，项。那么只公式前面加项个数的replace( ，在后面加项,值), 然后拼起来就可以了。也是批量处理。相当于有几个项就有几个replace.

还是写不出来，replace不会用。

som@'' 2017-08-24

打赏
举报

引用 16 楼 qq_37170555 的回复:

DECLARE @Material VARCHAR(20),@Project VARCHAR(20),@Data NUMERIC(6,4),@Formula VARCHAR(50),@Sql VARCHAR(50) DECLARE my_cur CURSOR FOR --第一个游标读取Material对应的公式 SELECT material FROM #Temp_1 WHERE Calcule='是' OPEN my_cur FETCH NEXT FROM my_cur INTO @Material WHILE @@FETCH_STATUS=0 BEGIN SELECT @Formula = Formula FROM #Temp_1 WHERE Material=@Material DECLARE my_cur2 CURSOR FOR --第二个游标读取Project的值用以替换 SELECT Project FROM #Temp_1 WHERE Material=@Material AND Calcule<>'是' OPEN my_cur2 FETCH NEXT FROM my_cur2 INTO @Project WHILE @@FETCH_STATUS=0 BEGIN SELECT @Data=data FROM #Temp_1 WHERE Material=@Material AND Project=@Project SET @Formula=REPLACE(@Formula,@Project,@Data) --replace替换公式 FETCH NEXT FROM my_cur2 INTO @Project END PRINT @Material PRINT @Formula SET @Sql='select '+@Formula EXEC (@Sql) PRINT @Data CLOSE my_cur2 DEALLOCATE my_cur2 FETCH NEXT FROM my_cur INTO @Material END CLOSE my_cur DEALLOCATE my_cur --运行以上脚本打印出来的结果，运用到了你说的游标，replace替换公式硫酸亚铁_含量 20.0000*30.0000*0.27801/10.0000 (1 行受影响) 30.0000 硫酸亚铁_二氧化钛 5*0.4500/0.2500 (1 行受影响) 0.4500 硫酸亚铁_水不溶物 (0.8800-0.6600)/0.1100 (1 行受影响) 0.8800 -------------------------------写在最后，虽然我只是一个小菜，哈哈-----------------------------

我用你的测试出来了，怎么把值放到Data里面去呢？

xiaoxiangqing 2017-08-24

打赏
举报

只能用动态语句了，最好保存的时候，就计算好

som@'' 2017-08-23

打赏
举报

引用 8 楼 Landa_Alice 的回复:

CREATE TABLE #Temp_1 ( ID INT, Material VARCHAR(200), Project VARCHAR(200), Calcule VARCHAR(200), Data VARCHAR(20), Formula VARCHAR(200), Tolerance VARCHAR(200), Standrad VARCHAR(200) ) INSERT INTO #Temp_1 SELECT 1,'硫酸亚铁_含量','质量','否',10,'','','' UNION ALL SELECT 1,'硫酸亚铁_含量','体积','否',20,'','','' UNION ALL SELECT 1,'硫酸亚铁_含量','标液','否',30,'','','' UNION ALL SELECT 1,'硫酸亚铁_含量','含量','否',0,'体积*标液*0.27801/质量','≤0.4%','' DECLARE @sql VARCHAR(max) SET @sql='select Material,max(Formula)Formula ' SELECT @sql=@sql+','+QUOTENAME(project)+N'= max(case project when '+QUOTENAME(project,N'''')+N' then data end)' FROM #Temp_1 GROUP BY project exec(@sql+N' from #Temp_1 GROUP BY Material') 下班了，就先上面吧

感谢，能不能用replace替换的方式实现计算的值

CREATE TABLE [6tb] ([fitemid] [int],[FID_V] [nvarchar](10),[FName] [nvarchar](10),[FValue] [numeric](14,10),[FExpression] [nvarchar](30))
INSERT INTO [6tb]
SELECT '8903','HBase','红本价','740.0000000000','HBase' UNION ALL
SELECT '8905','HBase','红本价','917.0000000000','HBase' UNION ALL
SELECT '8929','T1003','1KV','.0000000000','HBase+T1003+T1005' UNION ALL
SELECT '8929','T1005','1000欧姆','.0000000000','HBase+T1003+T1005' UNION ALL
SELECT '8929','HBase','红本价','200.0000000000','HBase+T1003+T1005' UNION ALL
SELECT '8931','HBase','红本价','288.0000000000','HBase+T1003+T1005' UNION ALL
SELECT '8931','T1005','1000欧姆','.0000000000','HBase+T1003+T1005' UNION ALL
SELECT '8931','T1003','1KV','.0000000000','HBase+T1003+T1005' UNION ALL
SELECT '8933','HBase','红本价','450.0000000000','HBase' UNION ALL
SELECT '8935','HBase','红本价','600.0000000000','HBase' UNION ALL
SELECT '9015','HBase','红本价','740.0000000000','(HBase+T1003+T1006)*T1009' UNION ALL
SELECT '9015','T1003','1KV','.0000000000','(HBase+T1003+T1006)*T1009' UNION ALL
SELECT '9015','T1006','100欧姆','.0000000000','(HBase+T1003+T1006)*T1009' UNION ALL
SELECT '9015','T1009','红绿色','11.0000000000','(HBase+T1003+T1006)*T1009' UNION ALL
SELECT '9017','T1009','红绿色','11.0000000000','HBase' UNION ALL
SELECT '9017','T1005','1000欧姆','.0000000000','HBase' UNION ALL
SELECT '9017','T1003','1KV','.0000000000','HBase' UNION ALL
SELECT '9017','HBase','红本价','965.0000000000','HBase' UNION ALL
SELECT '9019','HBase','红本价','1270.0000000000','HBase' UNION ALL
SELECT '9021','HBase','红本价','1790.0000000000','HBase' UNION ALL
SELECT '9029','HBase','红本价','200.0000000000','HBase' UNION ALL
SELECT '9031','HBase','红本价','288.0000000000','HBase'
 
CREATE TABLE GGGUU(fitemid INT,R NUMERIC(14,10))
GO 
CREATE FUNCTION GetVb(@Fitemid AS INT,@FExpression nvarchar(100) )
RETURNS nvarchar(100)
AS 
BEGIN
    DECLARE @sql varchar(100)
    SELECT @FExpression=replace(@FExpression,FId_V,ltrim(FValue)) FROM [6tb] WHERE fitemid=@Fitemid
    SET @sql='INSERT GGGUU select '+LTRIM(@Fitemid)+','+@FExpression+' G'
    RETURN @sql
END
GO 
EXEC MASTER.dbo.xp_execresultset 'SELECT dbo.getVB(Fitemid,FExpression) FROM [6tb]',N'test'
 
SELECT * FROM [6TB] A INNER JOIN (SELECT DISTINCT * FROM GGGUU) B
ON A.fitemid=B.fitemid
 
--RESULT
/*fitemid     FID_V      FName      FValue           FExpression                    fitemid     R                
----------- ---------- ---------- ---------------- ------------------------------ ----------- ---------------- 
8903        HBase      红本价        740.0000000000   HBase                          8903        740.0000000000
8905        HBase      红本价        917.0000000000   HBase                          8905        917.0000000000
8929        T1003      1KV        .0000000000      HBase+T1003+T1005              8929        200.0000000000
8929        T1005      1000欧姆     .0000000000      HBase+T1003+T1005              8929        200.0000000000
8929        HBase      红本价        200.0000000000   HBase+T1003+T1005              8929        200.0000000000
8931        HBase      红本价        288.0000000000   HBase+T1003+T1005              8931        288.0000000000
8931        T1005      1000欧姆     .0000000000      HBase+T1003+T1005              8931        288.0000000000
8931        T1003      1KV        .0000000000      HBase+T1003+T1005              8931        288.0000000000
8933        HBase      红本价        450.0000000000   HBase                          8933        450.0000000000
8935        HBase      红本价        600.0000000000   HBase                          8935        600.0000000000
9015        HBase      红本价        740.0000000000   (HBase+T1003+T1006)*T1009      9015        8140.0000000000
9015        T1003      1KV        .0000000000      (HBase+T1003+T1006)*T1009      9015        8140.0000000000
9015        T1006      100欧姆      .0000000000      (HBase+T1003+T1006)*T1009      9015        8140.0000000000
9015        T1009      红绿色        11.0000000000    (HBase+T1003+T1006)*T1009      9015        8140.0000000000
9017        T1009      红绿色        11.0000000000    HBase                          9017        965.0000000000
9017        T1005      1000欧姆     .0000000000      HBase                          9017        965.0000000000
9017        T1003      1KV        .0000000000      HBase                          9017        965.0000000000
9017        HBase      红本价        965.0000000000   HBase                          9017        965.0000000000
9019        HBase      红本价        1270.0000000000  HBase                          9019        1270.0000000000
9021        HBase      红本价        1790.0000000000  HBase                          9021        1790.0000000000
9029        HBase      红本价        200.0000000000   HBase                          9029        200.0000000000
9031        HBase      红本价        288.0000000000   HBase                          9031        288.0000000000

som@'' 2017-08-23

打赏
举报

引用 6 楼 jinfengyiye 的回复:



DECLARE @t TABLE(material NVARCHAR(100),project NVARCHAR(100),VALUE NUMERIC(10,2) ,formula NVARCHAR(100));
INSERT INTO @t (material,project,value,formula) VALUES
('a','质量',10,''),
('a','体积',20,''),
('a','标液',30,''),
('a','含量',0,'体积*标液*0.27801/质量'),
('b','质量1',0.25,''),
('b','m',10,''),
('b','含量',0,'5*m/质量1')

;WITH cte AS (
SELECT a.*,b.project AS pb,b.VALUE AS bv FROM @t a JOIN @t b ON a.material=b.material AND a.value=0 AND b.value<>0
)
SELECT x.material,x.formula,(SELECT ','+y.pb+':'+CAST(y.bv AS VARCHAR(100)) FROM cte y WHERE y.material=x.material FOR XML PATH('')) FROM cte x GROUP BY x.material,x.formula
/*
a	体积*标液*0.27801/质量	,质量:10.00,体积:20.00,标液:30.00
b	5*m/质量1	,质量1:0.25,m:10.00
*/

前面复制错了是这个

CREATE TABLE [6tb] ([fitemid] [int],[FID_V] [nvarchar](10),[FName] [nvarchar](10),[FValue] [numeric](14,10),[FExpression] [nvarchar](30))
INSERT INTO [6tb]
SELECT '8903','HBase','红本价','740.0000000000','HBase' UNION ALL
SELECT '8905','HBase','红本价','917.0000000000','HBase' UNION ALL
SELECT '8929','T1003','1KV','.0000000000','HBase+T1003+T1005' UNION ALL
SELECT '8929','T1005','1000欧姆','.0000000000','HBase+T1003+T1005' UNION ALL
SELECT '8929','HBase','红本价','200.0000000000','HBase+T1003+T1005' UNION ALL
SELECT '8931','HBase','红本价','288.0000000000','HBase+T1003+T1005' UNION ALL
SELECT '8931','T1005','1000欧姆','.0000000000','HBase+T1003+T1005' UNION ALL
SELECT '8931','T1003','1KV','.0000000000','HBase+T1003+T1005' UNION ALL
SELECT '8933','HBase','红本价','450.0000000000','HBase' UNION ALL
SELECT '8935','HBase','红本价','600.0000000000','HBase' UNION ALL
SELECT '9015','HBase','红本价','740.0000000000','(HBase+T1003+T1006)*T1009' UNION ALL
SELECT '9015','T1003','1KV','.0000000000','(HBase+T1003+T1006)*T1009' UNION ALL
SELECT '9015','T1006','100欧姆','.0000000000','(HBase+T1003+T1006)*T1009' UNION ALL
SELECT '9015','T1009','红绿色','11.0000000000','(HBase+T1003+T1006)*T1009' UNION ALL
SELECT '9017','T1009','红绿色','11.0000000000','HBase' UNION ALL
SELECT '9017','T1005','1000欧姆','.0000000000','HBase' UNION ALL
SELECT '9017','T1003','1KV','.0000000000','HBase' UNION ALL
SELECT '9017','HBase','红本价','965.0000000000','HBase' UNION ALL
SELECT '9019','HBase','红本价','1270.0000000000','HBase' UNION ALL
SELECT '9021','HBase','红本价','1790.0000000000','HBase' UNION ALL
SELECT '9029','HBase','红本价','200.0000000000','HBase' UNION ALL
SELECT '9031','HBase','红本价','288.0000000000','HBase'
 
CREATE TABLE GGGUU(fitemid INT,R NUMERIC(14,10))
GO 
CREATE FUNCTION GetVb(@Fitemid AS INT,@FExpression nvarchar(100) )
RETURNS nvarchar(100)
AS 
BEGIN
    DECLARE @sql varchar(100)
    SELECT @FExpression=replace(@FExpression,FId_V,ltrim(FValue)) FROM [6tb] WHERE fitemid=@Fitemid
    SET @sql='INSERT GGGUU select '+LTRIM(@Fitemid)+','+@FExpression+' G'
    RETURN @sql
END
GO 
EXEC MASTER.dbo.xp_execresultset 'SELECT dbo.getVB(Fitemid,FExpression) FROM [6tb]',N'test'
 
SELECT * FROM [6TB] A INNER JOIN (SELECT DISTINCT * FROM GGGUU) B
ON A.fitemid=B.fitemid
 
--RESULT
/*fitemid     FID_V      FName      FValue           FExpression                    fitemid     R                
----------- ---------- ---------- ---------------- ------------------------------ ----------- ---------------- 
8903        HBase      红本价        740.0000000000   HBase                          8903        740.0000000000
8905        HBase      红本价        917.0000000000   HBase                          8905        917.0000000000
8929        T1003      1KV        .0000000000      HBase+T1003+T1005              8929        200.0000000000
8929        T1005      1000欧姆     .0000000000      HBase+T1003+T1005              8929        200.0000000000
8929        HBase      红本价        200.0000000000   HBase+T1003+T1005              8929        200.0000000000
8931        HBase      红本价        288.0000000000   HBase+T1003+T1005              8931        288.0000000000
8931        T1005      1000欧姆     .0000000000      HBase+T1003+T1005              8931        288.0000000000
8931        T1003      1KV        .0000000000      HBase+T1003+T1005              8931        288.0000000000
8933        HBase      红本价        450.0000000000   HBase                          8933        450.0000000000
8935        HBase      红本价        600.0000000000   HBase                          8935        600.0000000000
9015        HBase      红本价        740.0000000000   (HBase+T1003+T1006)*T1009      9015        8140.0000000000
9015        T1003      1KV        .0000000000      (HBase+T1003+T1006)*T1009      9015        8140.0000000000
9015        T1006      100欧姆      .0000000000      (HBase+T1003+T1006)*T1009      9015        8140.0000000000
9015        T1009      红绿色        11.0000000000    (HBase+T1003+T1006)*T1009      9015        8140.0000000000
9017        T1009      红绿色        11.0000000000    HBase                          9017        965.0000000000
9017        T1005      1000欧姆     .0000000000      HBase                          9017        965.0000000000
9017        T1003      1KV        .0000000000      HBase                          9017        965.0000000000
9017        HBase      红本价        965.0000000000   HBase                          9017        965.0000000000
9019        HBase      红本价        1270.0000000000  HBase                          9019        1270.0000000000
9021        HBase      红本价        1790.0000000000  HBase                          9021        1790.0000000000
9029        HBase      红本价        200.0000000000   HBase                          9029        200.0000000000
9031        HBase      红本价        288.0000000000   HBase                          9031        288.0000000000

som@'' 2017-08-23

打赏
举报

引用 6 楼 jinfengyiye 的回复:



DECLARE @t TABLE(material NVARCHAR(100),project NVARCHAR(100),VALUE NUMERIC(10,2) ,formula NVARCHAR(100));
INSERT INTO @t (material,project,value,formula) VALUES
('a','质量',10,''),
('a','体积',20,''),
('a','标液',30,''),
('a','含量',0,'体积*标液*0.27801/质量'),
('b','质量1',0.25,''),
('b','m',10,''),
('b','含量',0,'5*m/质量1')

;WITH cte AS (
SELECT a.*,b.project AS pb,b.VALUE AS bv FROM @t a JOIN @t b ON a.material=b.material AND a.value=0 AND b.value<>0
)
SELECT x.material,x.formula,(SELECT ','+y.pb+':'+CAST(y.bv AS VARCHAR(100)) FROM cte y WHERE y.material=x.material FOR XML PATH('')) FROM cte x GROUP BY x.material,x.formula
/*
a	体积*标液*0.27801/质量	,质量:10.00,体积:20.00,标液:30.00
b	5*m/质量1	,质量1:0.25,m:10.00
*/

谢谢能用这种方式实现吗

WITH cte AS (
SELECT a.*,b.project AS pb,b.VALUE AS bv FROM @t a JOIN @t b ON a.material=b.material AND a.value=0 AND b.value<>0
)
SELECT x.material,x.formula,(SELECT ','+y.pb+':'+CAST(y.bv AS VARCHAR(100)) FROM cte y WHERE y.material=x.material FOR XML PATH('')) FROM cte x GROUP BY x.material,x.formula

用replace实现

Pact_Alice 2017-08-23

打赏
举报

CREATE TABLE #Temp_1
(
ID INT,
Material VARCHAR(200),
Project VARCHAR(200),
Calcule VARCHAR(200),
Data VARCHAR(20),
Formula VARCHAR(200),
Tolerance VARCHAR(200),
Standrad VARCHAR(200)
)

INSERT INTO #Temp_1
SELECT 1,'硫酸亚铁_含量','质量','否',10,'','','' UNION ALL
SELECT 1,'硫酸亚铁_含量','体积','否',20,'','','' UNION ALL
SELECT 1,'硫酸亚铁_含量','标液','否',30,'','','' UNION ALL
SELECT 1,'硫酸亚铁_含量','含量','否',0,'体积*标液*0.27801/质量','≤0.4%',''

DECLARE @sql VARCHAR(max)
SET @sql='select Material,max(Formula)Formula '
SELECT @sql=@sql+','+QUOTENAME(project)+N'= max(case project when '+QUOTENAME(project,N'''')+N' then data end)'
FROM #Temp_1
GROUP BY project

exec(@sql+N' from #Temp_1 GROUP BY Material')

下班了，就先上面吧

mywisdom88 2017-08-23

打赏
举报

楼主,要的,是不是和我这个差不多.. -- http://bbs.csdn.net/topics/392207318 -- 终于消化了,自己按模样写了个,算是自己看懂的,但发现1个问题,科目公式表中,不能引用其他计算结果. -- 如 20001,sqrt(sqrt({10002}+{10003}),不能写成 sqrt({10001}),望各大神完善指点,SQL2000测试 -- 科目明细表 if object_id('tempdb..#科目明细') is not null drop table #科目明细 create table #科目明细(code varchar(8),num numeric(12,2),dt datetime,fcode varchar(10)) insert into #科目明细(code,num,dt) select '10003',3000,'2017/08/01' union all select '10002',1000,'2017/08/01' union all select '10004',1200,'2017/08/02' union all select '10003',2000,'2017/08/02' union all select '10004',5000,'2017/08/03' -- 科目公式表 if object_id('tempdb..#科目公式') is not null drop table #科目公式 create table #科目公式(id int identity(1,1),code varchar(8),formula varchar(1000),result numeric(12,2)) insert into #科目公式(code,formula) select '10001','{10002}+{10003}' union all select '20001','sqrt({10002}+{10003})' union all select '20002','({10002}-{10004}+10001)*0.05' -- 科目汇总表,增加1个字段 fcode = {code},把科目编号用{}括起来,区分公式中的数值"常数" if object_id('tempdb..#科目汇总') is not null drop table #科目汇总 create table #科目汇总(id int identity(1,1),code varchar(8),num numeric(12,2),dt datetime,fcode varchar(10)) insert into #科目汇总(code,num,dt,fcode) select code,sum(num) as num,max(dt) as dt,'{'+max(code)+'}' as fcode from #科目明细 group by code select * from #科目公式 -- 替换科目公式的 fcode = num declare @List_id int set @List_id =1 while @List_id is not null begin update k0 set k0.formula=replace(k0.formula,k1.fcode,k1.num) from #科目公式 k0,#科目汇总 k1 where charindex(k1.fcode,k0.formula)>0 select @List_id =min(k1.id) from #科目汇总 k1,#科目公式 k0 where charindex(k1.fcode,k0.formula)>0 end select * from #科目公式 --按公式类别遍历 declare @i int,@n int,@formula varchar(1000),@sql varchar(8000) select @i=min(id),@n=max(id) from #科目公式 while (@i<=@n) begin select @formula=formula from #科目公式 where id=@i set @sql='update #科目公式 set result='+@formula+' where formula='''+@formula+'''' --生成脚本 exec(@sql) --执行脚本，可以print看效果 set @i=@i+1 end select * from #科目公式

gw6328 2017-08-23

打赏
举报



DECLARE @t TABLE(material NVARCHAR(100),project NVARCHAR(100),VALUE NUMERIC(10,2) ,formula NVARCHAR(100));
INSERT INTO @t (material,project,value,formula) VALUES
('a','质量',10,''),
('a','体积',20,''),
('a','标液',30,''),
('a','含量',0,'体积*标液*0.27801/质量'),
('b','质量1',0.25,''),
('b','m',10,''),
('b','含量',0,'5*m/质量1')

;WITH cte AS (
SELECT a.*,b.project AS pb,b.VALUE AS bv FROM @t a JOIN @t b ON a.material=b.material AND a.value=0 AND b.value<>0
)
SELECT x.material,x.formula,(SELECT ','+y.pb+':'+CAST(y.bv AS VARCHAR(100)) FROM cte y WHERE y.material=x.material FOR XML PATH('')) FROM cte x GROUP BY x.material,x.formula
/*
a	体积*标液*0.27801/质量	,质量:10.00,体积:20.00,标液:30.00
b	5*m/质量1	,质量1:0.25,m:10.00
*/

学海无涯-回头是岸 2017-08-23

打赏
举报

引用 4 楼 qq_37889561 的回复:

是这样吗》？但是公式太多而且是动态的，这样不行啊

所以要用计算符号分割 Formula公式

som@'' 2017-08-23

打赏
举报

引用 3 楼 baidu_36457652 的回复:

首先用计算符号分割 Formula公式，然后通过一个值一个值 select出来再套用公式

select 
(select data from  FrmMaterial  where Project='体积' and Material='硫酸亚铁_含量')*
(select data from  FrmMaterial  where Project='标液'  and Material='硫酸亚铁_含量')*
0.27801/(select data from  FrmMaterial  where Project='质量'  and Material='硫酸亚铁_含量')
FROM FrmMaterial  where  project='含量' GROUP BY project

是这样吗》？但是公式太多而且是动态的，这样不行啊

加载更多回复（3）

SQL 计算公式

相关推荐