Zapytania SQL, monitoring

"SELECT mk.key, sum(mc.cost) FROM `monitoring_history`
 mh, monitoring_keys mk, monitoring_cost mc WHERE mh.key_id = mc.key_id 
and mh.key_id = mk.key_id and mh.site_id = mk.site_id and mh.`site_id` =
 889 AND mh.`data` BETWEEN '2015-01-01' AND '2015-01-31' and mh.position
 <= mc.to and mh.position >= mc.from group by mk.key"
 
 
 
SELECT * FROM `monitoring_keys` where `site_id` = 889 

SELECT `position` FROM `monitoring_history` WHERE `data`= '2015-05-01' AND `site_id` = 889
 
 
SELECT mk.key, sum(mc.cost) FROM `monitoring_history`
 mh, monitoring_keys mk, monitoring_cost mc WHERE mh.key_id = mc.key_id 
and mh.key_id = mk.key_id and mh.site_id = mk.site_id and mh.`site_id` =
 889 AND mh.`data` BETWEEN '2015-01-01' AND '2015-01-31' and mh.position
 <= mc.to and mh.position >= mc.from group by mk.key
 
`monitoring_keys`
  interesują nas tutaj:
 
site_id - id strony
key_id - id frazy
key - fraza
 
`monitoring_history`
interesują nas tutaj:
  
`position` - pozycja strony
`data` - danego dnia
 
 
 
 
SELECT k.`site_id`, k.`key_id`, k.`key`, h.`position`, h.`data` FROM `monitoring_keys` k  NATURAL JOIN `monitoring_history` h WHERE k.`site_id`= 889 AND h.`data` BETWEEN '2015-05-01' AND CURDATE();
  
 
OSTATNIE ZAPYTANIE, DOBRE
 
SELECT k.`site_id`, k.`key_id`, k.`key`, h.`position`, h.`data` FROM `monitoring_keys` k join `monitoring_history` h on k.`key_id` = h.`key_id` WHERE k.`site_id`= 889 AND h.`data` BETWEEN '2015-05-01' AND '2015-06-26'
   
   
SELECT k.`site_id`, k.`key_id`, k.`key`, h.`position`, h.`data`, c.`cost` FROM `monitoring_keys` k join `monitoring_history` h on k.`key_id` = h.`key_id` join `monitoring_cost` c on (c.`key_id` = h.`key_id` AND h.`position` BETWEEN c.`from` AND c.`to`) WHERE k.`site_id`= 889 AND h.`data` BETWEEN '2015-05-01' AND '2015-06-26';
   
SELECT k.`key`, h.`position`, h.`data`, c.`cost` FROM `monitoring_keys` k join `monitoring_history` h on k.`key_id` = h.`key_id` join `monitoring_cost` c on (c.`key_id` = h.`key_id` AND h.`position` BETWEEN c.`from` AND c.`to`) WHERE k.`site_id`= 889 AND h.`data` BETWEEN '2015-05-01' AND '2015-06-30';
Advertisements

Skomentuj

Wprowadź swoje dane lub kliknij jedną z tych ikon, aby się zalogować:

Logo WordPress.com

Komentujesz korzystając z konta WordPress.com. Log Out / Zmień )

Zdjęcie z Twittera

Komentujesz korzystając z konta Twitter. Log Out / Zmień )

Facebook photo

Komentujesz korzystając z konta Facebook. Log Out / Zmień )

Google+ photo

Komentujesz korzystając z konta Google+. Log Out / Zmień )

Connecting to %s