22,210
社区成员
发帖
与我相关
我的任务
分享SQL 计算公式
Material：物品名称
Project：检测项
Calculate：是否计算
Data：值
Formula：公式
Tolerance：显示
Standard：显示
用SQL计算出 需要计算的值
游标
replace替换公式,执行公式.
...全文
请发表友善的回复…
发表回复
gw6328 2017-08-28
- 打赏
- 举报
还是写不出来，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
- 打赏
- 举报
还是写不出来，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
后面你还是要执行一次啊。所以我建议还是程序处理吧。 [/quote]
使用了很多合并重复行的 函数 都是报错，有什么好的办法吗？
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;
听雨停了 2017-08-25
- 打赏
- 举报
我用你的 测试出来了，怎么把值放到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
- 打赏
- 举报
前面 复制错了 是这个
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
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
- 打赏
- 举报
还是写不出来，replace不会用。
som@'' 2017-08-24
- 打赏
- 举报
我用你的 测试出来了，怎么把值放到Data里面去呢？
xiaoxiangqing 2017-08-24
- 打赏
- 举报
只能用动态语句了，最好保存的时候，就计算好
som@'' 2017-08-23
- 打赏
- 举报
感谢，能不能用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
- 打赏
- 举报
前面 复制错了 是这个
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
- 打赏
- 举报
谢谢 能用这种方式 实现吗
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
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
- 打赏
- 举报
所以要用计算符号分割 Formula公式
som@'' 2017-08-23
- 打赏
- 举报
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）
