1. SQL / Говнокод #5978

    −864

    1. 01
    2. 02
    3. 03
    4. 04
    5. 05
    6. 06
    7. 07
    8. 08
    9. 09
    10. 10
    11. 11
    12. 12
    13. 13
    14. 14
    15. 15
    16. 16
    17. 17
    18. 18
    19. 19
    20. 20
    21. 21
    22. 22
    23. 23
    24. 24
    25. 25
    26. 26
    27. 27
    28. 28
    29. 29
    30. 30
    31. 31
    32. 32
    33. 33
    34. 34
    35. 35
    36. 36
    37. 37
    38. 38
    39. 39
    SELECT foo.oper_id,
	                     foo.client_id,
			     base_clients.name,
			     base_clients.options,
			     base_providers.name,
	  		     base_subproviders.name,
			     base_clients.address_post,
			     base_clients.balance,
			     base_currency_types.descr,
			     base_clients.descr,
			     base_clients.client_type,
			     base_clients.pdn_permission_taken,
			     base_clients.person_use_srv_as_org,
	             base_clients.none_client
		      FROM   (SELECT oper_id,client_id FROM base_clients WHERE  (  oper_id = 'TT'  OR oper_id = 'SC'  OR oper_id = 'TT2'  OR oper_id = 'RC'  )   AND ( base_clients.client_id = '681843' OR base_clients.personal_id = '681843' OR base_clients.name ~ '[66][88][11][88][44][33]' OR base_clients.address_post ILIKE  '%681843%'OR base_clients.descr ILIKE '%681843%' )  UNION SELECT DISTINCT oper_id,client_id FROM telephone_services WHERE  (  oper_id = 'TT'  OR oper_id = 'SC'  OR oper_id = 'TT2'  OR oper_id = 'RC'  )  AND user_id ILIKE '%681843%'   UNION SELECT DISTINCT oper_id,client_id FROM channel_services WHERE  (  oper_id = 'TT'  OR oper_id = 'SC'  OR oper_id = 'TT2'  OR oper_id = 'RC'  )  AND user_id ILIKE '%681843%'   UNION SELECT DISTINCT oper_id,client_id FROM dry_pair_services WHERE  (  oper_id = 'TT'  OR oper_id = 'SC'  OR oper_id = 'TT2'  OR oper_id = 'RC'  )  AND user_id ILIKE '%681843%'   UNION SELECT DISTINCT oper_id,client_id FROM inet_dynamic_ip_services WHERE  (  oper_id = 'TT'  OR oper_id = 'SC'  OR oper_id = 'TT2'  OR oper_id = 'RC'  )  AND user_id ILIKE '%681843%'   
				UNION 
				    SELECT DISTINCT sss.oper_id, sss.client_id 
					FROM inet_fixed_ip_services as sss
					    left join inet_fixed_ip_group_items as itms on (sss.oper_id = itms.oper_id and sss.user_id = itms.ip_group )
				    WHERE 
					 (  sss.oper_id = 'TT'  OR sss.oper_id = 'SC'  OR sss.oper_id = 'TT2'  OR sss.oper_id = 'RC'  )  AND 
					(  
					    sss.user_id ILIKE '%681843%'  
						OR
					    itms.ip ILIKE '%681843%'  
					)
			     UNION SELECT DISTINCT oper_id,client_id FROM unix_shell_services WHERE  (  oper_id = 'TT'  OR oper_id = 'SC'  OR oper_id = 'TT2'  OR oper_id = 'RC'  )  AND user_id ILIKE '%681843%'   UNION SELECT DISTINCT oper_id,client_id FROM voip_ip_services WHERE  (  oper_id = 'TT'  OR oper_id = 'SC'  OR oper_id = 'TT2'  OR oper_id = 'RC'  )  AND user_id ILIKE '%681843%'   UNION SELECT DISTINCT oper_id,client_id FROM voip_card_services WHERE  (  oper_id = 'TT'  OR oper_id = 'SC'  OR oper_id = 'TT2'  OR oper_id = 'RC'  )  AND user_id ILIKE '%681843%'   UNION SELECT DISTINCT oper_id,client_id FROM ani_phone_services WHERE  (  oper_id = 'TT'  OR oper_id = 'SC'  OR oper_id = 'TT2'  OR oper_id = 'RC'  )  AND user_id ILIKE '%681843%'   UNION SELECT DISTINCT oper_id,client_id FROM cable_tv_services WHERE  (  oper_id = 'TT'  OR oper_id = 'SC'  OR oper_id = 'TT2'  OR oper_id = 'RC'  )  AND user_id ILIKE '%681843%'  UNION select distinct oper_id,client_id from base_client_aon_list where caller_phone = '681843' AND  (  oper_id = 'TT'  OR oper_id = 'SC'  OR oper_id = 'TT2'  OR oper_id = 'RC'  )  ) AS foo 
		             JOIN 
			     base_clients ON foo.oper_id=base_clients.oper_id AND 
			                     foo.client_id=base_clients.client_id 
			     JOIN 
			     base_providers ON base_clients.oper_id=base_providers.oper_id 
			     JOIN 
			     base_currency_types ON base_clients.oper_id=base_currency_types.oper_id AND 
			                            base_clients.currency_type=base_currency_types.currency_type
	                     LEFT JOIN
			     base_subproviders ON base_clients.oper_id=base_subproviders.oper_id AND
	                                          base_clients.suboper_id=base_subproviders.suboper_id 
	              ORDER BY to_number(base_clients.client_id,'999999999') DESC

    Это запрос на простой поиск клиента в биллинге.

    Запостил: spaceoflabview, 14 Марта 2011

    Комментарии (3) RSS

    • ava Lure Of Chaos 14.03.2011 23:09 # 0

      'TT' в голову
      Ответить

    • ava GhOsTMZ 15.03.2011 19:10 # 0

      гы) Смотрим №5979 и понимаем, что это детские шалости)
      Ответить

    • ava RomaShka 14.07.2011 14:30 # 0

      base_clients
      telephone_services
      channel_services
      dry_pair_services
      inet_dynamic_ip_services
      inet_fixed_ip_services
      unix_shell_services
      voip_ip_services
      voip_card_services
      ani_phone_services
      cable_tv_services
      base_client_aon_list
      и все это можно было в одну таблицу сделать, с гибкими атрибутивными полями...
      Или им там по-табличково платили?
      Ответить

    Добавить комментарий