История изменений [назад]

по ssh

. /etc/amportal.conf
mysql asterisk -u $AMPDBUSER -p$AMPDBPASS

дальше на sql пишите запрос вот такого вида

select ids.id,
(select data from sip where keyword='callerid' and id=ids.id) as callerid,
(select data from sip where keyword='secret' and id=ids.id) as secret,
(select data from sip where keyword='callgroup' and id=ids.id) as callgroup
  from (select distinct id from sip) ids;

ну добавляете колонки чего вам надо.

получаете вот такое

mysql> select ids.id,(select data from sip where keyword='callerid' and id=ids.id) as callerid,(select data from sip where keyword='secret' and id=ids.id) as secret,(select data from sip where keyword='callgroup' and id=ids.id) as callgroup from (select distinct id from sip) ids; +-----+--------------+-------------+-----------+ | id | callerid | secret | callgroup | +-----+--------------+-------------+-----------+ | 199 | device <199> | mtest23test | | +-----+--------------+-------------+-----------+ 1 row in set (0.00 sec)

можно сразу експортнуть в csv. смотрите

select ids.id,
(select data from sip where keyword='callerid' and id=ids.id) as callerid,
    (select data from sip where keyword='secret' and id=ids.id) as secret,
    (select data from sip where keyword='callgroup' and id=ids.id) as callgroup

INTO OUTFILE '/tmp/products.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'

from (select distinct id from sip) ids;

выполнить по sshssh(скопировать и вставить в консоль/putty)

. /etc/amportal.conf
mysql asterisk -u $AMPDBUSER -p$AMPDBPASS

дальше на sql пишите запрос вот такого вида

select ids.id,
(select data from sip where keyword='callerid' and id=ids.id) as callerid,
(select data from sip where keyword='secret' and id=ids.id) as secret,
(select data from sip where keyword='callgroup' and id=ids.id) as callgroup
  from (select distinct id from sip) ids;

ну добавляете колонки чего вам надо.

получаете вот такое

mysql> select ids.id,(select data from sip where keyword='callerid' and id=ids.id) as callerid,(select data from sip where keyword='secret' and id=ids.id) as secret,(select data from sip where keyword='callgroup' and id=ids.id) as callgroup from (select distinct id from sip) ids; +-----+--------------+-------------+-----------+ | id | callerid | secret | callgroup | +-----+--------------+-------------+-----------+ | 199 | device <199> | mtest23test | | +-----+--------------+-------------+-----------+ 1 row in set (0.00 sec)

можно сразу експортнуть в csv. смотрите

select ids.id,
(select data from sip where keyword='callerid' and id=ids.id) as callerid,
    (select data from sip where keyword='secret' and id=ids.id) as secret,
    (select data from sip where keyword='callgroup' and id=ids.id) as callgroup

INTO OUTFILE '/tmp/products.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'

from (select distinct id from sip) ids;