Пример немного утрирован. Если вам покажеться, что он не соответствует практике, то это ничего.
Есть покупатель. К покупателю могут быть привязаны
1) счета
2) платежи
В таблицах счета и платежи есть ссылка на покупателя.
нужно вывести так
Покупатель Счет Платеж
Пример
Петров СН1 ПЛ1
Петров СН2
Петров СН3
т.е у одного покупателя может быть разное кол-во счетов и платежей. Нужно вывести в одной колонке все счета, в другой все платежи.
Например 1 Петрова 3 счета и 1 платеж нужно вывести 3 строчки. Если у петрова 3 счета и 4 платежа, то нужно вывести 4 строки.
Здравствуйте, Аноним, Вы писали:
А>Пример немного утрирован. Если вам покажеться, что он не соответствует практике, то это ничего.
select *
from pok, sch, pla
where pok.id_sch = sch.id(+)
and pok.id_pla = pla.id(+)
или
select *
from sch left outer join pok on pok.id_sch = sch.id
right outer join pla on pok.id_pla = pla.id
Здравствуйте, Аноним, Вы писали:
Можно. Для примера я выведу к таблицам столбцы "числовых полей" и строковых полей
SQL> ed
Wrote file afiedt.buf
1 with
2 number_columns as (
3 select owner, table_name, column_name,
4 row_number() over (partition by table_name order by column_name) col_number
5 from dba_tab_columns
6 where data_type = 'NUMBER' and table_name in ( 'DBA_USERS', 'DBA_OBJECTS' )),
7 string_columns as (
8 select owner, table_name, column_name,
9 row_number() over (partition by table_name order by column_name) col_number
10 from dba_tab_columns
11 where data_type = 'VARCHAR2' and table_name in ( 'DBA_USERS', 'DBA_OBJECTS' ))
12 select
13 coalesce ( nc.table_name, sc.table_name ) "table:",
14 nc.column_name "number fields:",
15 sc.column_name "string fields:"
16 from
17 number_columns nc full outer join string_columns sc
18 on ( nc.table_name = sc.table_name and nc.owner = sc.owner and
19 nc.col_number = sc.col_number )
20* order by 1, 2, 3
SQL> /
table: number fields: string fields:
-------------------- -------------------- --------------------
DBA_OBJECTS DATA_OBJECT_ID GENERATED
DBA_OBJECTS OBJECT_ID OBJECT_NAME
DBA_OBJECTS OBJECT_TYPE
DBA_OBJECTS OWNER
DBA_OBJECTS SECONDARY
DBA_OBJECTS STATUS
DBA_OBJECTS SUBOBJECT_NAME
DBA_OBJECTS TEMPORARY
DBA_OBJECTS TIMESTAMP
DBA_USERS USER_ID ACCOUNT_STATUS
DBA_USERS DEFAULT_TABLESPACE
DBA_USERS EXTERNAL_NAME
DBA_USERS INITIAL_RSRC_CONSUME
DBA_USERS PASSWORD
DBA_USERS PROFILE
DBA_USERS TEMPORARY_TABLESPACE
DBA_USERS USERNAME
17 rows selected.
Здравствуйте, Аноним, Вы писали:
А>А если будет не 2 а 5 таблиц такое можно сделать??
Да собственно нет особой разницы
SQL> with
2 tables_list as (
3 select owner, view_name table_name from dba_views
4 where view_name in ( 'MVIEW_WORKLOAD', 'USER_TAB_COLUMNS', 'KU$_COLUMN_VIEW' )),
5 number_columns as (
6 select tc.owner, tc.table_name, column_name,
7 row_number() over (partition by tc.table_name order by column_name) col_number
8 from dba_tab_columns tc, tables_list tl
9 where data_type = 'NUMBER' and tc.owner = tl.owner and tc.table_name = tl.table_name ),
10 string_columns as (
11 select tc.owner, tc.table_name, column_name,
12 row_number() over (partition by tc.table_name order by column_name) col_number
13 from dba_tab_columns tc, tables_list tl
14 where data_type = 'VARCHAR2' and tc.owner = tl.owner and tc.table_name = tl.table_name ),
15 date_columns as (
16 select tc.owner, tc.table_name, column_name,
17 row_number() over (partition by tc.table_name order by column_name) col_number
18 from dba_tab_columns tc, tables_list tl
19 where data_type = 'DATE' and tc.owner = tl.owner and tc.table_name = tl.table_name),
20 other_columns as (
21 select tc.owner, tc.table_name, column_name, data_type,
22 row_number() over (partition by tc.table_name order by column_name) col_number
23 from dba_tab_columns tc, tables_list tl
24 where data_type not in ( 'DATE', 'NUMBER', 'VARCHAR2' ) and tc.owner = tl.owner and tc.table_name = tl.table_name )
25 select
26 case when length ( s."number fields:" || s."string fields:" || s."date fields:" || s."other fields:" ) > 0
27 then s."table:"
28 else '- - - - - - - -'
29 end "table:",
30 s."number fields:", s."string fields:", s."date fields:", s."other fields:"
31 from (
32 select
33 coalesce ( nc.table_name, sc.table_name, dc.table_name, oc.table_name ) "table:",
34 nc.column_name "number fields:",
35 sc.column_name "string fields:",
36 dc.column_name "date fields:",
37 case when oc.column_name is not null
38 then oc.column_name || ' (' || oc.data_type || ')'
39 else null
40 end "other fields:"
41 from
42 number_columns nc full outer join string_columns sc
43 on ( nc.table_name = sc.table_name and nc.owner = sc.owner and
44 nc.col_number = sc.col_number )
45 full outer join date_columns dc
46 on ( nc.table_name = dc.table_name and nc.owner = dc.owner and
47 nc.col_number = dc.col_number )
48 full outer join other_columns oc
49 on ( nc.table_name = oc.table_name and nc.owner = oc.owner and
50 nc.col_number = oc.col_number )
51 order by 1, 2, 3, 4, 5 ) s
52 group by grouping sets (
53 (s."table:", s."number fields:", s."string fields:", s."date fields:", s."other fields:"),
54 (s."table:"))
55 order by s."table:"
56 /
table: number fields: string fields: date fields: other fields:
-------------------- -------------------- -------------------- -------------------- -------------------------
KU$_COLUMN_VIEW CHARSETFORM ATTRNAME SPARE6 CON (KU$_CONSTRAINT0_T)
KU$_COLUMN_VIEW CHARSETID DEFAULT_VAL LOBMD (KU$_LOB_T)
KU$_COLUMN_VIEW COL_NUM NAME OIDINDEX (KU$_OIDINDEX_T)
KU$_COLUMN_VIEW DEFLENGTH SPARE4 OPQMD (KU$_OPQTYPE_T)
KU$_COLUMN_VIEW FIXEDSTORAGE SPARE5 TYPEMD (KU$_COLTYPE_T)
KU$_COLUMN_VIEW INTCOL_NUM
KU$_COLUMN_VIEW IS_NULL
KU$_COLUMN_VIEW LENGTH
KU$_COLUMN_VIEW OBJ_NUM
KU$_COLUMN_VIEW OFFSET
KU$_COLUMN_VIEW PRECISION_NUM
KU$_COLUMN_VIEW PROPERTY
KU$_COLUMN_VIEW SCALE
KU$_COLUMN_VIEW SEGCOLLENGTH
KU$_COLUMN_VIEW SEGCOL_NUM
KU$_COLUMN_VIEW SPARE1
KU$_COLUMN_VIEW SPARE2
KU$_COLUMN_VIEW SPARE3
KU$_COLUMN_VIEW TYPE_NUM
- - - - - - - -
MVIEW_WORKLOAD CARDINALITY APPLICATION IMPORT_TIME QUERY (LONG)
MVIEW_WORKLOAD FREQUENCY OWNER LASTUSE
MVIEW_WORKLOAD PRIORITY
MVIEW_WORKLOAD QUERYID
MVIEW_WORKLOAD RESPONSETIME
MVIEW_WORKLOAD RESULTSIZE
MVIEW_WORKLOAD WORKLOADID
- - - - - - - -
USER_TAB_COLUMNS AVG_COL_LEN CHARACTER_SET_NAME LAST_ANALYZED DATA_DEFAULT (LONG)
USER_TAB_COLUMNS CHAR_COL_DECL_LENGTH CHAR_USED HIGH_VALUE (RAW)
USER_TAB_COLUMNS CHAR_LENGTH COLUMN_NAME LOW_VALUE (RAW)
USER_TAB_COLUMNS COLUMN_ID DATA_TYPE
USER_TAB_COLUMNS DATA_LENGTH DATA_TYPE_MOD
USER_TAB_COLUMNS DATA_PRECISION DATA_TYPE_OWNER
USER_TAB_COLUMNS DATA_SCALE DATA_UPGRADED
USER_TAB_COLUMNS DEFAULT_LENGTH GLOBAL_STATS
USER_TAB_COLUMNS DENSITY NULLABLE
USER_TAB_COLUMNS NUM_BUCKETS TABLE_NAME
USER_TAB_COLUMNS NUM_DISTINCT USER_STATS
USER_TAB_COLUMNS NUM_NULLS V80_FMT_IMAGE
USER_TAB_COLUMNS SAMPLE_SIZE
- - - - - - - -
42 rows selected
