第一张表 -> 第二张表 -> 第三张表
单号 安装工 单号 安装工组合 安装工组合 单数
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
现在我眼前的数据是第一张,而现在需要得到第三张表的情况,拜托各位了!
安装工组合,
单数=count(*)
from
(select
单号,
min(安装工)||decode(max(安装工),min(安装工),'',' '||max(安装工)) as 安装工组合
from
表1
group by
单号)
group by
安装工组合
SQL> create table testt(
2 id varchar2(10),
3 name varchar2(10)
4 )
5 /Table createdSQL> insert into testt values('1','A');1 row insertedSQL> insert into testt values('1','B');1 row insertedSQL> insert into testt values('2','A');1 row insertedSQL> insert into testt values('2','C');1 row insertedSQL> insert into testt values('3','B');1 row insertedSQL> insert into testt values('4','A');1 row insertedSQL> insert into testt values('4','C');1 row insertedSQL> insert into testt values('5','B');1 row insertedSQL> insert into testt values('5','A');1 row insertedSQL> select * from testt;ID NAME
---------- ----------
1 A
1 B
2 A
2 C
3 B
4 A
4 C
5 B
5 A9 rows selectedSQL>
SQL> select
2 name,
3 count(*)
4 from
5 (select
6 id,
7 min(name)||decode(max(name),min(name),'',' '||max(name)) as name
8 from
9 testt
10 group by
11 id)
12 group by
13 name
14 /NAME COUNT(*)
--------------------- ----------
A B 2
A C 2
B 1SQL>
id,
min(name)||decode(max(name),min(name),'',' '||max(name)) as NAME
FROM
testt
group BY
id这个是中间结果!
那想再请问一下,现在的情况就是说,一个单号对应的可能不止二个安装工,而且具体几个也是不定的,那这种情况下,该怎么处理呢,呼,先谢谢了!
单号 安装工 单号 安装工组合 安装工组合 单数
1 A 1 A B C A B C 2
1 B 2 A C A C 2
1 C 3 B B 1
2 A 4 A C
2 C 5 B A C
3 B
4 A
4 C
5 B
5 A
5 C
当然也非常谢谢楼上几位!
TABLE B:create table T2(ID NUMBER,STAFF_ALL VARCHAR2(20))
TABLE C:create table T3(STAFF_ALL VARCHAR2(20),COUNTS NUMBER)
create or replace procedure hhmjenny as
v_id number ;
v_id_old number :=-1;
v_staff varchar2(20) :='';
v_staff_new varchar2(20) :='';
cursor cur_id is select id,staff from t1 order by id;
begin
OPEN cur_id;
LOOP
FETCH cur_id
INTO v_id,v_staff;
EXIT WHEN cur_id%NOTFOUND;
if v_id = v_id_old then
v_staff_new:=v_staff_new||v_staff;
else insert into T2 SELECT v_id_old,v_staff_new FROM DUAL;
commit; v_id_old := v_id;
v_staff_new :=v_staff;
end if;
END LOOP;
insert into T2 SELECT v_id_old,v_staff_new FROM DUAL;
commit; delete from t2 where id = -1; COMMIT;
end;验证了一下好像暂时没问题呵呵!表3很简单:
select staff_all,count(*) from t2 group by staff_all;
应为:cursor cur_id is select id,staff from t1 order by id ,staff ;
太匆忙了 ,你再检查一下
SQL> execute hhmjenny;PL/SQL procedure successfully completedSQL> select * from t2; ID STAFF_ALL
---------- --------------------
1 ABC
2 AC
3 B
4 AC
5 ABCSQL> select staff_all,count(*) from t2 group by staff_all;STAFF_ALL COUNT(*)
-------------------- ----------
ABC 2
AC 2
B 1
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
安装工组合
SQL> select gp, count(*) from (
2 select min(col2)||max(col2) gp, col1 from a
3 group by col1
4 having count(*)=2)
5 group by gp;GP COUNT(*)
---------- ----------
AB 2
AC 2
表二
SQL> select min(col2)||max(col2) gp, col1 from a
2 group by col1
3 having count(*)=2;GP COL1
---------- ----------
AB 1
AC 2
AC 4
AB 5
我現在把我在那篇貼子中用過的方法給你再貼一遍:--先創建一個函數
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 (select 安装工组合,SUM(单数) 单数 FROM 表三 GROUP BY 安装工组合) t2
ON get_chars(t1.单号)=t2.安装工组合