34,591
社区成员
发帖
与我相关
我的任务
分享
select a.cp,b.num1*a.num,b.num2*a.num,b.num3*a.num from a,b
where a.cp=b.cp
改两个字母也不会。。偶真不知道你能做什么了。
---------------------------------
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (fa INT,zp VARCHAR(3),cp VARCHAR(3),num INT)
INSERT INTO @tb1
SELECT 0,'001','1-1',10 UNION ALL
SELECT 1,'001','2-1',2 UNION ALL
SELECT 2,'001','3-1',10 UNION ALL
SELECT 0,'002','1-4',8 UNION ALL
SELECT 1,'002','2-6',10 UNION ALL
SELECT 2,'002','3-7',8
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (zp VARCHAR(3),num1 INT,num2 INT,num3 INT)
INSERT INTO @tb2
SELECT '001',10,500,400
--SQL查询如下:
SELECT
A.cp,
A.num*B.num1 AS num1,
A.num*B.num2 AS num2,
A.num*B.num3 AS num3
FROM @tb1 AS A
JOIN @tb2 AS B
ON A.zp=B.zp
/*
cp num1 num2 num3
---- ----------- ----------- -----------
1-1 100 5000 4000
2-1 20 1000 800
3-1 100 5000 4000
(3 行受影响)
*/
---------------------------------
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (fa INT,zp VARCHAR(3),cp VARCHAR(3),num INT)
INSERT INTO @tb1
SELECT 0,'001','1-1',10 UNION ALL
SELECT 1,'001','2-1',2 UNION ALL
SELECT 2,'001','3-1',10 UNION ALL
SELECT 0,'002','1-4',8 UNION ALL
SELECT 1,'002','2-6',10 UNION ALL
SELECT 2,'002','3-7',8
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (zp VARCHAR(3),num1 INT,num2 INT,num3 INT)
INSERT INTO @tb2
SELECT '001',10,500,400
--SQL查询如下:
SELECT
A.zp,
A.num*B.num1 AS num1,
A.num*B.num2 AS num2,
A.num*B.num3 AS num3
FROM @tb1 AS A
JOIN @tb2 AS B
ON A.zp=B.zp
/*
zp num1 num2 num3
---- ----------- ----------- -----------
001 100 5000 4000
001 20 1000 800
001 100 5000 4000
(3 行受影响)
*/