Head(主表)
------------
FieldA FieldB
B001 E2008
B002 R2008
Detail(明细表)
-------------
FieldA FieldB FieldC
B001 A P
B001 A E
B001 B T
B001 C Y
B002 A R
B002 C WHead.FieldA是主键,Head.FieldA与Detail.FieldA进行关联。
先对Head.FieldA和Detail.FieldB同时进行分组,这样取出的结果的如下:
B001 E2008 A P
B001 E2008 B T
B001 E2008 C T
B002 R2008 A R
B002 R2008 C W进行分组之后,B001有三条明细记录,B002有两条明细记录。
最终我想形成如下结果,主要是Head.FieldB的变化:
Head.FieldA Head.FieldB Detail.FieldB Detail.FieldC
-------------------------------------------------------------
B001 E2008001 A P
B001 E2008002 B T
B001 E2008003 C T
B002 R2008001 A R
B002 R2008002 C W请各位大虾帮帮忙.如何写SQL语句.
------------
FieldA FieldB
B001 E2008
B002 R2008
Detail(明细表)
-------------
FieldA FieldB FieldC
B001 A P
B001 A E
B001 B T
B001 C Y
B002 A R
B002 C WHead.FieldA是主键,Head.FieldA与Detail.FieldA进行关联。
先对Head.FieldA和Detail.FieldB同时进行分组,这样取出的结果的如下:
B001 E2008 A P
B001 E2008 B T
B001 E2008 C T
B002 R2008 A R
B002 R2008 C W进行分组之后,B001有三条明细记录,B002有两条明细记录。
最终我想形成如下结果,主要是Head.FieldB的变化:
Head.FieldA Head.FieldB Detail.FieldB Detail.FieldC
-------------------------------------------------------------
B001 E2008001 A P
B001 E2008002 B T
B001 E2008003 C T
B002 R2008001 A R
B002 R2008002 C W请各位大虾帮帮忙.如何写SQL语句.
-------------
FieldA FieldB FieldC
B001 A P
B001 A E
B001 B T
B001 C Y
B002 A R
B002 C W
怎么没了?
B001 E2008 B T
B001 E2008 C T
B002 R2008 A R
B002 R2008 C W
你这个结果怎么出来的
主要是b001 有什么规律?
给你个例子:
SELECT DEPTNO,
EMPNO,
EMPNO ||
LPAD(ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY ENAME), 3, '0')
FROM SCOTT.EMP;
输出:
10 7782 7782001
10 7839 7839002
10 7934 7934003
20 7876 7876001
20 7902 7902002
20 7566 7566003
20 7788 7788004
20 7369 7369005
30 7499 7499001
30 7698 7698002
30 7900 7900003
30 7654 7654004
30 7844 7844005
30 7521 7521006
B001 E2008 B T
B001 E2008 C T 第一个,有两条a,后面取了P,可以理解为取max(FieldC)
不过C的数据怎么解释,原来是Y,现在变成了T
我已经写到一半了,和你的一样,突然发现楼主DETAIL表里的数据有处理过,规律不明
LEFT JOIN Head B
ON A.FieldA=B.FieldA
GROUP BY A.FieldA,A.FieldBHead是一张主表. Detail表是一张明细表.
Head中的B001只有一个.
而对应的Detail中的B001可能有多条记录.
LEFT JOIN Head B
ON A.FieldA=B.FieldA
GROUP BY A.FieldA,A.FieldBHead是一张主表. Detail表是一张明细表.
Head中的B001只有一个.
而对应的Detail中的B001可能有多条记录.
a.fieldb
|| LPAD (ROW_NUMBER () OVER (PARTITION BY a.fielda ORDER BY b.fieldb),
3,
'0'
) fieldb_new,
b.fieldb, b.fieldc
FROM head a,
(SELECT fielda, fieldb, MAX (fieldc) fieldc
FROM detail
GROUP BY fielda, fieldb) b
WHERE a.fielda = b.fielda
INSERT INTO HEAD VALUES('B001','E2008');
INSERT INTO HEAD VALUES('B002','R2008');
CREATE TABLE Detail(FieldA VARCHAR2(10),FieldB VARCHAR2(10),FieldC VARCHAR2(10));
INSERT INTO DETAIL VALUES('B001','A','P');
INSERT INTO DETAIL VALUES('B001','A','E');
INSERT INTO DETAIL VALUES('B001','B','T');
INSERT INTO DETAIL VALUES('B001','C','Y');
INSERT INTO DETAIL VALUES('B002','A','R');
INSERT INTO DETAIL VALUES('B002','C','W'); SELECT T1.FieldA,
REPLACE(T1.FieldB||to_char(row_number() over(partition by T1.FieldA,T1.FieldB ORDER BY T1.FieldB),'000'),' ') FieldB,
T2.FieldB FieldC,MIN(FieldC) FieldD
FROM Head T1,Detail T2
WHERE T1.FieldA=T2.FieldA GROUP BY T1.FieldA,T1.FieldB,T2.FieldB;FIELDA FIELDB FIELDC FIELDD
---------- -------------- ---------- ----------
B001 E2008001 A E
B001 E2008002 B T
B001 E2008003 C Y
B002 R2008001 A R
B002 R2008002 C W合乎要求吗?
SQL> SELECT HE.FIELDA,
2 HE.FIELDB || LPAD(ROW_NUMBER() OVER(PARTITION BY HE.FIELDA ORDER BY D1.FIELDB),3,'0') "FIELDB",
3 D1.FIELDB,
4 D1.FIELDC
5 FROM DETAIL D1,
6 HEAD HE
7 WHERE D1.FIELDA = HE.FIELDA
8 AND NOT EXISTS (
9 SELECT 1
10 FROM DETAIL D2
11 WHERE D1.FIELDA = D2.FIELDA
12 AND D1.FIELDB = D2.FIELDB
13 AND D1.FIELDC < D2.FIELDC
14 );FIELDA FIELDB FIELDB FIELDC
------ ----------- ------ ------
B001 E2008001 A P
B001 E2008002 B T
B001 E2008003 C Y
B002 R2008001 A R
B002 R2008002 C WSQL>