Все технические форумы на одном сайте Удобный поиск информации с популярных форумов в одном месте
Вопрос: Задачку задали хитрую

--ИСХОДНЫЕ ДАННЫЕ-------------------------------------
DECLARE @tbl TABLE (dt DATETIME, pow FLOAT)
DECLARE @cal TABLE (dt DATETIME)

INSERT INTO @tbl ([dt],[pow])
SELECT Cast('27.01.2013 00:00:00' AS DATETIME), 100 UNION ALL
SELECT Cast('27.01.2013 00:10:10' AS DATETIME), 100.5 UNION ALL
SELECT Cast('27.01.2013 00:10:55' AS DATETIME), 100.7 UNION ALL
SELECT Cast('27.01.2013 00:11:00' AS DATETIME), 120.1 UNION ALL
SELECT Cast('27.01.2013 00:15:00' AS DATETIME), 150.7 UNION ALL
SELECT Cast('27.01.2013 00:16:30' AS DATETIME), 151.5 UNION ALL
SELECT Cast('27.01.2013 00:16:40' AS DATETIME), 152.5 UNION ALL
SELECT Cast('27.01.2013 00:17:00' AS DATETIME), 155.7 UNION ALL
SELECT Cast('27.01.2013 00:17:31' AS DATETIME), 155.7 UNION ALL
SELECT Cast('27.01.2013 00:19:00' AS DATETIME), 154.0 UNION ALL
SELECT Cast('27.01.2013 00:20:30' AS DATETIME), 169.5936 union all
SELECT Cast('27.01.2013 00:20:34' AS DATETIME), 171.7632 union all
SELECT Cast('27.01.2013 00:22:40' AS DATETIME), 171.1008 union all
SELECT Cast('27.01.2013 00:23:23' AS DATETIME), 170.496 union all
SELECT Cast('27.01.2013 00:24:13' AS DATETIME), 170.9856 union all
SELECT Cast('27.01.2013 00:25:23' AS DATETIME), 169.2 union all
SELECT Cast('27.01.2013 00:26:23' AS DATETIME), 170.1024 union all
SELECT Cast('27.01.2013 00:27:23' AS DATETIME), 169.4304 union all
SELECT Cast('27.01.2013 00:28:54' AS DATETIME), 169.8624 union all
SELECT Cast('27.01.2013 00:29:32' AS DATETIME), 170.1312 union all
SELECT Cast('27.01.2013 00:40:00' AS DATETIME), 171.36 union all
SELECT Cast('27.01.2013 00:40:10' AS DATETIME), 170.1888 union all
SELECT Cast('27.01.2013 00:40:50' AS DATETIME), 170.5056 union all
SELECT Cast('27.01.2013 00:43:40' AS DATETIME), 172.1568 union all
SELECT Cast('27.01.2013 00:44:30' AS DATETIME), 171.3504 union all
SELECT Cast('27.01.2013 00:45:54' AS DATETIME), 169.4112 union all
SELECT Cast('27.01.2013 00:46:00' AS DATETIME), 171.3696


INSERT INTO @cal ([dt])

SELECT Cast('27.01.2013 00:11:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:12:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:13:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:14:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:15:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:16:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:17:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:18:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:19:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:20:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:21:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:22:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:23:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:24:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:25:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:26:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:27:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:28:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:29:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:30:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:31:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:32:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:33:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:34:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:35:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:36:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:37:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:38:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:39:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:40:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:41:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:42:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:43:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:44:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:45:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:46:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:47:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:48:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:49:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:50:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:51:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:52:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:53:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:54:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:55:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:56:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:57:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:58:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 00:59:00' AS DATETIME) UNION ALL
SELECT Cast('27.01.2013 01:00:00' AS DATETIME)
--ИСХОДНЫЕ ДАННЫЕ КОНЕЦ---------------------------------


--В таблице @tbl данные со счетчиков в определенный момент времени
--Таблица @cal это календарь

--ЗАДАНИЕ №1 Нужно построить запрос, который выведет нагрузку на каждую минуту времени, без пустых значений


Я так понимаю главная таблица это @cal, из нее выводим все возможные значения поминутно цепляя таблицу @tbl. Т.е. примерно такой результат.


27.01.2013 00:00:00 100
27.01.2013 00:01:00 100
27.01.2013 00:02:00 100
27.01.2013 00:03:00 100
27.01.2013 00:04:00 100
27.01.2013 00:05:00 100
27.01.2013 00:06:00 100
27.01.2013 00:07:00 100
27.01.2013 00:08:00 100
27.01.2013 00:09:00 100
27.01.2013 00:10:00 100.5
Ответ:
CheaterX
Minamoto,

Спасибо большое! Я просто хотел чтобы без cross apply, самому нравится этот метод, но он тормозной бывает порой...



Индексы надо создавать правильно и будет он летать
Вопрос: Нужна помощь в развороте таблицы

Имеем пример набора данных и 11-й оракл, т.е. можно использовать pivot.

+ Набор данных

select 'M11' id_m, 'T1' id_t, 111 cnt,10.11 price,11.11 price2,12.11 price3 from dual
union
select 'M11' id_m, 'T3' id_t, 311 cnt,20.11 price,21.11 price2,22.11 price3 from dual
union
select 'M11' id_m, 'T3' id_t, 311 cnt,30.11 price,31.11 price2,32.11 price3 from dual
union
select 'M11' id_m, 'T4' id_t, 411 cnt,40.11 price,41.11 price2,42.11 price3 from dual
union
select 'M11' id_m, 'T5' id_t, 511 cnt,50.11 price,51.11 price2,52.11 price3 from dual

union

select 'M12' id_m, 'T1' id_t, 112 cnt,10.12 price,11.12 price2,12.12 price3 from dual
union                                                                      
select 'M12' id_m, 'T3' id_t, 312 cnt,20.12 price,21.12 price2,22.12 price3 from dual
union                                                                      
select 'M12' id_m, 'T3' id_t, 312 cnt,30.12 price,31.12 price2,32.12 price3 from dual
union                                                                      
select 'M12' id_m, 'T4' id_t, 412 cnt,40.12 price,41.12 price2,42.12 price3 from dual
union                                                                      
select 'M12' id_m, 'T5' id_t, 512 cnt,50.12 price,51.12 price2,52.12 price3 from dual

union

select 'M13' id_m, 'T1' id_t, 113 cnt,10.13 price,11.13 price2,12.13 price3 from dual
union                                                                      
select 'M13' id_m, 'T3' id_t, 313 cnt,20.13 price,21.13 price2,22.13 price3 from dual
union                                                                      
select 'M13' id_m, 'T3' id_t, 313 cnt,30.13 price,31.13 price2,32.13 price3 from dual
union                                                                      
select 'M13' id_m, 'T4' id_t, 413 cnt,40.13 price,41.13 price2,42.13 price3 from dual
union                                                                      
select 'M13' id_m, 'T5' id_t, 513 cnt,50.13 price,51.13 price2,52.13 price3 from dual

union

select 'M14' id_m, 'T1' id_t, 114 cnt,10.14 price,11.14 price2,12.14 price3 from dual
union                                                                      
select 'M14' id_m, 'T3' id_t, 314 cnt,20.14 price,21.14 price2,22.14 price3 from dual
union                                                                      
select 'M14' id_m, 'T3' id_t, 314 cnt,30.14 price,31.14 price2,32.14 price3 from dual
union                                                                      
select 'M14' id_m, 'T4' id_t, 414 cnt,40.14 price,41.14 price2,42.14 price3 from dual
union                                                                      
select 'M14' id_m, 'T5' id_t, 514 cnt,50.14 price,51.14 price2,52.14 price3 from dual

union

select 'M15' id_m, 'T1' id_t, 115 cnt,10.15 price,11.15 price2,12.15 price3 from dual
union                                                                      
select 'M15' id_m, 'T3' id_t, 315 cnt,20.15 price,21.15 price2,22.15 price3 from dual
union                                                                      
select 'M15' id_m, 'T3' id_t, 315 cnt,30.15 price,31.15 price2,32.15 price3 from dual
union                                                                      
select 'M15' id_m, 'T4' id_t, 415 cnt,40.15 price,41.15 price2,42.15 price3 from dual
union                                                                      
select 'M15' id_m, 'T5' id_t, 515 cnt,50.15 price,51.15 price2,52.15 price3 from dual;


Получаем такую таблицу

ID_M ID_T CNT PRICE PRICE2 PRICE3
M11 T1 111 10.11 11.11 12.11
M11 T3 311 20.11 21.11 22.11
M11 T3 311 30.11 31.11 32.11
M11 T4 411 40.11 41.11 42.11
M11 T5 511 50.11 51.11 52.11
M12 T1 112 10.12 11.12 12.12
M12 T3 312 20.12 21.12 22.12
M12 T3 312 30.12 31.12 32.12
M12 T4 412 40.12 41.12 42.12
M12 T5 512 50.12 51.12 52.12
M13 T1 113 10.13 11.13 12.13
M13 T3 313 20.13 21.13 22.13
M13 T3 313 30.13 31.13 32.13
M13 T4 413 40.13 41.13 42.13
M13 T5 513 50.13 51.13 52.13
M14 T1 114 10.14 11.14 12.14
M14 T3 314 20.14 21.14 22.14
M14 T3 314 30.14 31.14 32.14
M14 T4 414 40.14 41.14 42.14
M14 T5 514 50.14 51.14 52.14
M15 T1 115 10.15 11.15 12.15
M15 T3 315 20.15 21.15 22.15
M15 T3 315 30.15 31.15 32.15
M15 T4 415 40.15 41.15 42.15
M15 T5 515 50.15 51.15 52.15

Ее нужно развернуть в таком виде:

ID_M-> M11 M12 M13 M14 M15
ID_T CNT PRICE PRICE2 PRICE3 CNT PRICE PRICE2 PRICE3 CNT PRICE PRICE2 PRICE3 CNT PRICE PRICE2 PRICE3 CNT PRICE PRICE2 PRICE3
T1 111 10.11 11.11 12.11 112 10.12 11.12 12.12 113 10.13 11.13 12.13 114 10.14 11.14 12.14 115 10.15 11.15 12.15
T3 311 20.11 21.11 22.11 312 20.12 21.12 22.12 313 20.13 21.13 22.13 314 20.14 21.14 22.14 315 20.15 21.15 22.15
T3 311 30.11 31.11 32.11 312 30.12 31.12 32.12 313 30.13 31.13 32.13 314 30.14 31.14 32.14 315 30.15 31.15 32.15
T4 411 40.11 41.11 42.11 412 40.12 41.12 42.12 413 40.13 41.13 42.13 414 40.14 41.14 42.14 415 40.15 41.15 42.15
T5 511 50.11 51.11 52.11 512 50.12 51.12 52.12 513 50.13 51.13 52.13 514 50.14 51.14 52.14 515 50.15 51.15 52.15

Пробовал создавать таблицу с помощью динамического SQL размножая в ширину по ID_M, приписывая именам колонок сам ID_M, а затем по ключу ID_T и динамическим именам колонок вставлял данные и это работает, но столкнулся с проблемой ограничения количества колонок таблицы равной 1000. С функцией PIVOT ранее не работал, но попытки чтения документации и разбора примеров что-то ни как не помогают. Прошу помощи владеющих этой функцией в совершенстве.
Ответ: Pivot оказался тоже ограничен 1000-ю колонками, а вариант с decode не ограничен ничем, так что я переделал отчет именно с decode и теперь вообще все круто работает.
Вопрос: Интерполяция с динамическим вычислением диапазонов

WITH t AS (SELECT 3 sm FROM dual UNION ALL
           SELECT 2000 sm FROM dual UNION ALL
           SELECT 17000 sm FROM dual UNION ALL
           SELECT 5000 sm FROM dual UNION ALL
           SELECT 300 sm FROM dual UNION ALL
           SELECT 50000 sm FROM dual UNION ALL
           SELECT 90000000 sm FROM dual UNION ALL
           SELECT 12000000 sm FROM dual),
     lims AS (SELECT 1 sum_from,   10000 sum_to FROM dual UNION ALL 
              SELECT 10001,        100000 FROM dual UNION ALL
              SELECT 100001,       1000000 FROM dual UNION ALL
              SELECT 1000001,      10000000 FROM dual UNION ALL
              SELECT 10000001,     100000000 FROM dual)
SELECT sum_from-1 as sum_from, sum_to, COUNT(*) cnt, SUM(sm) summa 
  FROM t, lims
 WHERE t.sm BETWEEN lims.sum_from AND lims.sum_to
 GROUP BY sum_from-1, sum_to           
 ORDER BY 1

SUM_FROM SUM_TO CNT SUMMA
0 10000 4 7303
10000 100000 2 67000
10000000 100000000 2 102000000

Есть ряд сумм и надо сложить их по диапазонам.
Если задавать диапазоны фиксировано, то идет избыточная информация.
Избыточная в том плане, что например суммы в первых двух строках на три порядка меньше сумм в третьей строке
и поэтому первые две строки можно было бы объединить, получив
SUM_FROM SUM_TO CNT SUMMA
0 100000 6 74303
10000000 100000000 2 102000000

То есть необходимо разбивать на диапазоны динамически в зависимости от того насколько велика разница между суммами.
Видимо, прежде надо выработать критерии этой самой разницы )
Типа разница на три порядка или на два и т.п. но возможно как-то более красиво можно?

зы слово интерполяция просто чтоб интересно было ))
Ответ: Egoр, да все именно так )
Elic, ну да, делаю примерно в этом же направлении, пока так:
WITH t AS (SELECT 30 sm FROM dual UNION ALL
           SELECT 2000 sm FROM dual UNION ALL
           SELECT 17000 sm FROM dual UNION ALL
           SELECT 5000 sm FROM dual UNION ALL
           SELECT 300 sm FROM dual UNION ALL
           SELECT 100020 sm FROM dual UNION ALL
           SELECT 500000 sm FROM dual UNION ALL           
           SELECT 50000 sm FROM dual UNION ALL
           SELECT 90000000 sm FROM dual UNION ALL
           SELECT 19000000000 sm FROM dual UNION ALL           
           SELECT 12000000 sm FROM dual),
     lims AS (SELECT POWER(10, LEVEL-1) sum_from,   
                     POWER(10, LEVEL) sum_to 
                FROM dual CONNECT BY LEVEL < 20)
SELECT sum_from,
       sum_to,
       cnt,
       summa,
       CASE WHEN ROUND(summa/LAG(sum_rol) OVER(ORDER BY sum_from))>10 THEN 1 ELSE 0 END lag_sum_rol
  FROM (SELECT sum_from,
               sum_to,
               cnt,
               summa,       
               SUM(summa) OVER(ORDER BY sum_from ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_rol
         FROM (SELECT sum_from sum_from, 
                      sum_to, 
                      COUNT(*) cnt, 
                      SUM(sm) summa
                 FROM t, lims
                WHERE t.sm BETWEEN lims.sum_from+1 AND lims.sum_to
                GROUP BY sum_from, sum_to           
                ORDER BY 1))

SUM_FROM SUM_TO CNT SUMMA LAG_SUM_ROL
10 100 1 30 0
100 1000 1 300 0
1000 10000 2 7000 1
10000 100000 2 67000 0
100000 1000000 2 600020 0
10000000 100000000 2 102000000 1
10000000000 100000000000 1 19000000000 1

И там где нули, надо схлопнуть.
То есть пока критерий такой (не уверен правда, что будет нормально):
если сумма накопительного итога по предыдущим диапазонам в 10 раз меньше суммы в текущей записи,
то предыдущие диапазоны схлопываем в один, но не все а именно предыдущие где LAG_SUM_ROL = 0.
Усложняется еще тем, что кроме сумм надо анализировать и количество записей, т.е. если например
большое число записей в диапазоне мелких сумм, то схлопывать их нельзя, надо показать это, что основное кол-во записей в таком-то диапазоне. То есть нужны как бы контрасты )
Relict_35
почитайте про WIDTH_BUCKET

Спасибо, гляну.
Вопрос: найти полное соответствие множеств с подгруппой

with c as 
  (select 10 as id, 21 as typ, 0 as period from dual
    union all
   select 10 as id, 22 as typ, 0 as period from dual
    union all
   select 10 as id, 23 as typ, 0 as period from dual
    union all
   select 10 as id, 21 as typ, 1 as period from dual
   union all
   select 10 as id, 21 as typ, 2 as period from dual
   union all
   select 10 as id, 21 as typ, 3 as period from dual
   union all
   select 10 as id, 21 as typ, 4 as period from dual
   union all
   select 10 as id, 22 as typ, 1 as period from dual
   union all
   select 10 as id, 22 as typ, 2 as period from dual
   union all
   select 10 as id, 22 as typ, 3 as period from dual
    union all
   select 10 as id, 22 as typ, 4 as period from dual
),
spl as  (
    select 0 as code, 0 as gr from dual
        union all
    select 1 as code, 1 as gr from dual
        union all  
    select 2 as code, 1 as gr from dual
        union all  
    select 3 as code, 1 as gr from dual
        union all      
    select 4 as code, 1 as gr from dual
        union all  
    select 5 as code, 5 as gr from dual
        union all  
    select 6 as code, 6 as gr from dual                                
)
  select * from (   
   select c.id, c.typ, c.period, t.code from c 
    full outer join ( select code from  spl
                        where gr in ( select gr from spl 
                                where code = c.period)) t
    on c.period = spl.code )
    where period is null or code is null;  


ORA-00904: "C"."PERIOD": invalid identifier

соответственно вышеуказанный запрос должен вернуть пустоту


Нужно для каждой связки id + typ убедиться, что множество вариантов из данной группы равно (как множество) элементам code группы.

те например для если у связки айди и тип есть период 1, то должны быть ещё 2, 3, 4 и ТОЛЬКО они.

на мой взгляд это решается через full outer join и отсеивание null, но мешает подзапрос.



а

+
with c as 
  (select 10 as id, 21 as typ, 0 as period from dual
    union all
   select 10 as id, 22 as typ, 0 as period from dual
    union all
   select 10 as id, 23 as typ, 0 as period from dual
    union all
   select 10 as id, 21 as typ, 1 as period from dual
   union all
   select 10 as id, 21 as typ, 2 as period from dual
   union all
   select 10 as id, 21 as typ, 3 as period from dual
   union all
   select 10 as id, 21 as typ, 4 as period from dual
   union all
   select 10 as id, 21 as typ, 1 as period from dual  -- ERROR!!! 
   union all
   select 10 as id, 22 as typ, 1 as period from dual
   union all
   select 10 as id, 22 as typ, 2 as period from dual
   union all
   select 10 as id, 22 as typ, 3 as period from dual
    union all
   select 10 as id, 22 as typ, 4 as period from dual
)


должен 10, 21 как лишний элемент
Ответ:
Hawker_1
listagg в качестве сворачивателя множества в трубочку подходит идеально, так что в целом SY прав
listagg безусловно хорош там, где его можно заюзать.
XMLAGG не очень хорош даже для работы с XML, а если говорить про агрегацию в общем случае - вообще ужасен.
Для общего случае подойдет либо свой агрегат либо collect + своя функция клеящая коллекцию.
Советующий использовать XMLAGG для конкатенации строк это почти как советующий использовать аналитику для агрегации.\
В плане разумности.
Hawker_1
от мультисетов я отказался ввиду тормознутости оных.
Смотря о каких мультисетах речь.
Если про операции с коллекциями в SQL - то действительно должна быть очень серьезная причина использовать их.
Если про cast + multiset - ты просто не умеешь их готовить.
Вопрос: Разнесение данных запроса на 4 ~ "равные группы" [oracle]

Всем привет

Ниже пример, который должен вывести 4 группы данных, которые необходимо поместить в 1,2,3 и 4 столбец некоторого отчета соответственно, но возникает небольшая проблемка с работой данного алгоритма, когда количество записей 2, то данные разносятся в 1 и 3 столбец.

Подскажите, пожалуйста, мб есть чуть другой алгоритм или как обрабатывать подобную ситуацию ?
Заранее спасибо.

with a as
  (select 1 p from dual union all
   select 22 p from dual union all
   select 4 p from dual union all
   select 44 p from dual union all
   select 2 p from dual union all
   select 16 p from dual union all
   select 7 p from dual union all
   select 99 p from dual union all
   select 100 p from dual
   ),
   b as (select rownum row_cnt , a.p from a) 
select ROW_CNT ROW_CNT2,p,1 lvl_1,lvl,ROW_CNT 
  from ( 
         select ROW_CNT,p,2 lvl 
           from b where b.row_cnt <= (select ceil(max(row_cnt)/2) max_row_cnt from b)
         union all 
         select ROW_CNT,p,4 lvl 
           from b where b.row_cnt > (select ceil(max(row_cnt)/2) max_row_cnt from b)
       ) sub1
where sub1.lvl = 2 
  and sub1.row_cnt <= (select ceil(max(row_cnt)/2) max_row_cnt 
                         from ( select ROW_CNT,p,2 lvl from b 
                                 where b.row_cnt <= (select ceil(max(row_cnt)/2) max_row_cnt from b)
                              )
                       )
 union all  
 select ROW_CNT ROW_CNT2,p,2 lvl_1,lvl,ROW_CNT
  from ( 
         select ROW_CNT,p,2 lvl 
           from b where b.row_cnt <= (select ceil(max(row_cnt)/2) max_row_cnt from b)
         union all 
         select ROW_CNT,p,4 lvl 
           from b where b.row_cnt > (select ceil(max(row_cnt)/2) max_row_cnt from b)
       ) sub1
where sub1.lvl = 2 
  and sub1.row_cnt > (select ceil(max(row_cnt)/2) max_row_cnt 
                         from ( select ROW_CNT,p,2 lvl from b 
                                 where b.row_cnt <= (select ceil(max(row_cnt)/2) max_row_cnt from b)
                              )
                       )                    

                       
  union all   
 select ROW_CNT2,p,3 lvl_1,lvl,ROW_CNT   
 from        
 (select ROWNUM ROW_CNT2,p,3 lvl_1,lvl,ROW_CNT
  from (  
         select ROW_CNT,p,4 lvl 
           from b where b.row_cnt > (select ceil(max(row_cnt)/2) max_row_cnt from b)
       ) sub1
where sub1.lvl = 4) s1
where  ROW_CNT2 <= (select ceil(max(row_cnt)/2) max_row_cnt  --2
                         from  ( select ROWNUM ROW_CNT,p,4 lvl 
                               from b where b.row_cnt > (select ceil(max(row_cnt)/2) max_row_cnt from b)
                               ) sub2 
                    )
  union all                      
  select ROW_CNT2,p,4 lvl_1,lvl,ROW_CNT   
 from        
 (select ROWNUM ROW_CNT2,p,3 lvl_1,lvl,ROW_CNT
  from (  
         select ROW_CNT,p,4 lvl 
           from b where b.row_cnt > (select ceil(max(row_cnt)/2) max_row_cnt from b)
       ) sub1
where sub1.lvl = 4) s2
where ROW_CNT2 > (select ceil(max(row_cnt)/2) max_row_cnt  --2
                         from  ( select ROWNUM ROW_CNT,p,4 lvl 
                               from b where b.row_cnt > (select ceil(max(row_cnt)/2) max_row_cnt from b)
                               ) sub2 
                       )                                                  


Результат работы

корректный результат
1  4  6  8
2  5  7  9
3


некорректный результат ( хотелось бы, чтобы эта запись выводилась во втором столбце )
1  null 2 null
Ответ:
Добрый Э - Эх
или ещё короче...
with
  a as
    (
      select 1 p from dual union all
      select 22 p from dual union all
      select 4 p from dual union all
      select 44 p from dual union all
      select 2 p from dual union all
      select 16 p from dual union all
      select 7 p from dual union all
      select 99 p from dual union all
      select 100 p from dual
   )
--
--
select max(decode(ntile_4,1,p)) as f1
     , max(decode(ntile_4,2,p)) as f2
     , max(decode(ntile_4,3,p)) as f3
     , max(decode(ntile_4,4,p)) as f4
  from (
         select v.*
              , row_number() over(partition by ntile_4 order by row_cnt) as rn_x
           from (
                  select p
                       , rownum as row_cnt
                       , ntile(4) over(order by rownum) as ntile_4
                   from a
                ) v
       ) v
 group by rn_x;


Согласен! К этому и пришел :-)
Вопрос: django останавливает Mysql

Здравствуйте, у меня виртуальный хостинг, на нем два сайта один php другой python. Здесь работает связка nginx + mysql. И в данном случае mysql работает только с php, но работал и с сайтом django python, но потом когда начались проблемы я его перевел на sqlite. А проблемы вот в чем когда я запуская сервер на котором работает djangо и не важно это manage.py runserver это тестовый сервер или uwsgi то при их запуске останавливается mysql, хотя сайт уже не использует мускл. Я понимаю что проблема в коде django, но я так думаю можно узнать причину остановки из логов mysql, поэтому обращаюсь сюда. Поэтому вылаживаю настройки и логи, буду признателен за помощь.
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port            = 3307
socket          = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3307
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1
#
# * Fine Tuning
#
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 20 #8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
max_connections        = 100   #
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 10M #1
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
general_log_file        = /var/log/mysql/mysql.log
general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#slow_query_log = 1
#slow_query_log_file = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id              = 1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
[mysqldump]
quick
quote-names
max_allowed_packet      = 36M #/16

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 36M #/16

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#


innodb_buffer_pool_size=4G
innodb_buffer_pool_instances=2
innodb_read_io_threads=12
innodb_write_io_threads=12
innodb_io_capacity=300
innodb_log_file_size=128M
innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table          = 1

!includedir /etc/mysql/conf.d/




root@crackkc:/var/log/mysql# cat error.log
2015-08-24 01:35:09 13904 [Note] Plugin 'FEDERATED' is disabled.
2015-08-24 01:35:09 13904 [ERROR] Function 'innodb' already exists
2015-08-24 01:35:09 13904 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2015-08-24 01:35:09 13904 [ERROR] Function 'federated' already exists
2015-08-24 01:35:09 13904 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2015-08-24 01:35:09 13904 [ERROR] Function 'blackhole' already exists
2015-08-24 01:35:09 13904 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2015-08-24 01:35:09 13904 [ERROR] Function 'archive' already exists
2015-08-24 01:35:09 13904 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
2015-08-24 01:35:09 13904 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-08-24 01:35:09 13904 [Note] InnoDB: The InnoDB memory heap is disabled
2015-08-24 01:35:09 13904 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-08-24 01:35:09 13904 [Note] InnoDB: Memory barrier is not used
2015-08-24 01:35:09 13904 [Note] InnoDB: Compressed tables use zlib 1.2.7
2015-08-24 01:35:09 13904 [Note] InnoDB: Using Linux native AIO
2015-08-24 01:35:09 13904 [Note] InnoDB: Not using CPU crc32 instructions
2015-08-24 01:35:09 13904 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-08-24 01:35:09 13904 [Note] InnoDB: Completed initialization of buffer pool
2015-08-24 01:35:09 13904 [Note] InnoDB: Highest supported file format is Barracuda.
2015-08-24 01:35:09 13904 [Note] InnoDB: 128 rollback segment(s) are active.
2015-08-24 01:35:09 13904 [Note] InnoDB: Waiting for purge to start
2015-08-24 01:35:09 13904 [Note] InnoDB: 5.6.25 started; log sequence number 54580288
2015-08-24 01:35:09 13904 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2015-08-24 01:35:09 13904 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2015-08-24 01:35:09 13904 [Note] Server socket created on IP: '127.0.0.1'.
2015-08-24 01:35:09 13904 [Note] Event Scheduler: Loaded 0 events
2015-08-24 01:35:09 13904 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.25-1~dotdeb+7.1-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Debian)
2015-08-24 01:35:15 14034 [Note] Plugin 'FEDERATED' is disabled.
2015-08-24 01:35:15 14034 [ERROR] Function 'innodb' already exists
2015-08-24 01:35:15 14034 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2015-08-24 01:35:15 14034 [ERROR] Function 'federated' already exists
2015-08-24 01:35:15 14034 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2015-08-24 01:35:15 14034 [ERROR] Function 'blackhole' already exists
2015-08-24 01:35:15 14034 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2015-08-24 01:35:15 14034 [ERROR] Function 'archive' already exists
2015-08-24 01:35:15 14034 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
2015-08-24 01:35:15 14034 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-08-24 01:35:15 14034 [Note] InnoDB: The InnoDB memory heap is disabled
2015-08-24 01:35:15 14034 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-08-24 01:35:15 14034 [Note] InnoDB: Memory barrier is not used
2015-08-24 01:35:15 14034 [Note] InnoDB: Compressed tables use zlib 1.2.7
2015-08-24 01:35:15 14034 [Note] InnoDB: Using Linux native AIO
2015-08-24 01:35:15 14034 [Note] InnoDB: Not using CPU crc32 instructions
2015-08-24 01:35:15 14034 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-08-24 01:35:15 14034 [Note] InnoDB: Completed initialization of buffer pool
2015-08-24 01:35:16 14067 [Note] Plugin 'FEDERATED' is disabled.
2015-08-24 01:35:16 14067 [ERROR] Function 'innodb' already exists
2015-08-24 01:35:16 14067 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2015-08-24 01:35:16 14067 [ERROR] Function 'federated' already exists
2015-08-24 01:35:16 14067 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2015-08-24 01:35:16 14067 [ERROR] Function 'blackhole' already exists
2015-08-24 01:35:16 14067 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2015-08-24 01:35:16 14067 [ERROR] Function 'archive' already exists
2015-08-24 01:35:16 14067 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
2015-08-24 01:35:16 14067 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-08-24 01:35:16 14067 [Note] InnoDB: The InnoDB memory heap is disabled
2015-08-24 01:35:16 14067 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-08-24 01:35:16 14067 [Note] InnoDB: Memory barrier is not used
2015-08-24 01:35:16 14067 [Note] InnoDB: Compressed tables use zlib 1.2.7
2015-08-24 01:35:16 14067 [Note] InnoDB: Using Linux native AIO
2015-08-24 01:35:16 14067 [Note] InnoDB: Not using CPU crc32 instructions
2015-08-24 01:35:16 14067 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-08-24 01:35:16 14067 [Note] InnoDB: Completed initialization of buffer pool
2015-08-24 02:20:53 14239 [Note] Plugin 'FEDERATED' is disabled.
2015-08-24 02:20:53 14239 [ERROR] Function 'innodb' already exists
2015-08-24 02:20:53 14239 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2015-08-24 02:20:53 14239 [ERROR] Function 'federated' already exists
2015-08-24 02:20:53 14239 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2015-08-24 02:20:53 14239 [ERROR] Function 'blackhole' already exists
2015-08-24 02:20:53 14239 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2015-08-24 02:20:53 14239 [ERROR] Function 'archive' already exists
2015-08-24 02:20:53 14239 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
2015-08-24 02:20:53 14239 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-08-24 02:20:53 14239 [Note] InnoDB: The InnoDB memory heap is disabled
2015-08-24 02:20:53 14239 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-08-24 02:20:53 14239 [Note] InnoDB: Memory barrier is not used
2015-08-24 02:20:53 14239 [Note] InnoDB: Compressed tables use zlib 1.2.7
2015-08-24 02:20:53 14239 [Note] InnoDB: Using Linux native AIO
2015-08-24 02:20:53 14239 [Note] InnoDB: Not using CPU crc32 instructions
2015-08-24 02:20:53 14239 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-08-24 02:20:54 14271 [Note] Plugin 'FEDERATED' is disabled.
2015-08-24 02:20:54 14271 [ERROR] Function 'innodb' already exists
2015-08-24 02:20:54 14271 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2015-08-24 02:20:54 14271 [ERROR] Function 'federated' already exists
2015-08-24 02:20:54 14271 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2015-08-24 02:20:54 14271 [ERROR] Function 'blackhole' already exists
2015-08-24 02:20:54 14271 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2015-08-24 02:20:54 14271 [ERROR] Function 'archive' already exists
2015-08-24 02:20:54 14271 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
2015-08-24 02:20:54 14271 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-08-24 02:20:54 14271 [Note] InnoDB: The InnoDB memory heap is disabled
2015-08-24 02:20:54 14271 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-08-24 02:20:54 14271 [Note] InnoDB: Memory barrier is not used
2015-08-24 02:20:54 14271 [Note] InnoDB: Compressed tables use zlib 1.2.7
2015-08-24 02:20:54 14271 [Note] InnoDB: Using Linux native AIO
2015-08-24 02:20:54 14271 [Note] InnoDB: Not using CPU crc32 instructions
2015-08-24 02:20:54 14271 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-08-24 02:20:55 14303 [Note] Plugin 'FEDERATED' is disabled.
2015-08-24 02:20:55 14303 [ERROR] Function 'innodb' already exists
2015-08-24 02:20:55 14303 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2015-08-24 02:20:55 14303 [ERROR] Function 'federated' already exists
2015-08-24 02:20:55 14303 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2015-08-24 02:20:55 14303 [ERROR] Function 'blackhole' already exists
2015-08-24 02:20:55 14303 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2015-08-24 02:20:55 14303 [ERROR] Function 'archive' already exists
2015-08-24 02:20:55 14303 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
2015-08-24 02:20:55 14303 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-08-24 02:20:55 14303 [Note] InnoDB: The InnoDB memory heap is disabled
2015-08-24 02:20:55 14303 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-08-24 02:20:55 14303 [Note] InnoDB: Memory barrier is not used
2015-08-24 02:20:55 14303 [Note] InnoDB: Compressed tables use zlib 1.2.7
2015-08-24 02:20:55 14303 [Note] InnoDB: Using Linux native AIO
2015-08-24 02:20:55 14303 [Note] InnoDB: Not using CPU crc32 instructions
2015-08-24 02:20:55 14303 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-08-24 02:21:04 14340 [Note] Plugin 'FEDERATED' is disabled.
2015-08-24 02:21:04 14340 [ERROR] Function 'innodb' already exists
2015-08-24 02:21:04 14340 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2015-08-24 02:21:04 14340 [ERROR] Function 'federated' already exists
2015-08-24 02:21:04 14340 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2015-08-24 02:21:04 14340 [ERROR] Function 'blackhole' already exists
2015-08-24 02:21:04 14340 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2015-08-24 02:21:04 14340 [ERROR] Function 'archive' already exists
2015-08-24 02:21:04 14340 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
2015-08-24 02:21:04 14340 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-08-24 02:21:04 14340 [Note] InnoDB: The InnoDB memory heap is disabled
2015-08-24 02:21:04 14340 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-08-24 02:21:04 14340 [Note] InnoDB: Memory barrier is not used
2015-08-24 02:21:04 14340 [Note] InnoDB: Compressed tables use zlib 1.2.7
2015-08-24 02:21:04 14340 [Note] InnoDB: Using Linux native AIO
2015-08-24 02:21:04 14340 [Note] InnoDB: Not using CPU crc32 instructions
2015-08-24 02:21:04 14340 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-08-24 02:21:04 14340 [Note] InnoDB: Completed initialization of buffer pool
2015-08-24 02:21:04 14340 [Note] InnoDB: Highest supported file format is Barracuda.
2015-08-24 02:21:04 14340 [Note] InnoDB: The log sequence numbers 54580288 and 54580288 in ibdata files do not match the log sequence number 54580298 in the ib_logfiles!
2015-08-24 02:21:04 14340 [Note] InnoDB: Database was not shutdown normally!
2015-08-24 02:21:04 14340 [Note] InnoDB: Starting crash recovery.
2015-08-24 02:21:04 14340 [Note] InnoDB: Reading tablespace information from the .ibd files...
2015-08-24 02:21:04 14340 [Note] InnoDB: Restoring possible half-written data pages 
2015-08-24 02:21:04 14340 [Note] InnoDB: from the doublewrite buffer...
2015-08-24 02:21:04 14340 [Note] InnoDB: 128 rollback segment(s) are active.
2015-08-24 02:21:04 14340 [Note] InnoDB: Waiting for purge to start
2015-08-24 02:21:04 14340 [Note] InnoDB: 5.6.25 started; log sequence number 54580298
2015-08-24 02:21:04 14340 [Note] Recovering after a crash using /var/log/mysql/mysql-bin
2015-08-24 02:21:04 14340 [Note] Starting crash recovery...
2015-08-24 02:21:04 14340 [Note] Crash recovery finished.
2015-08-24 02:21:04 14340 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3307
2015-08-24 02:21:04 14340 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2015-08-24 02:21:04 14340 [Note] Server socket created on IP: '127.0.0.1'.
2015-08-24 02:21:04 14340 [Note] Event Scheduler: Loaded 0 events
2015-08-24 02:21:04 14340 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.25-1~dotdeb+7.1-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3307  (Debian)
2015-08-24 02:22:20 14468 [Note] Plugin 'FEDERATED' is disabled.
2015-08-24 02:22:20 14468 [ERROR] Function 'innodb' already exists
2015-08-24 02:22:20 14468 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2015-08-24 02:22:20 14468 [ERROR] Function 'federated' already exists
2015-08-24 02:22:20 14468 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2015-08-24 02:22:20 14468 [ERROR] Function 'blackhole' already exists
2015-08-24 02:22:20 14468 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2015-08-24 02:22:20 14468 [ERROR] Function 'archive' already exists
2015-08-24 02:22:20 14468 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
2015-08-24 02:22:20 14468 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-08-24 02:22:20 14468 [Note] InnoDB: The InnoDB memory heap is disabled
2015-08-24 02:22:20 14468 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-08-24 02:22:20 14468 [Note] InnoDB: Memory barrier is not used
2015-08-24 02:22:20 14468 [Note] InnoDB: Compressed tables use zlib 1.2.7
2015-08-24 02:22:20 14468 [Note] InnoDB: Using Linux native AIO
2015-08-24 02:22:20 14468 [Note] InnoDB: Not using CPU crc32 instructions
2015-08-24 02:22:20 14468 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-08-24 02:22:20 14468 [Note] InnoDB: Completed initialization of buffer pool
2015-08-24 02:22:21 14505 [Note] Plugin 'FEDERATED' is disabled.
2015-08-24 02:22:21 14505 [ERROR] Function 'innodb' already exists
2015-08-24 02:22:21 14505 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2015-08-24 02:22:21 14505 [ERROR] Function 'federated' already exists
2015-08-24 02:22:21 14505 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2015-08-24 02:22:21 14505 [ERROR] Function 'blackhole' already exists
2015-08-24 02:22:21 14505 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2015-08-24 02:22:21 14505 [ERROR] Function 'archive' already exists
2015-08-24 02:22:21 14505 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
2015-08-24 02:22:21 14505 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-08-24 02:22:21 14505 [Note] InnoDB: The InnoDB memory heap is disabled
2015-08-24 02:22:21 14505 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-08-24 02:22:21 14505 [Note] InnoDB: Memory barrier is not used
2015-08-24 02:22:21 14505 [Note] InnoDB: Compressed tables use zlib 1.2.7
2015-08-24 02:22:21 14505 [Note] InnoDB: Using Linux native AIO
2015-08-24 02:22:21 14505 [Note] InnoDB: Not using CPU crc32 instructions
2015-08-24 02:22:21 14505 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-08-24 02:24:56 14546 [Note] Plugin 'FEDERATED' is disabled.
2015-08-24 02:24:56 14546 [ERROR] Function 'innodb' already exists
2015-08-24 02:24:56 14546 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2015-08-24 02:24:56 14546 [ERROR] Function 'federated' already exists
2015-08-24 02:24:56 14546 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2015-08-24 02:24:56 14546 [ERROR] Function 'blackhole' already exists
2015-08-24 02:24:56 14546 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2015-08-24 02:24:56 14546 [ERROR] Function 'archive' already exists
2015-08-24 02:24:56 14546 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
2015-08-24 02:24:56 14546 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-08-24 02:24:56 14546 [Note] InnoDB: The InnoDB memory heap is disabled
2015-08-24 02:24:56 14546 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-08-24 02:24:56 14546 [Note] InnoDB: Memory barrier is not used
2015-08-24 02:24:56 14546 [Note] InnoDB: Compressed tables use zlib 1.2.7
2015-08-24 02:24:56 14546 [Note] InnoDB: Using Linux native AIO
2015-08-24 02:24:56 14546 [Note] InnoDB: Not using CPU crc32 instructions
2015-08-24 02:24:56 14546 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-08-24 02:24:56 14546 [Note] InnoDB: Completed initialization of buffer pool
2015-08-24 02:24:56 14546 [Note] InnoDB: Highest supported file format is Barracuda.
2015-08-24 02:24:56 14546 [Note] InnoDB: The log sequence numbers 54580288 and 54580288 in ibdata files do not match the log sequence number 54580308 in the ib_logfiles!
2015-08-24 02:24:56 14546 [Note] InnoDB: Database was not shutdown normally!
2015-08-24 02:24:56 14546 [Note] InnoDB: Starting crash recovery.
2015-08-24 02:24:56 14546 [Note] InnoDB: Reading tablespace information from the .ibd files...
2015-08-24 02:24:56 14546 [Note] InnoDB: Restoring possible half-written data pages 
2015-08-24 02:24:56 14546 [Note] InnoDB: from the doublewrite buffer...
2015-08-24 02:24:56 14546 [Note] InnoDB: 128 rollback segment(s) are active.
2015-08-24 02:24:56 14546 [Note] InnoDB: Waiting for purge to start
2015-08-24 02:24:56 14546 [Note] InnoDB: 5.6.25 started; log sequence number 54580308
2015-08-24 02:24:56 14546 [Note] Recovering after a crash using /var/log/mysql/mysql-bin
2015-08-24 02:24:56 14546 [Note] Starting crash recovery...
2015-08-24 02:24:56 14546 [Note] Crash recovery finished.
2015-08-24 02:24:56 14546 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3307
2015-08-24 02:24:56 14546 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2015-08-24 02:24:56 14546 [Note] Server socket created on IP: '127.0.0.1'.
2015-08-24 02:24:56 14546 [Note] Event Scheduler: Loaded 0 events
2015-08-24 02:24:56 14546 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.25-1~dotdeb+7.1-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3307  (Debian)
2015-08-24 02:25:02 14677 [Note] Plugin 'FEDERATED' is disabled.
2015-08-24 02:25:02 14677 [ERROR] Function 'innodb' already exists
2015-08-24 02:25:02 14677 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2015-08-24 02:25:02 14677 [ERROR] Function 'federated' already exists
2015-08-24 02:25:02 14677 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2015-08-24 02:25:02 14677 [ERROR] Function 'blackhole' already exists
2015-08-24 02:25:02 14677 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2015-08-24 02:25:02 14677 [ERROR] Function 'archive' already exists
2015-08-24 02:25:02 14677 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
2015-08-24 02:25:02 14677 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-08-24 02:25:02 14677 [Note] InnoDB: The InnoDB memory heap is disabled
2015-08-24 02:25:02 14677 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-08-24 02:25:02 14677 [Note] InnoDB: Memory barrier is not used
2015-08-24 02:25:02 14677 [Note] InnoDB: Compressed tables use zlib 1.2.7
2015-08-24 02:25:02 14677 [Note] InnoDB: Using Linux native AIO
2015-08-24 02:25:02 14677 [Note] InnoDB: Not using CPU crc32 instructions
2015-08-24 02:25:02 14677 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-08-24 02:25:02 14677 [Note] InnoDB: Completed initialization of buffer pool
2015-08-24 02:25:02 14710 [Note] Plugin 'FEDERATED' is disabled.
2015-08-24 02:25:02 14710 [ERROR] Function 'innodb' already exists
2015-08-24 02:25:02 14710 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2015-08-24 02:25:02 14710 [ERROR] Function 'federated' already exists
2015-08-24 02:25:02 14710 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2015-08-24 02:25:02 14710 [ERROR] Function 'blackhole' already exists
2015-08-24 02:25:02 14710 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2015-08-24 02:25:02 14710 [ERROR] Function 'archive' already exists
2015-08-24 02:25:02 14710 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
2015-08-24 02:25:02 14710 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-08-24 02:25:02 14710 [Note] InnoDB: The InnoDB memory heap is disabled
2015-08-24 02:25:02 14710 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-08-24 02:25:02 14710 [Note] InnoDB: Memory barrier is not used
2015-08-24 02:25:02 14710 [Note] InnoDB: Compressed tables use zlib 1.2.7
2015-08-24 02:25:02 14710 [Note] InnoDB: Using Linux native AIO
2015-08-24 02:25:02 14710 [Note] InnoDB: Not using CPU crc32 instructions
2015-08-24 02:25:02 14710 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-08-24 02:25:02 14710 [Note] InnoDB: Completed initialization of buffer pool
2015-08-24 02:25:19 14767 [Note] Plugin 'FEDERATED' is disabled.
2015-08-24 02:25:19 14767 [ERROR] Function 'innodb' already exists
2015-08-24 02:25:19 14767 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2015-08-24 02:25:19 14767 [ERROR] Function 'federated' already exists
2015-08-24 02:25:19 14767 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2015-08-24 02:25:19 14767 [ERROR] Function 'blackhole' already exists
2015-08-24 02:25:19 14767 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2015-08-24 02:25:19 14767 [ERROR] Function 'archive' already exists
2015-08-24 02:25:19 14767 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
2015-08-24 02:25:19 14767 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-08-24 02:25:19 14767 [Note] InnoDB: The InnoDB memory heap is disabled
2015-08-24 02:25:19 14767 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-08-24 02:25:19 14767 [Note] InnoDB: Memory barrier is not used
2015-08-24 02:25:19 14767 [Note] InnoDB: Compressed tables use zlib 1.2.7
2015-08-24 02:25:19 14767 [Note] InnoDB: Using Linux native AIO
2015-08-24 02:25:19 14767 [Note] InnoDB: Not using CPU crc32 instructions
2015-08-24 02:25:19 14767 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-08-24 02:25:19 14767 [Note] InnoDB: Completed initialization of buffer pool
2015-08-24 02:25:19 14767 [Note] InnoDB: Highest supported file format is Barracuda.
2015-08-24 02:25:19 14767 [Note] InnoDB: The log sequence numbers 54580288 and 54580288 in ibdata files do not match the log sequence number 54580318 in the ib_logfiles!
2015-08-24 02:25:19 14767 [Note] InnoDB: Database was not shutdown normally!
2015-08-24 02:25:19 14767 [Note] InnoDB: Starting crash recovery.
2015-08-24 02:25:19 14767 [Note] InnoDB: Reading tablespace information from the .ibd files...
2015-08-24 02:25:19 14767 [Note] InnoDB: Restoring possible half-written data pages 
2015-08-24 02:25:19 14767 [Note] InnoDB: from the doublewrite buffer...
2015-08-24 02:25:20 14767 [Note] InnoDB: 128 rollback segment(s) are active.
2015-08-24 02:25:20 14767 [Note] InnoDB: Waiting for purge to start
2015-08-24 02:25:20 14767 [Note] InnoDB: 5.6.25 started; log sequence number 54580318
2015-08-24 02:25:20 14767 [Note] Recovering after a crash using /var/log/mysql/mysql-bin
2015-08-24 02:25:20 14767 [Note] Starting crash recovery...
2015-08-24 02:25:20 14767 [Note] Crash recovery finished.
2015-08-24 02:25:20 14767 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3307
2015-08-24 02:25:20 14767 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2015-08-24 02:25:20 14767 [Note] Server socket created on IP: '127.0.0.1'.
2015-08-24 02:25:20 14767 [Note] Event Scheduler: Loaded 0 events
2015-08-24 02:25:20 14767 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.25-1~dotdeb+7.1-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3307  (Debian)
root@crackkc:/var/log/mysql# 


root@crackkc:/var/log/mysql# cat mysql.log
/usr/sbin/mysqld, Version: 5.6.25-1~dotdeb+7.1-log ((Debian)). started with:
Tcp port: 3307  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
150824  3:33:27     1 Connect   debian-sys-maint@localhost on 
                    1 Quit
                    2 Connect   debian-sys-maint@localhost on mysql
                    2 Query     select @@version_comment limit 1
                    2 Query     SET SQL_LOG_BIN=0
                    2 Query     show variables like 'datadir'
                    2 Quit
                    3 Connect   debian-sys-maint@localhost on 
                    3 Query     select @@version_comment limit 1
                    3 Query     SELECT count(*) FROM mysql.user WHERE user='root' and password=''
                    3 Quit
                    4 Connect   debian-sys-maint@localhost on 
                    4 Query     select @@version_comment limit 1
                    4 Query     select concat('select count(*) into @discard from `',
                    TABLE_SCHEMA, '`.`', TABLE_NAME, '`') 
      from information_schema.TABLES where ENGINE='MyISAM'
                    4 Quit
                    5 Connect   debian-sys-maint@localhost on 
                    5 Query     select @@version_comment limit 1
                    5 Query     select count(*) into @discard from `information_schema`.`COLUMNS`
                    5 Quit
                    6 Connect   debian-sys-maint@localhost on 
                    6 Query     select @@version_comment limit 1
                    6 Query     select count(*) into @discard from `information_schema`.`EVENTS`
                    6 Quit
                    7 Connect   debian-sys-maint@localhost on 
                    7 Query     select @@version_comment limit 1
                    7 Query     select count(*) into @discard from `information_schema`.`OPTIMIZER_TRACE`
                    7 Quit
                    8 Connect   debian-sys-maint@localhost on 
                    8 Query     select @@version_comment limit 1
                    8 Query     select count(*) into @discard from `information_schema`.`PARAMETERS`
                    8 Quit
                    9 Connect   debian-sys-maint@localhost on 
                    9 Query     select @@version_comment limit 1
                    9 Query     select count(*) into @discard from `information_schema`.`PARTITIONS`
                    9 Quit
                   10 Connect   debian-sys-maint@localhost on 
                   10 Query     select @@version_comment limit 1
                   10 Query     select count(*) into @discard from `information_schema`.`PLUGINS`
                   10 Quit
                   11 Connect   debian-sys-maint@localhost on 
                   11 Query     select @@version_comment limit 1
                   11 Query     select count(*) into @discard from `information_schema`.`PROCESSLIST`
                   11 Quit
                   12 Connect   debian-sys-maint@localhost on 
                   12 Query     select @@version_comment limit 1
                   12 Query     select count(*) into @discard from `information_schema`.`ROUTINES`
                   12 Quit
                   13 Connect   debian-sys-maint@localhost on 
                   13 Query     select @@version_comment limit 1
                   13 Query     select count(*) into @discard from `information_schema`.`TRIGGERS`
                   13 Quit
                   14 Connect   debian-sys-maint@localhost on 
                   14 Query     select @@version_comment limit 1
                   14 Query     select count(*) into @discard from `information_schema`.`VIEWS`
                   14 Quit
                   15 Connect   debian-sys-maint@localhost on 
                   15 Query     select @@version_comment limit 1
                   15 Query     select count(*) into @discard from `mysql`.`columns_priv`
                   15 Quit
                   16 Connect   debian-sys-maint@localhost on 
                   16 Query     select @@version_comment limit 1
                   16 Query     select count(*) into @discard from `mysql`.`db`
                   16 Quit
                   17 Connect   debian-sys-maint@localhost on 
                   17 Query     select @@version_comment limit 1
                   17 Query     select count(*) into @discard from `mysql`.`event`
                   17 Quit
                   18 Connect   debian-sys-maint@localhost on 
                   18 Query     select @@version_comment limit 1
                   18 Query     select count(*) into @discard from `mysql`.`func`
                   18 Quit
                   19 Connect   debian-sys-maint@localhost on 
                   19 Query     select @@version_comment limit 1
                   19 Query     select count(*) into @discard from `mysql`.`help_category`
                   19 Quit
                   20 Connect   debian-sys-maint@localhost on 
                   20 Query     select @@version_comment limit 1
                   20 Query     select count(*) into @discard from `mysql`.`help_keyword`
                   20 Quit
                   21 Connect   debian-sys-maint@localhost on 
                   21 Query     select @@version_comment limit 1
                   21 Query     select count(*) into @discard from `mysql`.`help_relation`
                   21 Quit
                   22 Connect   debian-sys-maint@localhost on 
                   22 Query     select @@version_comment limit 1
                   22 Query     select count(*) into @discard from `mysql`.`help_topic`
                   22 Quit
                   23 Connect   debian-sys-maint@localhost on 
                   23 Query     select @@version_comment limit 1
                   23 Query     select count(*) into @discard from `mysql`.`ndb_binlog_index`
                   23 Quit
                   24 Connect   debian-sys-maint@localhost on 
                   24 Query     select @@version_comment limit 1
                   24 Query     select count(*) into @discard from `mysql`.`plugin`
                   24 Quit
                   25 Connect   debian-sys-maint@localhost on 
                   25 Query     select @@version_comment limit 1
                   25 Query     select count(*) into @discard from `mysql`.`proc`
                   25 Quit
                   26 Connect   debian-sys-maint@localhost on 
                   26 Query     select @@version_comment limit 1
                   26 Query     select count(*) into @discard from `mysql`.`procs_priv`
                   26 Quit
                   27 Connect   debian-sys-maint@localhost on 
                   27 Query     select @@version_comment limit 1
                   27 Query     select count(*) into @discard from `mysql`.`proxies_priv`
                   27 Quit
                   28 Connect   debian-sys-maint@localhost on 
                   28 Query     select @@version_comment limit 1
                   28 Query     select count(*) into @discard from `mysql`.`servers`
                   28 Quit
                   29 Connect   debian-sys-maint@localhost on 
                   29 Query     select @@version_comment limit 1
                   29 Query     select count(*) into @discard from `mysql`.`tables_priv`
                   29 Quit
                   30 Connect   debian-sys-maint@localhost on 
                   30 Query     select @@version_comment limit 1
                   30 Query     select count(*) into @discard from `mysql`.`time_zone`
                   30 Quit
                   31 Connect   debian-sys-maint@localhost on 
                   31 Query     select @@version_comment limit 1
                   31 Query     select count(*) into @discard from `mysql`.`time_zone_leap_second`
                   31 Quit
                   32 Connect   debian-sys-maint@localhost on 
                   32 Query     select @@version_comment limit 1
                   32 Query     select count(*) into @discard from `mysql`.`time_zone_name`
                   32 Quit
                   33 Connect   debian-sys-maint@localhost on 
                   33 Query     select @@version_comment limit 1
                   33 Query     select count(*) into @discard from `mysql`.`time_zone_transition`
                   33 Quit
                   34 Connect   debian-sys-maint@localhost on 
                   34 Query     select @@version_comment limit 1
                   34 Query     select count(*) into @discard from `mysql`.`time_zone_transition_type`
                   34 Quit
                   35 Connect   debian-sys-maint@localhost on 
                   35 Query     select @@version_comment limit 1
                   35 Query     select count(*) into @discard from `mysql`.`user`
                   35 Quit
                   36 Connect   debian-sys-maint@localhost on 
                   36 Query     select @@version_comment limit 1
                   36 Query     select count(*) into @discard from `niws`.`catalog`
                   36 Quit
                   37 Connect   debian-sys-maint@localhost on 
                   37 Query     select @@version_comment limit 1
                   37 Query     select count(*) into @discard from `niws`.`comments`
                   37 Quit
                   38 Connect   debian-sys-maint@localhost on 
                   38 Query     select @@version_comment limit 1
                   38 Query     select count(*) into @discard from `niws`.`content`
                   38 Quit
                   39 Connect   debian-sys-maint@localhost on 
                   39 Query     select @@version_comment limit 1
                   39 Query     select count(*) into @discard from `niws`.`forum`
                   39 Quit
                   40 Connect   debian-sys-maint@localhost on 
                   40 Query     select @@version_comment limit 1
                   40 Query     select count(*) into @discard from `niws`.`mail`
                   40 Quit
                   41 Connect   debian-sys-maint@localhost on 
                   41 Query     select @@version_comment limit 1
                   41 Query     select count(*) into @discard from `niws`.`menu`
                   41 Quit
                   42 Connect   debian-sys-maint@localhost on 
                   42 Query     select @@version_comment limit 1
                   42 Query     select count(*) into @discard from `niws`.`news`
                   42 Quit
                   43 Connect   debian-sys-maint@localhost on 
                   43 Query     select @@version_comment limit 1
                   43 Query     select count(*) into @discard from `niws`.`pages`
                   43 Quit
                   44 Connect   debian-sys-maint@localhost on 
                   44 Query     select @@version_comment limit 1
                   44 Query     select count(*) into @discard from `niws`.`user_admin`
                   44 Quit
                   45 Connect   debian-sys-maint@localhost on 
                   45 Query     select @@version_comment limit 1
                   45 Query     select count(*) into @discard from `phpmyadmin`.`pma_bookmark`
                   45 Quit
                   46 Connect   debian-sys-maint@localhost on 
                   46 Query     select @@version_comment limit 1
                   46 Query     select count(*) into @discard from `phpmyadmin`.`pma_column_info`
                   46 Quit
                   47 Connect   debian-sys-maint@localhost on 
                   47 Query     select @@version_comment limit 1
                   47 Query     select count(*) into @discard from `phpmyadmin`.`pma_designer_coords`
                   47 Quit
                   48 Connect   debian-sys-maint@localhost on 
                   48 Query     select @@version_comment limit 1
                   48 Query     select count(*) into @discard from `phpmyadmin`.`pma_history`
                   48 Quit
                   49 Connect   debian-sys-maint@localhost on 
                   49 Query     select @@version_comment limit 1
                   49 Query     select count(*) into @discard from `phpmyadmin`.`pma_pdf_pages`
                   49 Quit
                   50 Connect   debian-sys-maint@localhost on 
                   50 Query     select @@version_comment limit 1
                   50 Query     select count(*) into @discard from `phpmyadmin`.`pma_relation`
                   50 Quit
                   51 Connect   debian-sys-maint@localhost on 
                   51 Query     select @@version_comment limit 1
                   51 Query     select count(*) into @discard from `phpmyadmin`.`pma_table_coords`
                   51 Quit
                   52 Connect   debian-sys-maint@localhost on 
                   52 Query     select @@version_comment limit 1
                   52 Query     select count(*) into @discard from `phpmyadmin`.`pma_table_info`
                   52 Quit
                   53 Connect   debian-sys-maint@localhost on 
                   53 Query     select @@version_comment limit 1
                   53 Query     select count(*) into @discard from `phpmyadmin`.`pma_tracking`
                   53 Quit
                   54 Connect   debian-sys-maint@localhost on 
                   54 Query     select @@version_comment limit 1
                   54 Query     select count(*) into @discard from `phpmyadmin`.`pma_userconfig`
                   54 Quit
150824  3:33:29    55 Connect   root@localhost on 
                   55 Init DB   niws
                   55 Query     SET NAMES utf8
                   55 Query     SELECT *
FROM (`menu`)
WHERE `id` =  '88'
                   55 Query     SELECT *
FROM (`menu`)
WHERE `id_mom` =  '22'
ORDER BY `order` ASC
                   55 Query     SELECT *
FROM (`menu`)
WHERE `id` =  '88'
                   55 Query     SELECT *
FROM (`menu`)
ORDER BY `order` ASC
                   55 Query     SELECT *
FROM (`catalog`)
WHERE `cat` =  '88'
ORDER BY `id` DESC
                   55 Query     SELECT *
FROM (`menu`)
WHERE `url` =  '1'
ORDER BY `order` ASC
                   56 Connect   root@localhost on 
                   56 Init DB   niws
                   56 Query     SET NAMES utf8
                   56 Query     SELECT *
FROM (`pages`)
WHERE `name` =  'main'
                   56 Query     SELECT *
FROM (`menu`)
ORDER BY `order` ASC
                   56 Query     SELECT *
FROM (`menu`)
WHERE `id_mom` =  '22'
ORDER BY `order` ASC
                   56 Query     SELECT *
FROM (`content`)
WHERE `cat` =  'main'
                   56 Query     SELECT *
FROM (`catalog`)
WHERE `tip_text` =  'history'
ORDER BY `id` DESC
LIMIT 2
                   56 Query     SELECT *
FROM (`catalog`)
WHERE `tip_text` =  'open'
ORDER BY `id` DESC
LIMIT 2
                   56 Query     SELECT *
FROM (`catalog`)
WHERE `tip_text` =  'ludi'
ORDER BY `id` DESC
LIMIT 2
/usr/sbin/mysqld, Version: 5.6.25-1~dotdeb+7.1-log ((Debian)). started with:
Tcp port: 3307  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
150824  3:33:59     1 Connect   debian-sys-maint@localhost on 
                    1 Quit
                    2 Connect   debian-sys-maint@localhost on mysql
                    2 Query     select @@version_comment limit 1
                    2 Query     SET SQL_LOG_BIN=0
                    2 Query     show variables like 'datadir'
                    2 Quit
                    3 Connect   debian-sys-maint@localhost on 
                    3 Query     select @@version_comment limit 1
                    3 Query     SELECT count(*) FROM mysql.user WHERE user='root' and password=''
                    3 Quit
                    4 Connect   debian-sys-maint@localhost on 
                    4 Query     select @@version_comment limit 1
                    4 Query     select concat('select count(*) into @discard from `',
                    TABLE_SCHEMA, '`.`', TABLE_NAME, '`') 
      from information_schema.TABLES where ENGINE='MyISAM'
                    4 Quit
                    5 Connect   debian-sys-maint@localhost on 
                    5 Query     select @@version_comment limit 1
                    5 Query     select count(*) into @discard from `information_schema`.`COLUMNS`
                    5 Quit
                    6 Connect   debian-sys-maint@localhost on 
                    6 Query     select @@version_comment limit 1
                    6 Query     select count(*) into @discard from `information_schema`.`EVENTS`
                    6 Quit
                    7 Connect   debian-sys-maint@localhost on 
                    7 Query     select @@version_comment limit 1
                    7 Query     select count(*) into @discard from `information_schema`.`OPTIMIZER_TRACE`
                    7 Quit
                    8 Connect   debian-sys-maint@localhost on 
                    8 Query     select @@version_comment limit 1
                    8 Query     select count(*) into @discard from `information_schema`.`PARAMETERS`
                    8 Quit
                    9 Connect   debian-sys-maint@localhost on 
                    9 Query     select @@version_comment limit 1
                    9 Query     select count(*) into @discard from `information_schema`.`PARTITIONS`
                    9 Quit
                   10 Connect   debian-sys-maint@localhost on 
                   10 Query     select @@version_comment limit 1
                   10 Query     select count(*) into @discard from `information_schema`.`PLUGINS`
                   10 Quit
                   11 Connect   debian-sys-maint@localhost on 
                   11 Query     select @@version_comment limit 1
                   11 Query     select count(*) into @discard from `information_schema`.`PROCESSLIST`
                   11 Quit
                   12 Connect   debian-sys-maint@localhost on 
                   12 Query     select @@version_comment limit 1
                   12 Query     select count(*) into @discard from `information_schema`.`ROUTINES`
                   12 Quit
                   13 Connect   debian-sys-maint@localhost on 
                   13 Query     select @@version_comment limit 1
                   13 Query     select count(*) into @discard from `information_schema`.`TRIGGERS`
                   13 Quit
                   14 Connect   debian-sys-maint@localhost on 
                   14 Query     select @@version_comment limit 1
                   14 Query     select count(*) into @discard from `information_schema`.`VIEWS`
                   14 Quit
                   15 Connect   debian-sys-maint@localhost on 
                   15 Query     select @@version_comment limit 1
                   15 Query     select count(*) into @discard from `mysql`.`columns_priv`
                   15 Quit
                   16 Connect   debian-sys-maint@localhost on 
                   16 Query     select @@version_comment limit 1
                   16 Query     select count(*) into @discard from `mysql`.`db`
                   16 Quit
                   17 Connect   debian-sys-maint@localhost on 
                   17 Query     select @@version_comment limit 1
                   17 Query     select count(*) into @discard from `mysql`.`event`
                   17 Quit
                   18 Connect   debian-sys-maint@localhost on 
                   18 Query     select @@version_comment limit 1
                   18 Query     select count(*) into @discard from `mysql`.`func`
                   18 Quit
                   19 Connect   debian-sys-maint@localhost on 
                   19 Query     select @@version_comment limit 1
                   19 Query     select count(*) into @discard from `mysql`.`help_category`
                   19 Quit
                   20 Connect   debian-sys-maint@localhost on 
                   20 Query     select @@version_comment limit 1
                   20 Query     select count(*) into @discard from `mysql`.`help_keyword`
                   20 Quit
                   21 Connect   debian-sys-maint@localhost on 
                   21 Query     select @@version_comment limit 1
                   21 Query     select count(*) into @discard from `mysql`.`help_relation`
                   21 Quit
                   22 Connect   debian-sys-maint@localhost on 
                   22 Query     select @@version_comment limit 1
                   22 Query     select count(*) into @discard from `mysql`.`help_topic`
                   22 Quit
                   23 Connect   debian-sys-maint@localhost on 
                   23 Query     select @@version_comment limit 1
                   23 Query     select count(*) into @discard from `mysql`.`ndb_binlog_index`
                   23 Quit
150824  3:34:00    24 Connect   debian-sys-maint@localhost on 
                   24 Query     select @@version_comment limit 1
                   24 Query     select count(*) into @discard from `mysql`.`plugin`
                   24 Quit
                   25 Connect   debian-sys-maint@localhost on 
                   25 Query     select @@version_comment limit 1
                   25 Query     select count(*) into @discard from `mysql`.`proc`
                   25 Quit
                   26 Connect   debian-sys-maint@localhost on 
                   26 Query     select @@version_comment limit 1
                   26 Query     select count(*) into @discard from `mysql`.`procs_priv`
                   26 Quit
                   27 Connect   debian-sys-maint@localhost on 
                   27 Query     select @@version_comment limit 1
                   27 Query     select count(*) into @discard from `mysql`.`proxies_priv`
                   27 Quit
                   28 Connect   debian-sys-maint@localhost on 
                   28 Query     select @@version_comment limit 1
                   28 Query     select count(*) into @discard from `mysql`.`servers`
                   28 Quit
                   29 Connect   debian-sys-maint@localhost on 
                   29 Query     select @@version_comment limit 1
                   29 Query     select count(*) into @discard from `mysql`.`tables_priv`
                   29 Quit
                   30 Connect   debian-sys-maint@localhost on 
                   30 Query     select @@version_comment limit 1
                   30 Query     select count(*) into @discard from `mysql`.`time_zone`
                   30 Quit
                   31 Connect   debian-sys-maint@localhost on 
                   31 Query     select @@version_comment limit 1
                   31 Query     select count(*) into @discard from `mysql`.`time_zone_leap_second`
                   31 Quit
                   32 Connect   debian-sys-maint@localhost on 
                   32 Query     select @@version_comment limit 1
                   32 Query     select count(*) into @discard from `mysql`.`time_zone_name`
                   32 Quit
                   33 Connect   debian-sys-maint@localhost on 
                   33 Query     select @@version_comment limit 1
                   33 Query     select count(*) into @discard from `mysql`.`time_zone_transition`
                   33 Quit
                   34 Connect   debian-sys-maint@localhost on 
                   34 Query     select @@version_comment limit 1
                   34 Query     select count(*) into @discard from `mysql`.`time_zone_transition_type`
                   34 Quit
                   35 Connect   debian-sys-maint@localhost on 
                   35 Query     select @@version_comment limit 1
                   35 Query     select count(*) into @discard from `mysql`.`user`
                   35 Quit
                   36 Connect   debian-sys-maint@localhost on 
                   36 Query     select @@version_comment limit 1
                   36 Query     select count(*) into @discard from `niws`.`catalog`
                   36 Quit
                   37 Connect   debian-sys-maint@localhost on 
                   37 Query     select @@version_comment limit 1
                   37 Query     select count(*) into @discard from `niws`.`comments`
                   37 Quit
                   38 Connect   debian-sys-maint@localhost on 
                   38 Query     select @@version_comment limit 1
                   38 Query     select count(*) into @discard from `niws`.`content`
                   38 Quit
                   39 Connect   debian-sys-maint@localhost on 
                   39 Query     select @@version_comment limit 1
                   39 Query     select count(*) into @discard from `niws`.`forum`
                   39 Quit
                   40 Connect   debian-sys-maint@localhost on 
                   40 Query     select @@version_comment limit 1
                   40 Query     select count(*) into @discard from `niws`.`mail`
                   40 Quit
                   41 Connect   debian-sys-maint@localhost on 
                   41 Query     select @@version_comment limit 1
                   41 Query     select count(*) into @discard from `niws`.`menu`
                   41 Quit
                   42 Connect   debian-sys-maint@localhost on 
                   42 Query     select @@version_comment limit 1
                   42 Query     select count(*) into @discard from `niws`.`news`
                   42 Quit
                   43 Connect   debian-sys-maint@localhost on 
                   43 Query     select @@version_comment limit 1
                   43 Query     select count(*) into @discard from `niws`.`pages`
                   43 Quit
                   44 Connect   debian-sys-maint@localhost on 
                   44 Query     select @@version_comment limit 1
                   44 Query     select count(*) into @discard from `niws`.`user_admin`
                   44 Quit
                   45 Connect   debian-sys-maint@localhost on 
                   45 Query     select @@version_comment limit 1
                   45 Query     select count(*) into @discard from `phpmyadmin`.`pma_bookmark`
                   45 Quit
                   46 Connect   debian-sys-maint@localhost on 
                   46 Query     select @@version_comment limit 1
                   46 Query     select count(*) into @discard from `phpmyadmin`.`pma_column_info`
                   46 Quit
                   47 Connect   debian-sys-maint@localhost on 
                   47 Query     select @@version_comment limit 1
                   47 Query     select count(*) into @discard from `phpmyadmin`.`pma_designer_coords`
                   47 Quit
                   48 Connect   debian-sys-maint@localhost on 
                   48 Query     select @@version_comment limit 1
                   48 Query     select count(*) into @discard from `phpmyadmin`.`pma_history`
                   48 Quit
                   49 Connect   debian-sys-maint@localhost on 
                   49 Query     select @@version_comment limit 1
                   49 Query     select count(*) into @discard from `phpmyadmin`.`pma_pdf_pages`
                   49 Quit
                   50 Connect   debian-sys-maint@localhost on 
                   50 Query     select @@version_comment limit 1
                   50 Query     select count(*) into @discard from `phpmyadmin`.`pma_relation`
                   50 Quit
                   51 Connect   debian-sys-maint@localhost on 
                   51 Query     select @@version_comment limit 1
                   51 Query     select count(*) into @discard from `phpmyadmin`.`pma_table_coords`
                   51 Quit
                   52 Connect   debian-sys-maint@localhost on 
                   52 Query     select @@version_comment limit 1
                   52 Query     select count(*) into @discard from `phpmyadmin`.`pma_table_info`
                   52 Quit
                   53 Connect   debian-sys-maint@localhost on 
                   53 Query     select @@version_comment limit 1
                   53 Query     select count(*) into @discard from `phpmyadmin`.`pma_tracking`
                   53 Quit
                   54 Connect   debian-sys-maint@localhost on 
                   54 Query     select @@version_comment limit 1
                   54 Query     select count(*) into @discard from `phpmyadmin`.`pma_userconfig`
                   54 Quit
150824  3:34:02    55 Connect   root@localhost on 
                   55 Init DB   niws
                   55 Query     SET NAMES utf8
                   55 Query     SELECT *
FROM (`menu`)
WHERE `id` =  '85'
                   55 Query     SELECT *
FROM (`menu`)
WHERE `id_mom` =  '22'
ORDER BY `order` ASC
                   55 Query     SELECT *
FROM (`menu`)
WHERE `id` =  '85'
                   55 Query     SELECT *
FROM (`menu`)
ORDER BY `order` ASC
                   55 Query     SELECT *
FROM (`catalog`)
WHERE `cat` =  '85'
ORDER BY `id` DESC
                   55 Query     SELECT *
FROM (`menu`)
WHERE `url` =  '1'
ORDER BY `order` ASC
                   56 Connect   root@localhost on 
                   56 Init DB   niws
                   56 Query     SET NAMES utf8
                   56 Query     SELECT *
FROM (`pages`)
WHERE `name` =  'main'
                   56 Query     SELECT *
FROM (`menu`)
ORDER BY `order` ASC
                   56 Query     SELECT *
FROM (`menu`)
WHERE `id_mom` =  '22'
ORDER BY `order` ASC
                   56 Query     SELECT *
FROM (`content`)
WHERE `cat` =  'main'
                   56 Query     SELECT *
FROM (`catalog`)
WHERE `tip_text` =  'history'
ORDER BY `id` DESC
LIMIT 2
                   56 Query     SELECT *
FROM (`catalog`)
WHERE `tip_text` =  'open'
ORDER BY `id` DESC
LIMIT 2
                   56 Query     SELECT *
FROM (`catalog`)
WHERE `tip_text` =  'ludi'
ORDER BY `id` DESC
LIMIT 2
root@crackkc:/var/log/mysql# 
Ответ: !includedir /etc/mysql/conf.d/

вот тут
Вопрос: Запрос через аналитические функции

какой аналит. функцией можно сделать преобразование к необходимому виду?
Исходные данные:
with tab as
(select 1 a from dual 
union all
select 1 a from dual 
union all
select 1 a from dual 
union all
select 1 a from dual 
union all
select 7 a from dual 
union all
select 7 a from dual 
union all
select 30 a from dual
union all
select 30 a from dual
union all
select 30 from dual)
select * from tab;


Нужно:
with tab as
(select 1 a , 1 b from dual 
union all
select 1 a, 1 b from dual 
union all
select 1 a, 1 b from dual 
union all
select 1 a, 1 b from dual 
union all
select 7 a, 2 b from dual 
union all
select 7 a, 2 b from dual 
union all
select 10 a, 3 b from dual
union all
select 10 a, 3 b from dual
union all
select 10 a, 3 b from dual)
select * from tab;


Можно вот так, но это не совсем правильно:
with tab as
(select 1 a from dual 
union all
select 1 a from dual 
union all
select 1 a from dual 
union all
select 1 a from dual 
union all
select 7 a from dual 
union all
select 7 a from dual 
union all
select 30 a from dual
union all
select 30 f1 from dual
union all
select 30 a from dual)
select a, tab1.rn from tab
join (select a, ROW_NUMBER() OVER (order by a) as rn from tab
group by a) tab1 on tab.a=tab1.a;
Ответ: большое всем спасибо
Вопрос: Промежуточные итоги rollup

добрый день! подскажите пожалуйста с промежуточными итогами, не могу получить нужный результат.
данные
with query1 as 
(
     select '123' schet, '1111101' INN, 'Рога и копыта' nameorg, 12.12 SummAll, 123.12 Summ1, 4.04 Proc, 'Филиал1' fil from dual
     union
     select '124' , '1111102' , 'Рога и копыта1' , 1.01 , 1.02 , 6.23 , 'Филиал2'  from dual
     union
     select '125' , '1111103' , 'Рога и копыта3' , 2.02 , 2.02 , 2.02 , 'Филиал1'  from dual
     union
     select '126' , '1111103' , 'Рога и копыта33' , 3.03 , 3.03 , 3.03 , 'Филиал3'  from dual
     union
     select '127' , '1111105' , 'Рога и копыта11' , 4.04 , 4.04 , 4.24 , 'Филиал1'  from dual
     union
     select '128' , '1111105' , 'Рога и копыта12'  , 5.05 , 5.05 , 5.25 , 'Филиал2'  from dual
     union
     select '129' , '1111107' , 'Рога и копыта111' , 6.06 , 6.06 , 6.26 , 'Филиал1'  from dual
     union
     select '130' , '1111107' , 'Рога и копыта122' , 7.07 , 7.07 , 7.27 , 'Филиал2'  from dual
     union
     select '131' , '1111107' , 'Рога и копыта133' , 8.08 , 8.08 , 8.28 , 'Филиал3'  from dual

)

select  nameorg, INN, schet, fil, sum(SummAll) SummAll, sum(Summ1) Summ1, sum(Proc) Proc
from query1
group by rollup (nameorg, INN, schet, fil)


необходимо сгруппировать и получить промежуточные итоги в виде результата

               
           nameorg            INN             schet        fil                     SummAll      Summ1        Proc
1	Рога и копыта	      1111101        '123'	   'Филиал1'               12,12        123,12        4,04
2	Итого		                                                           12,12        123,12        4,04
3	Рога и копыта1	      1111102	 '124'        'Филиал2'                    1,01        1,02        6,23
4	Итого               		                                           1,01        1,02        6,23
5	Рога и копыта3	      1111103	 '125'        'Филиал1'                    2,02        2,02        2,02
6	Рога и копыта33	      1111103	 '126'        'Филиал3'                    3,03        3,03        3,03
7	Итого             		                                           5,05        5,05        5,05
8	Рога и копыта11	      1111105	 '127'        'Филиал1'                    4,04        4,04        4,04
9	Рога и копыта12	      1111105	 '128'        'Филиал2'                    5,05        5,05        5,05
10	итого             		                                           9,09        9,09        9,09
11	Рога и копыта111      1111107	 '129'        'Филиал1'                     6,06        6,06        6,26
12	Рога и копыта122      1111107	 '130'        'Филиал2'                     7,07        7,07        7,27
13	Рога и копыта133      1111107	 '131'        'Филиал3'                     8,08        8,08        8,28
14	итого             		                                          21,21      21,21        21,21
Ответ: Подскажите пожалуйста, как применить условие для промежуточного итога rollup. Вывести те организации у которых суммарный % sum(Proc) <7,
with query1 as 
(
     select '123' schet, '1111101' INN, 'Рога и копыта' nameorg, 12.12 SummAll, 123.12 Summ1, 4.04 Proc, 'Филиал1' fil from dual
     union
     select '124' , '1111102' , 'Рога и копыта1' , 1.01 , 1.02 , 6.23 , 'Филиал2'  from dual
     union
     select '125' , '1111103' , 'Рога и копыта3' , 2.02 , 2.02 , 2.02 , 'Филиал1'  from dual
     union
     select '126' , '1111103' , 'Рога и копыта33' , 3.03 , 3.03 , 3.03 , 'Филиал3'  from dual
     union
     select '127' , '1111105' , 'Рога и копыта11' , 4.04 , 4.04 , 4.24 , 'Филиал1'  from dual
     union
     select '128' , '1111105' , 'Рога и копыта12'  , 5.05 , 5.05 , 5.25 , 'Филиал2'  from dual
     union
     select '129' , '1111107' , 'Рога и копыта111' , 6.06 , 6.06 , 6.26 , 'Филиал1'  from dual
     union
     select '130' , '1111107' , 'Рога и копыта122' , 7.07 , 7.07 , 7.27 , 'Филиал2'  from dual
     union
     select '131' , '1111107' , 'Рога и копыта133' , 8.08 , 8.08 , 8.28 , 'Филиал3'  from dual

)
select * 
from (
    select INN, 
           decode(grouping_id(INN,nameorg),1,'Всего по ИНН '||INN||':',3,'Итого:',nameorg) nameorg, 
           schet, 
           fil,
           sum(SummAll) SummAll, 
           sum(Summ1) Summ1, 
           sum(Proc) Proc,
           decode(grouping_id(INN,nameorg),3,1,0) seq_1,
           decode(grouping_id(INN,nameorg),1,1,0) seq_2
    from query1
    group by rollup(INN, (nameorg, schet, fil))
    order by seq_1,INN,seq_2,nameorg
)


т.е. должны получить результат
1	1111101	Рога и копыта	123	Филиал1	12,12	123,12	4,04	0	0
2	1111101	Всего по ИНН 1111101:			12,12	123,12	4,04	0	1
3	1111102	Рога и копыта1	124	Филиал2	1,01	1,02	6,23	0	0
4	1111102	Всего по ИНН 1111102:			1,01	1,02	6,23	0	1
5	1111103	Рога и копыта3	125	Филиал1	2,02	2,02	2,02	0	0
6	1111103	Рога и копыта33	126	Филиал3	3,03	3,03	3,03	0	0
7	1111103	Всего по ИНН 1111103:			5,05	5,05	5,05	0	1
Вопрос: select union select union select

select union select union select

возможен ли такой запрос. если да как мне это возможно

прошу покат на примерах
Ответ:

К сообщению приложен файл. Размер - 57Kb
Вопрос: динамический insert

всем привет

у меня есть следущий датасет
он содержит данные по опроснику - вопросы - ответы по каждому вопросу

select tt.id, tt.tag, tt.val from (
select 1  as id, 'quiz_id'     as tag, 20571 as val from dual union all
select 2  as id, 'question_id' as tag, 38091 as val from dual union all
select 3  as id, 'answer_val'  as tag, 10 as val from dual union all
select 4  as id, 'answer_val'  as tag, 11 as val from dual union all
select 5  as id, 'answer_val'  as tag, 12 as val from dual union all
select 6  as id, 'answer_val'  as tag, 13 as val from dual union all
select 7  as id, 'answer_val'  as tag, 14 as val from dual union all
-- 
select 8  as id, 'question_id' as tag, 38092 as val from dual union all
select 9  as id, 'answer_val'  as tag, 15 as val from dual union all
select 10 as id, 'answer_val'  as tag, 16 as val from dual union all
--
select 11 as id, 'question_id' as tag, 38093 as val from dual union all
select 12 as id, 'answer_val'  as tag, 20 as val from dual union all
select 13 as id, 'answer_val'  as tag, 21 as val from dual) tt



и таблица в которую надо инсертнуть эти данные

create table quiz_question_answers
(
    quiz_id         number,
    question_id     number,
    answer          number
)



по сути надо генерить следующие SQL insert:

insert into quiz_question_answers(quiz_id, question_id, answer) values (20571, 38091, 10);
insert into quiz_question_answers(quiz_id, question_id, answer) values (20571, 38091, 11);
insert into quiz_question_answers(quiz_id, question_id, answer) values (20571, 38091, 12);
insert into quiz_question_answers(quiz_id, question_id, answer) values (20571, 38091, 13);
insert into quiz_question_answers(quiz_id, question_id, answer) values (20571, 38091, 14);

insert into quiz_question_answers(quiz_id, question_id, answer) values (20571, 38092, 15);
insert into quiz_question_answers(quiz_id, question_id, answer) values (20571, 38092, 16);

insert into quiz_question_answers(quiz_id, question_id, answer) values (20571, 38093, 20);
insert into quiz_question_answers(quiz_id, question_id, answer) values (20571, 38093, 21);



как такое реализовать через динамический insert или используя INSERT INTO quiz_question_answers SELECT ... ?
Ответ: andreymx,
побаловался с рекурсией тоже
WITH T AS
(
SELECT 1  AS ID, 'quiz_id'     AS tag, 20571 AS val FROM dual UNION ALL
SELECT 2  AS ID, 'question_id' AS tag, 38091 AS val FROM dual UNION ALL
SELECT 3  AS ID, 'answer_val'  AS tag, 10 AS val FROM dual UNION ALL
SELECT 4  AS ID, 'answer_val'  AS tag, 11 AS val FROM dual UNION ALL
SELECT 5  AS ID, 'answer_val'  AS tag, 12 AS val FROM dual UNION ALL
SELECT 6  AS ID, 'answer_val'  AS tag, 13 AS val FROM dual UNION ALL
SELECT 7  AS ID, 'answer_val'  AS tag, 14 AS val FROM dual UNION ALL
-- 
SELECT 8  AS ID, 'question_id' AS tag, 38092 AS val FROM dual UNION ALL
SELECT 9  AS ID, 'answer_val'  AS tag, 15 AS val FROM dual UNION ALL
SELECT 10 AS ID, 'answer_val'  AS tag, 16 AS val FROM dual UNION ALL
--
SELECT 11 AS ID, 'question_id' AS tag, 38093 AS val FROM dual UNION ALL
SELECT 12 AS ID, 'answer_val'  AS tag, 20 AS val FROM dual UNION ALL
SELECT 13 AS ID, 'answer_val'  AS tag, 21 AS val FROM dual),
tt as
 (select t.*, decode(tag,'quiz_id',1,'question_id',2,'answer_val',3,4) lvl from t),
rec_t (id, next_id, lvl, quiz_id, question_id, val) AS
 (SELECT  tt.ID, lead (tt.id) over (order by tt.id), 1, val, -1, -1 
    FROM tt WHERE lvl=1
  UNION ALL
  SELECT tt.ID, lead (tt.id) over (order by tt.id), tt.lvl, quiz_id, rec_t.val,tt.val
   FROM tt, rec_t 
   WHERE tt.lvl=rec_t.lvl+1 and tt.id > rec_t.id and tt.id <= nvl(rec_t.next_id,tt.id))
SELECT quiz_id, question_id, val FROM rec_t
 WHERE lvl=3