Advertisement
campocreek

Untitled

Nov 20th, 2017
77
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.70 KB | None | 0 0
  1. UPDATE migrate.seo_available sa
  2. INNER JOIN (
  3. SELECT
  4. 1 as SA_available
  5. , t.seo_model_id as SA_model_id
  6. , si.id as SA_izdelie_id
  7. , COUNT(DISTINCT pa.id) as SA_count_OLD
  8. ,COUNT(DISTINCT
  9. IF (IF(t.min_available_price IS NOT NULL, CEIL(t.min_available_price), NULL) IS NOT NULL,
  10. IF (pa.avalaible_gen = 1, pa.id, NULL),
  11. pa.id)
  12. ) AS SA_count
  13. , IF(t.min_available_price IS NOT NULL, CEIL(t.min_available_price), NULL) as min_price
  14. FROM (
  15. SELECT
  16. ta.dok_razdel_id
  17. , ta.seo_model_id
  18. , MIN(IF(pa.avalaible_gen = 1, pa.price_torg, null)) as min_available_price
  19. , si.id as sid
  20. FROM tecdoc_art2 ta
  21. LEFT JOIN prices_all/*_duplicate*/ pa ON ta.art_id = pa.pa_art_id
  22. INNER JOIN seo_izdelie si ON ta.dok_razdel_id = si.dok_razdel_id
  23. INNER JOIN seo_model sm ON ta.seo_model_id = sm.id
  24. LEFT JOIN virtual_seo_models vsm ON ta.seo_model_id = vsm.seo_model_id
  25. WHERE
  26. pa.avalaible_gen IN (0, 1)
  27. AND ta.seo_model_id > 0 -- AND sm.id = 21 AND si.dok_razdel_id = 141
  28. AND vsm.seo_model_id IS NULL
  29. AND ta.seo_model_id = 304
  30. GROUP BY ta.seo_model_id, si.id
  31. ) t
  32. LEFT JOIN tecdoc_art2 ta ON t.seo_model_id = ta.seo_model_id AND t.dok_razdel_id = ta.dok_razdel_id
  33. LEFT JOIN prices_all/*_duplicate*/ pa ON ta.art_id = pa.pa_art_id
  34.  
  35. INNER JOIN seo_izdelie si ON t.sid = si.id
  36. INNER JOIN seo_model sm ON ta.seo_model_id = sm.id
  37.  
  38. WHERE
  39. pa.avalaible_gen IN (0, 1)
  40. AND pa.price_torg >= IFNULL(t.min_available_price, 0)
  41.  
  42. GROUP BY t.seo_model_id, t.sid
  43. ORDER BY SA_model_id, SA_izdelie_id
  44. ) t ON sa.SA_model_id = t.SA_model_id AND sa.SA_izdelie_id = t.SA_izdelie_id
  45. SET
  46. sa.SA_count = t.SA_count
  47. , sa.min_price = t.min_price
  48. , sa.SA_available = t.SA_available
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement