表A
ID id2 suliang bz
A-001 01-001 2 bz1
A-001 01-002 2 bz1 A-001 01-003 2 bz2
A-001 01-004 2 bz2 A-001 01-005 2 bz3
A-001 01-006 2 bz3
表B
ID bz1 bz2 bz3
A-001 3 4 5
如何得出结果
表C
id2 suliang
01-001 2*3=6
01-002 2*3=6 01-003 2*4=8
01-004 2*4=8 01-005 2*5=10
01-006 2*5=10
ID id2 suliang bz
A-001 01-001 2 bz1
A-001 01-002 2 bz1 A-001 01-003 2 bz2
A-001 01-004 2 bz2 A-001 01-005 2 bz3
A-001 01-006 2 bz3
表B
ID bz1 bz2 bz3
A-001 3 4 5
如何得出结果
表C
id2 suliang
01-001 2*3=6
01-002 2*3=6 01-003 2*4=8
01-004 2*4=8 01-005 2*5=10
01-006 2*5=10
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (ID VARCHAR(5),id2 VARCHAR(6),suliang INT,bz VARCHAR(3))
INSERT INTO @tb1
SELECT 'A-001','01-001',2,'bz1' UNION ALL
SELECT 'A-001','01-002',2,'bz1' UNION ALL
SELECT 'A-001','01-003',2,'bz2' UNION ALL
SELECT 'A-001','01-004',2,'bz2' UNION ALL
SELECT 'A-001','01-005',2,'bz3' UNION ALL
SELECT 'A-001','01-006',2,'bz3'
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (ID VARCHAR(5),bz1 INT,bz2 INT,bz3 INT)
INSERT INTO @tb2
SELECT 'A-001',3,4,5--SQL查询如下:SELECT
A.id2,
CASE WHEN A.bz='bz1' THEN A.suliang*B.bz1
WHEN A.bz='bz2' THEN A.suliang*B.bz2
WHEN A.bz='bz3' THEN A.suliang*B.bz3 END AS suliang
FROM @tb1 AS A
JOIN @tb2 AS B
ON A.ID=B.ID/*
id2 suliang
------ -----------
01-001 6
01-002 6
01-003 8
01-004 8
01-005 10
01-006 10(6 行受影响)*/
FROM A JOIN (
SELECT ID,'BZ1' AS BZ,BZ1 AS SL FROM B
UNION ALL
SELECT ID,'BZ2',BZ2 FROM B
UNION ALL
SELECT ID,'BZ3',BZ3 FROM B
) B
ON A.ID=B.ID
表B中可能会出现更多的列
表B
ID bz1 bz2 bz3 bz4 bz5 bz6 bz7
我试试liangCK兄弟的代码
SELECT A.DI,SULIANG*SL AS SULIANG
FROM A JOIN (
SELECT ID,'BZ1' AS BZ,BZ1 AS SL FROM B
UNION ALL
SELECT ID,'BZ2',BZ2 FROM B
UNION ALL
SELECT ID,'BZ3',BZ3 FROM B
) B
ON A.ID=B.ID AND A.BZ=B.BZ --***