27,582
社区成员




UPDATE dbo.b
SET b2 =
(
SELECT TOP 1 a2 FROM dbo.a WHERE b1 LIKE a1 + '/%'
) /
(
SELECT TOP 1 a2 FROM dbo.a WHERE b1 LIKE '%/' + a1
);
CREATE TABLE a(a1 varchar(40),a2 numeric(18,2))
/*a1->指标名称,a2->指标名称对应数值内容 */
INSERT INTO a(a1,a2)VALUES('应发工资','1000.00')
INSERT INTO a(a1,a2)VALUES('销售收入','20000.00')
INSERT INTO a(a1,a2)VALUES('研发支出','22300.00')
INSERT INTO a(a1,a2)VALUES('年度财务费用','12300.00')
INSERT INTO a(a1,a2)VALUES('超期应收','6300.00')
INSERT INTO a(a1,a2)VALUES('新产品销售收入','13000.00')
CREATE TABLE b(b1 varchar(40),b2 numeric(18,2))
/*b1->公式名称,b2->对应公式核算结果 */
INSERT INTO b(b1,b2)VALUES('应发工资/销售收入',null)
INSERT INTO b(b1,b2)VALUES('销售收入/新产品销售收入',null)
INSERT INTO b(b1,b2)VALUES('销售收入/研发支出',null)
INSERT INTO b(b1,b2)VALUES('销售收入/年度财务费用',null);
WITH cte AS (
SELECT left(b1,CHARINDEX('/',b1)-1) AS f_b1,
right(b1,len(b1)-CHARINDEX('/',b1)) AS l_b1,
b.b1
FROM b
)
SELECT c.*,a.a2,b.a2,cast(a.a2/b.a2 AS NUMERIC(12,4)) AS b2 FROM cte AS c
INNER JOIN a AS a ON c.f_b1=a.a1
INNER JOIN a AS b ON c.l_b1=b.a1
update b
set b2 = (select a2 from a where a.a1 = substring(b.b1,0, charindex('/',b.b1, 0)))
/
(select a2 from a where a.a1 = substring(b.b1, charindex('/',b.b1, 0) + 1 , 30))
go
select * from b
go