27,579
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE #A (S_NO VARCHAR(10),QTY VARCHAR(10),C_NO VARCHAR(10))
INSERT INTO #A SELECT 'A',20,1
INSERT INTO #A SELECT 'B',30,2
INSERT INTO #A SELECT 'B',40,3
INSERT INTO #A SELECT 'C',20,2
INSERT INTO #A SELECT 'C',50,3
CREATE TABLE #B (C_NO VARCHAR(10),C_NAME VARCHAR(10))
INSERT INTO #B SELECT 1,'CHENGPIN'
INSERT INTO #B SELECT 2,'YUANLIAO1'
INSERT INTO #B SELECT 3,'YUANLIAO2'
SELECT (CASE WHEN SORT_NO=1 THEN C_NAME ELSE C_NAME+'得到A的数量' END)C_NAME,S_NO,QTY
FROM
(SELECT 1 SORT_NO,B.C_NAME,A.S_NO,A.QTY
FROM #B B LEFT JOIN #A A ON A.C_NO=B.C_NO
WHERE C_NAME IN ('YUANLIAO1','YUANLIAO2')
UNION ALL
SELECT 2 SORT_NO,B.C_NAME,'',CASE WHEN (SELECT QTY FROM #A WHERE C_NO=B.C_NO AND S_NO='B')/3>(SELECT QTY FROM #A WHERE C_NO=B.C_NO AND S_NO='C')/5
THEN ROUND((SELECT QTY FROM #A WHERE C_NO=B.C_NO AND S_NO='C')/5,0) ELSE ROUND(( SELECT QTY FROM #A WHERE C_NO=B.C_NO AND S_NO='B')/3,0) END
FROM #B B
WHERE C_NAME IN ('YUANLIAO1','YUANLIAO2'))A
ORDER BY C_NAME,SORT_NO
DROP TABLE #A,#B
CREATE TABLE #A (S_NO VARCHAR(10),QTY VARCHAR(10),C_NO VARCHAR(10))
INSERT INTO #A SELECT 'A',20,1
INSERT INTO #A SELECT 'B',30,2
INSERT INTO #A SELECT 'B',40,3
INSERT INTO #A SELECT 'C',20,2
INSERT INTO #A SELECT 'C',50,3
CREATE TABLE #B (C_NO VARCHAR(10),C_NAME VARCHAR(10))
INSERT INTO #B SELECT 1,'CHENGPIN'
INSERT INTO #B SELECT 2,'YUANLIAO1'
INSERT INTO #B SELECT 3,'YUANLIAO2'
SELECT (CASE WHEN SORT_NO=1 THEN C_NAME ELSE C_NAME+'得到A的数量' END)C_NAME,S_NO,QTY
FROM
(SELECT 1 SORT_NO,B.C_NAME,A.S_NO,A.QTY
FROM #B B LEFT JOIN #A A ON A.C_NO=B.C_NO
WHERE C_NAME IN ('YUANLIAO1','YUANLIAO2')
UNION ALL
SELECT 2 SORT_NO,B.C_NAME,'',CASE WHEN (SELECT QTY FROM #A WHERE C_NO=B.C_NO AND S_NO='B')/3>(SELECT QTY FROM #A WHERE C_NO=B.C_NO AND S_NO='C')/5
THEN ROUND((SELECT QTY FROM #A WHERE C_NO=B.C_NO AND S_NO='C')/5,0) ELSE ROUND(( SELECT QTY FROM #A WHERE C_NO=B.C_NO AND S_NO='B')/3,0) END
FROM #B B
WHERE C_NAME IN ('YUANLIAO1','YUANLIAO2'))A
ORDER BY C_NAME,SORT_NO
DROP TABLE #A,#B
接分.....
接分.....
接分.....