Test: различия между версиями

Материал из noname.com.ua
Перейти к навигацииПерейти к поиску
 
(не показаны 4 промежуточные версии 2 участников)
Строка 1: Строка 1:
 
[[Категория:Test]]
  
[[Категория:Test]]
  +
=Query=
 
<PRE>
  
<PRE>
 
select
 
select
Строка 18: Строка 19:
 
tp.month_traf_limit,
 
tp.month_traf_limit,
 
tp.octets_direction,
 
tp.octets_direction,
−
if (count(un.uid) + count(tp_nas.tp_id) = 0, 0, if (count(un.uid)>0, 1, 2)),UNIX_TIMESTAMP(), UNIX_TIMESTAMP(DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP()), '%Y-%m-%d')), DAYOFWEEK(FROM_UNIXTIME(UNIX_TIMESTAMP())), DAYOFYEAR(FROM_UNIXTIME(UNIX_TIMESTAMP())), dv.disable, tp.max_session_duration, tp.payment_type, tp.credit_tresshold, tp.rad_pairs, count(i.id), tp.age, dv.callback, dv.port, tp.traffic_transfer_period, tp.neg_deposit_filter_id, tp.ext_bill_account, tp.credit, tp.ippool,dv.join_service, tp.tp_id
+
if (count(un.uid) + count(tp_nas.tp_id) = 0, 0, if (count(un.uid)>0, 1, 2)),UNIX_TIMESTAMP(), UNIX_TIMESTAMP(DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP()), '%Y-%m-%d')), DAYOFWEEK(FROM_UNIXTIME(UNIX_TIMESTAMP())), DAYOFYEAR(FROM_UNIXTIME(UNIX_TIMESTAMP())),
  +
dv.disable,
−  
  +
tp.max_session_duration,
  +
tp.payment_type,
  +
tp.credit_tresshold,
  +
tp.rad_pairs,
  +
count(i.id),
  +
tp.age,
  +
dv.callback,
  +
dv.port,
  +
tp.traffic_transfer_period,
  +
tp.neg_deposit_filter_id,
  +
tp.ext_bill_account,
  +
tp.credit,
  +
tp.ippool,dv.join_service,
  +
tp.tp_id
 
FROM
 
FROM
 
(dv_main dv)
 
(dv_main dv)
Строка 38: Строка 53:
 
ON
 
ON
 
(tp.tp_id = i.tp_id) WHERE dv.uid='1720'GROUP BY dv.uid;
  
(tp.tp_id = i.tp_id) WHERE dv.uid='1720'GROUP BY dv.uid;
  +
</PRE>
  +
  +
  +
=res 1 (work)=
  +
<PRE>
  +
+--------+----------------------------------------------------------------------------------+----------------------------------+-----------------------+-------+-------+-----+------------------+----------------+-----------------+------------------+---------------------------------------------------------------------------------------------------+------------------+----------------+-----------------+------------------+------------------+--------------------------------------------------------------------------------+------------------+--------------------------------------------------------------------------+--------------------------------------------+--------------------------------------------+---------+----------------------+--------------+------------------+-----------+-------------+------+----------+------+-------------------------+-----------------------+------------------+--------+--------+--------------+-------+
  +
| 1 | | 172.16.129.72 | 255.255.255.255 | 260 | 0 | | 0 | 0 | 0 | 0 | 114523 | 0 | 0 | 0 | 0 | 0 | 0 | 1298815877 | 1298757600 | 1 | 58 | 0 | 0 | 0 | 0.00 | | 1 | 0 | 0 | 0 | 0 | | 1 | 0.00 | 0 | 0 | 33 |
  +
+--------+----------------------------------------------------------------------------------+----------------------------------+-----------------------+-------+-------+-----+------------------+----------------+-----------------+------------------+---------------------------------------------------------------------------------------------------+------------------+----------------+-----------------+------------------+------------------+--------------------------------------------------------------------------------+------------------+--------------------------------------------------------------------------+--------------------------------------------+--------------------------------------------+---------+----------------------+--------------+------------------+-----------+-------------+------+----------+------+-------------------------+-----------------------+------------------+--------+--------+--------------+-------+
  +
1 row in set (0.01 sec)
  +
  +
</PRE>
  +
=res 2 (not work)=
  +
<PRE>
  +
+--------+----------------------------------------------------------------------------------+----------------------------------+-----------------------+-------+-------+-----+------------------+----------------+-----------------+------------------+---------------------------------------------------------------------------------------------------+------------------+----------------+-----------------+------------------+------------------+--------------------------------------------------------------------------------+------------------+--------------------------------------------------------------------------+--------------------------------------------+--------------------------------------------+---------+----------------------+--------------+------------------+-----------+-------------+------+----------+------+-------------------------+-----------------------+------------------+--------+--------+--------------+-------+
  +
| logins | if(dv.filter_id != '', dv.filter_id, if(tp.filter_id is null, '', tp.filter_id)) | if(dv.ip>0, INET_NTOA(dv.ip), 0) | INET_NTOA(dv.netmask) | tp_id | speed | cid | total_time_limit | day_time_limit | week_time_limit | month_time_limit | UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD(curdate(), INTERVAL 1 MONTH), '%Y-%m-01')) - UNIX_TIMESTAMP() | total_traf_limit | day_traf_limit | week_traf_limit | month_traf_limit | octets_direction | if (count(un.uid) + count(tp_nas.tp_id) = 0, 0, if (count(un.uid)>0, 1, 2)) | UNIX_TIMESTAMP() | UNIX_TIMESTAMP(DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP()), '%Y-%m-%d')) | DAYOFWEEK(FROM_UNIXTIME(UNIX_TIMESTAMP())) | DAYOFYEAR(FROM_UNIXTIME(UNIX_TIMESTAMP())) | disable | max_session_duration | payment_type | credit_tresshold | rad_pairs | count(i.id) | age | callback | port | traffic_transfer_period | neg_deposit_filter_id | ext_bill_account | credit | ippool | join_service | tp_id |
  +
+--------+----------------------------------------------------------------------------------+----------------------------------+-----------------------+-------+-------+-----+------------------+----------------+-----------------+------------------+---------------------------------------------------------------------------------------------------+------------------+----------------+-----------------+------------------+------------------+--------------------------------------------------------------------------------+------------------+--------------------------------------------------------------------------+--------------------------------------------+--------------------------------------------+---------+----------------------+--------------+------------------+-----------+-------------+------+----------+------+-------------------------+-----------------------+------------------+--------+--------+--------------+-------+
  +
| 1 | | 172.16.129.72 | 255.255.255.255 | 260 | 0 | | 0 | 0 | 0 | 0 | 115649 | 0 | 0 | 0 | 0 | 0 | 0 | 1298814751 | 1298757600 | 1 | 58 | 0 | 0 | 0 | 0.00 | | 1 | 0 | 0 | 0 | 0 | | 1 | 0.00 | 0 | 0 | 33 |
  +
+--------+----------------------------------------------------------------------------------+----------------------------------+-----------------------+-------+-------+-----+------------------+----------------+-----------------+------------------+---------------------------------------------------------------------------------------------------+------------------+----------------+-----------------+------------------+------------------+--------------------------------------------------------------------------------+------------------+--------------------------------------------------------------------------+--------------------------------------------+--------------------------------------------+---------+----------------------+--------------+------------------+-----------+-------------+------+----------+------+-------------------------+-----------------------+------------------+--------+--------+--------------+-------+
  +
1 row in set (0.00 sec)
 
</PRE>
  
</PRE>

Текущая версия на 16:11, 27 февраля 2011

Query

select  
 if (dv.logins=0, if(tp.logins is null, 0, tp.logins), dv.logins) AS logins,  
 if(dv.filter_id != '', dv.filter_id, if(tp.filter_id is null, '', tp.filter_id)),  
 if(dv.ip>0, INET_NTOA(dv.ip), 0),  INET_NTOA(dv.netmask),  
 dv.tp_id,  
 dv.speed,  
 dv.cid,    
 tp.total_time_limit,  
 tp.day_time_limit,  
 tp.week_time_limit, 
 tp.month_time_limit,  
 UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD(curdate(), INTERVAL 1 MONTH), '%Y-%m-01')) - UNIX_TIMESTAMP(),  
 tp.total_traf_limit,  
 tp.day_traf_limit,  
 tp.week_traf_limit,  
 tp.month_traf_limit,  
 tp.octets_direction,  
 if (count(un.uid) + count(tp_nas.tp_id) = 0, 0,    if (count(un.uid)>0, 1, 2)),UNIX_TIMESTAMP(),  UNIX_TIMESTAMP(DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP()), '%Y-%m-%d')), DAYOFWEEK(FROM_UNIXTIME(UNIX_TIMESTAMP())),  DAYOFYEAR(FROM_UNIXTIME(UNIX_TIMESTAMP())),  
dv.disable,  
tp.max_session_duration,  
tp.payment_type,  
tp.credit_tresshold, 
tp.rad_pairs,  
count(i.id),  
tp.age,  
dv.callback,  
dv.port,  
tp.traffic_transfer_period,  
tp.neg_deposit_filter_id,  
tp.ext_bill_account,  
tp.credit,  
tp.ippool,dv.join_service,  
tp.tp_id     
FROM 
 (dv_main dv)     
LEFT JOIN 
 tarif_plans tp 
ON 
 (dv.tp_id=tp.id AND tp.domain_id='0')     
LEFT JOIN 
 users_nas un 
ON 
(un.uid = dv.uid)     
LEFT JOIN 
 tp_nas 
ON 
 (tp_nas.tp_id = tp.tp_id)
LEFT JOIN 
intervals i 
ON 
(tp.tp_id = i.tp_id)     WHERE dv.uid='1720'GROUP BY dv.uid;


res 1 (work)

+--------+----------------------------------------------------------------------------------+----------------------------------+-----------------------+-------+-------+-----+------------------+----------------+-----------------+------------------+---------------------------------------------------------------------------------------------------+------------------+----------------+-----------------+------------------+------------------+--------------------------------------------------------------------------------+------------------+--------------------------------------------------------------------------+--------------------------------------------+--------------------------------------------+---------+----------------------+--------------+------------------+-----------+-------------+------+----------+------+-------------------------+-----------------------+------------------+--------+--------+--------------+-------+
|      1 |                                                                                  | 172.16.129.72                    | 255.255.255.255       |   260 |     0 |     |                0 |              0 |               0 |                0 |                                                                                            114523 |                0 |              0 |               0 |                0 |                0 |                                                                              0 |       1298815877 |                                                               1298757600 |                                          1 |                                         58 |       0 |                    0 |            0 |             0.00 |           |           1 |    0 |        0 |    0 |                       0 |                       |                1 |   0.00 |      0 |            0 |    33 |
+--------+----------------------------------------------------------------------------------+----------------------------------+-----------------------+-------+-------+-----+------------------+----------------+-----------------+------------------+---------------------------------------------------------------------------------------------------+------------------+----------------+-----------------+------------------+------------------+--------------------------------------------------------------------------------+------------------+--------------------------------------------------------------------------+--------------------------------------------+--------------------------------------------+---------+----------------------+--------------+------------------+-----------+-------------+------+----------+------+-------------------------+-----------------------+------------------+--------+--------+--------------+-------+
1 row in set (0.01 sec)

res 2 (not work)

+--------+----------------------------------------------------------------------------------+----------------------------------+-----------------------+-------+-------+-----+------------------+----------------+-----------------+------------------+---------------------------------------------------------------------------------------------------+------------------+----------------+-----------------+------------------+------------------+--------------------------------------------------------------------------------+------------------+--------------------------------------------------------------------------+--------------------------------------------+--------------------------------------------+---------+----------------------+--------------+------------------+-----------+-------------+------+----------+------+-------------------------+-----------------------+------------------+--------+--------+--------------+-------+
| logins | if(dv.filter_id != '', dv.filter_id, if(tp.filter_id is null, '', tp.filter_id)) | if(dv.ip>0, INET_NTOA(dv.ip), 0) | INET_NTOA(dv.netmask) | tp_id | speed | cid | total_time_limit | day_time_limit | week_time_limit | month_time_limit | UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD(curdate(), INTERVAL 1 MONTH), '%Y-%m-01')) - UNIX_TIMESTAMP() | total_traf_limit | day_traf_limit | week_traf_limit | month_traf_limit | octets_direction | if (count(un.uid) + count(tp_nas.tp_id) = 0, 0,    if (count(un.uid)>0, 1, 2)) | UNIX_TIMESTAMP() | UNIX_TIMESTAMP(DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP()), '%Y-%m-%d')) | DAYOFWEEK(FROM_UNIXTIME(UNIX_TIMESTAMP())) | DAYOFYEAR(FROM_UNIXTIME(UNIX_TIMESTAMP())) | disable | max_session_duration | payment_type | credit_tresshold | rad_pairs | count(i.id) | age  | callback | port | traffic_transfer_period | neg_deposit_filter_id | ext_bill_account | credit | ippool | join_service | tp_id |
+--------+----------------------------------------------------------------------------------+----------------------------------+-----------------------+-------+-------+-----+------------------+----------------+-----------------+------------------+---------------------------------------------------------------------------------------------------+------------------+----------------+-----------------+------------------+------------------+--------------------------------------------------------------------------------+------------------+--------------------------------------------------------------------------+--------------------------------------------+--------------------------------------------+---------+----------------------+--------------+------------------+-----------+-------------+------+----------+------+-------------------------+-----------------------+------------------+--------+--------+--------------+-------+
|      1 |                                                                                  | 172.16.129.72                    | 255.255.255.255       |   260 |     0 |     |                0 |              0 |               0 |                0 |                                                                                            115649 |                0 |              0 |               0 |                0 |                0 |                                                                              0 |       1298814751 |                                                               1298757600 |                                          1 |                                         58 |       0 |                    0 |            0 |             0.00 |           |           1 |    0 |        0 |    0 |                       0 |                       |                1 |   0.00 |      0 |            0 |    33 | 
+--------+----------------------------------------------------------------------------------+----------------------------------+-----------------------+-------+-------+-----+------------------+----------------+-----------------+------------------+---------------------------------------------------------------------------------------------------+------------------+----------------+-----------------+------------------+------------------+--------------------------------------------------------------------------------+------------------+--------------------------------------------------------------------------+--------------------------------------------+--------------------------------------------+---------+----------------------+--------------+------------------+-----------+-------------+------+----------+------+-------------------------+-----------------------+------------------+--------+--------+--------------+-------+
1 row in set (0.00 sec)
Источник — https://noname.com.ua/mediawiki/index.php?title=Test&oldid=3170