Я новичок с оракулом PL / SQL,
прямо сейчас с помощью оракула sqldeveloper,
мне нужна помощь для моей домашней работы,
как сделать так, чтобы столбец с номерами 2, 3, 4, 5, 6, ... рассчитывал на основе первого значения столбца, DISTINCT OWNER?
Может кто-нибудь мне помочь? или предложить мне какой-нибудь намек?
SELECT DISTINCT OWNER as SCHEMA,
(SELECT COUNT(DISTINCT OBJECT_NAME) FROM DBA_OBJECTS where object_type = 'TABLE') "TABLE",
(SELECT COUNT(DISTINCT OBJECT_NAME) FROM DBA_OBJECTS where object_type = 'VIEW') "VIEW",
(SELECT COUNT(DISTINCT OBJECT_NAME) FROM DBA_OBJECTS where object_type = 'MATERIALIZED_VIEW') "MATERIALIZED VIEW",
(SELECT COUNT(DISTINCT OBJECT_NAME) FROM DBA_OBJECTS where object_type = 'INDEX') "INDEX",
(SELECT COUNT(DISTINCT OBJECT_NAME) FROM DBA_OBJECTS where object_type = 'SEQUENCE') "SEQUENCE",
(SELECT COUNT(DISTINCT OBJECT_NAME) FROM DBA_OBJECTS where object_type = 'TYPE') "TYPE",
(SELECT COUNT(DISTINCT OBJECT_NAME) FROM DBA_OBJECTS where object_type = 'SYNONYM') "SYNONYM",
(SELECT COUNT(DISTINCT OBJECT_NAME) FROM DBA_OBJECTS where object_type = 'DATABASE LINK') "DATABASE LINK",
(SELECT COUNT(DISTINCT OBJECT_NAME) FROM DBA_OBJECTS where object_type = 'LOB') "LOB",
(SELECT COUNT(DISTINCT OBJECT_NAME) FROM DBA_OBJECTS where object_type = 'JOB') "JOB",
(SELECT COUNT(DISTINCT OBJECT_NAME) FROM DBA_OBJECTS where object_type = 'TRIGGER') "TRIGGER",
(SELECT COUNT(DISTINCT OBJECT_NAME) FROM DBA_OBJECTS where object_type = 'PROCEDURE') "PROCEDURE",
(SELECT COUNT(DISTINCT OBJECT_NAME) FROM DBA_OBJECTS where object_type = 'FUNCTION') "FUNCTION",
(SELECT COUNT(DISTINCT OBJECT_NAME) FROM DBA_OBJECTS where object_type = 'PACKAGE') "PACKAGE",
(SELECT COUNT(DISTINCT OBJECT_NAME) FROM DBA_OBJECTS where object_type = 'PACKAGE BODY') "PACKAGE BODY"
FROM DBA_OBJECTS
WHERE OWNER IN ('BERKAS', 'BIAYAPERJALANAN', 'DASHBOARD', 'DIKLAT', 'FLATKKP', 'INVENT',
'KKPWEB', 'KKPWEBAUDITOR', 'KONTEN', 'LP2B', 'LPPB', 'MITRAKERJA', 'MOBILE', 'OLTPUSER', 'ONEDEE', 'PEGAWAI', 'PERENCANAAN',
'PETA', 'PM', 'PNBP', 'PNBPTRAIN', 'SIMPEG_1703', 'SIMPEG_2702', 'SIPT', 'SKP', 'SURAT', 'SURATBDG', 'TEMATIK', 'TIMKENDALI',
'WEBAPIDATA', 'WILAYAH') ORDER BY OWNER;
это неправильно, мне нужно количество n другой столбец на основе первого столбца, что мне делать?
Всего 2 ответа
Вы должны использовать условное агрегирование следующим образом:
SELECT OWNER as SCHEMA,
COALESCE(SUM(CASE WHEN object_type = 'TABLE' THEN 1 END), 0) "TABLE",
COALESCE(SUM(CASE WHEN object_type = 'VIEW' THEN 1 END), 0) "VIEW",
COALESCE(SUM(CASE WHEN object_type = 'MATERIALIZED_VIEW' THEN 1 END), 0) "MATERIALIZED VIEW",
COALESCE(SUM(CASE WHEN object_type = 'INDEX' THEN 1 END), 0) "INDEX",
COALESCE(SUM(CASE WHEN object_type = 'SEQUENCE' THEN 1 END), 0) "SEQUENCE",
COALESCE(SUM(CASE WHEN object_type = 'TYPE' THEN 1 END), 0) "TYPE",
COALESCE(SUM(CASE WHEN object_type = 'SYNONYM' THEN 1 END), 0) "SYNONYM",
COALESCE(SUM(CASE WHEN object_type = 'DATABASE LINK' THEN 1 END), 0) "DATABASE LINK",
COALESCE(SUM(CASE WHEN object_type = 'LOB' THEN 1 END), 0) "LOB",
COALESCE(SUM(CASE WHEN object_type = 'JOB' THEN 1 END), 0) "JOB",
COALESCE(SUM(CASE WHEN object_type = 'TRIGGER' THEN 1 END), 0) "TRIGGER",
COALESCE(SUM(CASE WHEN object_type = 'PROCEDURE' THEN 1 END), 0) "PROCEDURE",
COALESCE(SUM(CASE WHEN object_type = 'FUNCTION' THEN 1 END), 0) "FUNCTION",
COALESCE(SUM(CASE WHEN object_type = 'PACKAGE' THEN 1 END), 0) "PACKAGE",
COALESCE(SUM(CASE WHEN object_type = 'PACKAGE BODY' THEN 1 END), 0) "PACKAGE BODY"
FROM DBA_OBJECTS
WHERE OWNER IN ('BERKAS', 'BIAYAPERJALANAN', 'DASHBOARD', 'DIKLAT', 'FLATKKP', 'INVENT',
'KKPWEB', 'KKPWEBAUDITOR', 'KONTEN', 'LP2B', 'LPPB', 'MITRAKERJA', 'MOBILE', 'OLTPUSER', 'ONEDEE', 'PEGAWAI', 'PERENCANAAN',
'PETA', 'PM', 'PNBP', 'PNBPTRAIN', 'SIMPEG_1703', 'SIMPEG_2702', 'SIPT', 'SKP', 'SURAT', 'SURATBDG', 'TEMATIK', 'TIMKENDALI',
'WEBAPIDATA', 'WILAYAH')
GROUP BY OWNER
ORDER BY OWNER;
Ура !!
Используйте PIVOT
:
SELECT *
FROM (
SELECT owner AS schema,
object_name,
object_type
FROM DBA_OBJECTS
WHERE OWNER IN (
'BERKAS', 'BIAYAPERJALANAN', 'DASHBOARD', 'DIKLAT', 'FLATKKP', 'INVENT',
'KKPWEB', 'KKPWEBAUDITOR', 'KONTEN', 'LP2B', 'LPPB', 'MITRAKERJA', 'MOBILE',
'OLTPUSER', 'ONEDEE', 'PEGAWAI', 'PERENCANAAN', 'PETA', 'PM', 'PNBP',
'PNBPTRAIN', 'SIMPEG_1703', 'SIMPEG_2702', 'SIPT', 'SKP', 'SURAT',
'SURATBDG', 'TEMATIK', 'TIMKENDALI', 'WEBAPIDATA', 'WILAYAH'
)
)
PIVOT ( COUNT( DISTINCT OBJECT_NAME ) FOR OBJECT_TYPE IN (
'TABLE' AS "TABLE",
'VIEW' AS "VIEW",
'MATERIALIZED_VIEW' AS "MATERIALIZED VIEW",
'INDEX' AS "INDEX",
'SEQUENCE' AS "SEQUENCE"
-- rest of your object types.
) )
ORDER BY schema;