第一张表 -> 第二张表 -> 第三张表
单号 安装工 单号 安装工组合 安装工组合 单数
1 A 1 A B A B 2
1 B 2 A C A C 2
2 A 3 B B 1
2 C 4 A C
3 B 5 B A
4 A
4 C
5 B
5 A
现在我眼前的数据是第一张,而现在需要得到第三张表的情况,拜托各位了!
单号 安装工 单号 安装工组合 安装工组合 单数
1 A 1 A B A B 2
1 B 2 A C A C 2
2 A 3 B B 1
2 C 4 A C
3 B 5 B A
4 A
4 C
5 B
5 A
现在我眼前的数据是第一张,而现在需要得到第三张表的情况,拜托各位了!
FROM (SELECT a,
REPLACE (MAX (SYS_CONNECT_BY_PATH (b, ';')), ';', '') b
FROM (SELECT 单号 a, 安装工 b,
( ROW_NUMBER () OVER (ORDER BY 单号, 安装工)
+ DENSE_RANK () OVER (ORDER BY 单号)
) rn,
MIN (安装工) OVER (PARTITION BY 单号) c
FROM 表 a)
START WITH b = c
CONNECT BY rn - 1 = PRIOR rn
GROUP BY a)
GROUP BY b
安装工组合,
单数=count(*)
from
(select
单号,
min(安装工)||decode(max(安装工),min(安装工),'',' '||max(安装工)) as 安装工组合
from
表1
group by
单号)
group by
安装工组合
CREATE OR REPLACE FUNCTION SumString(
I_TableName IN VARCHAR2 ,
I_GroupColName IN VARCHAR2 ,
I_ResultColName IN VARCHAR2 ,
I_GroupColValue IN VARCHAR2 ,
I_Separator IN VARCHAR2
)
RETURN VARCHAR2 IS
TYPE T_Cur IS REF CURSOR ;
C_Cur T_Cur ;
V_Sql VARCHAR2(2000) ;
V_Result VARCHAR2(2000) ;
V_Tmp VARCHAR2(200) ;
V_Cnt NUMBER := 0 ;
BEGIN
V_Result := '' ;
V_Sql := 'SELECT '|| I_ResultColName ||' FROM '|| I_TableName ||' WHERE '|| I_GroupColName || ' = '''|| I_GroupColValue || ''' ORDER BY '|| I_ResultColName ;
OPEN C_Cur FOR V_Sql ;
LOOP
FETCH C_Cur INTO V_Tmp ;
EXIT WHEN C_Cur%NOTFOUND ;
IF V_Cnt = 0 THEN
V_Result := V_Tmp ;
ELSE
V_Result := V_Result || I_Separator || V_Tmp ;
END IF ;
V_Cnt := V_Cnt + 1 ;
END LOOP ;
CLOSE C_Cur ;
RETURN V_Result ;
END SUMSTRING;
--借助用户定义函数实现统计过程
SELECT
安装工组合,
单数 = COUNT(*)
FROM
(SELECT
单号,
SumString( '表1', '单号' , '安装工' , '单号' , ' ' ) 安装工组合
FROM
表1
GROUP BY
单号)
GROUP BY
安装工组合
Create or replace function get_Chars
(DH in varchar2)
RETURN varchar2
IS
chr_AZG varchar2(2000);
chr_AZG1 varchar2(10);
cursor cur_cur1 is select 安裝工 from 表一 where 單號=DH;
begin
open cur_cur1;
loop
fetch cur_cur1 into chr_AZG1;
exit when cur_cur1%NOTFOUND;
chr_AZG:=chr_AZG||chr_AZG1;
end loop;
Return(chr_AZG);
end;--二、再查詢並獲得結果:
select distinct 安裝工組合,單數 FROM 表一 t1 JOIN 表三 t2 ON get_chars(t1.單號)=t2.安裝工組合;
上面代碼已經過我的測試,是符合樓主的要求的
select distinct 安裝工組合,單數 FROM
表一 t1 JOIN (select 安裝工組合,SUM(單數) 單數 FROM 表三 GROUP BY 安裝工組合) t2
ON get_chars(t1.單號)=t2.安裝工組合;看了樓上幾位的代碼,覺得都是有點復雜,還是我的比較簡單!
FROM (SELECT a,
REPLACE (MAX (SYS_CONNECT_BY_PATH (b, ';')), ';', '') b
FROM (SELECT 单号 a, 安装工 b,
( ROW_NUMBER () OVER (ORDER BY 单号, 安装工)
+ DENSE_RANK () OVER (ORDER BY 单号)
) rn,
MIN (安装工) OVER (PARTITION BY 单号) c
FROM 表 a)
START WITH b = c
CONNECT BY rn - 1 = PRIOR rn
GROUP BY a)
GROUP BY b
2 (
3 STR VARCHAR2(30000),
4 STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LINK) RETURN NUMBER,
5 MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LINK,
6 VALUE IN VARCHAR2) RETURN NUMBER,
7 MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK,
8 RETURNVALUE OUT VARCHAR2,
9 FLAGS IN NUMBER) RETURN NUMBER,
10 MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_LINK,
11 CTX2 IN T_LINK) RETURN NUMBER
12 );Type createdSQL>
SQL> CREATE OR REPLACE TYPE BODY T_LINK IS
2 STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LINK) RETURN NUMBER IS
3 BEGIN SCTX := T_LINK(NULL); RETURN ODCICONST.SUCCESS; END;
4
5 MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LINK, VALUE IN VARCHAR2) RETURN NUMBER IS
6 BEGIN SELF.STR := SELF.STR || VALUE; RETURN ODCICONST.SUCCESS; END;
7
8 MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER)
9 RETURN NUMBER IS BEGIN RETURNVALUE := SELF.STR; RETURN ODCICONST.SUCCESS; END;
10
11 MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_LINK, CTX2 IN T_LINK) RETURN NUMBER IS
12 BEGIN NULL; RETURN ODCICONST.SUCCESS; END;
13 END;
14 /Type body createdSQL> CREATE OR REPLACE FUNCTION F_LINK(P_STR VARCHAR2) RETURN VARCHAR2 AGGREGATE USING T_LINK;
2 /Function createdSQL> drop table test;Table droppedSQL>
SQL> CREATE TABLE TEST(单号 NUMBER, 安装工 VARCHAR2(20));Table createdSQL> INSERT INTO TEST VALUES(1, 'A');1 row insertedSQL> INSERT INTO TEST VALUES(1, 'B');1 row insertedSQL> INSERT INTO TEST VALUES(2, 'A');1 row insertedSQL> INSERT INTO TEST VALUES(2, 'C');1 row insertedSQL> INSERT INTO TEST VALUES(3, 'B');1 row insertedSQL> INSERT INTO TEST VALUES(4, 'A');1 row insertedSQL> INSERT INTO TEST VALUES(4, 'C');1 row insertedSQL> INSERT INTO TEST VALUES(5, 'B');1 row insertedSQL> INSERT INTO TEST VALUES(5, 'A');1 row insertedSQL> INSERT INTO TEST VALUES(6, 'D');1 row insertedSQL> INSERT INTO TEST VALUES(6, 'C');1 row insertedSQL> INSERT INTO TEST VALUES(6, 'A');1 row insertedSQL> COMMIT;Commit completeSQL> SELECT 安装工组合, COUNT(*) 单数
2 FROM (SELECT 单号, F_LINK(安装工) 安装工组合 FROM
3 (SELECT * FROM test ORDER BY 单号, 安装工)
4 GROUP BY 单号)
5 GROUP BY 安装工组合
6 /安装工组合 单数
------------------------------------------------------------ ----------
AB 2
AC 2
ACD 1
B 1