变态算法

liming495

浏览: 149654 次
性别:
来自: 耒阳

最近访客更多访客>>

jiangbing9293

gumuzhiyu

随遇羽翔

abc123

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

SELECT *
,(((20015000 - ROUND(6378.138*2*ASIN(SQRT(POW(SIN((22.299439*PI()/180-latitude*PI()/180)/2),2)+COS(22.299439*PI()/180)*COS(latitude*PI()/180)*POW(SIN((114.173881*PI()/180-longitude*PI()/180)/2),2)))*1000)) / 20015000) * 0.3)
,((CASE WHEN tags LIKE '%,1,%' THEN 1 ELSE 0 END) * 0.1 ) + ((CASE WHEN tags LIKE '%,2,%' THEN 1 ELSE 0 END) * 0.1 ) + ((CASE WHEN tags LIKE '%,3,%' THEN 1 ELSE 0 END) * 0.1 )
,ifnull((vitality / max_vitality * 0.3) ,0) + ifnull(((CASE WHEN tags LIKE '%,1,%' THEN 1 ELSE 0 END) * 0.1 ) ,0) + ifnull(((CASE WHEN tags LIKE '%,2,%' THEN 1 ELSE 0 END) * 0.1 ) ,0) + ifnull(((CASE WHEN tags LIKE '%,3,%' THEN 1 ELSE 0 END) * 0.1 ) ,0) + ifnull((((20015000 - ROUND(6378.138*2*ASIN(SQRT(POW(SIN((22.299439*PI()/180-latitude*PI()/180)/2),2)+COS(22.299439*PI()/180)*COS(latitude*PI()/180)*POW(SIN((114.173881*PI()/180-longitude*PI()/180)/2),2)))*1000)) / 20015000) * 0.3) ,0)
FROM same_channel_info, (SELECT MAX(vitality) AS max_vitality FROM same_channel_info) AS t_max_vitality
WHERE (tags LIKE '%,1,%') OR (tags LIKE '%,2,%') OR (tags LIKE '%,3,%')
ORDER BY (
ifnull((vitality / max_vitality * 0.3) ,0)
+ ifnull(((CASE WHEN tags LIKE '%,1,%' THEN 1 ELSE 0 END) * 0.1 ) ,0)
+ ifnull(((CASE WHEN tags LIKE '%,2,%' THEN 1 ELSE 0 END) * 0.1 ) ,0)
+ ifnull(((CASE WHEN tags LIKE '%,3,%' THEN 1 ELSE 0 END) * 0.1 ) ,0)
+ ifnull((((20015000 - ROUND(6378.138*2*ASIN(SQRT(POW(SIN((22.299439*PI()/180-latitude*PI()/180)/2),2)+COS(22.299439*PI()/180)*COS(latitude*PI()/180)*POW(SIN((114.173881*PI()/180-longitude*PI()/180)/2),2)))*1000)) / 20015000) * 0.3) ,0)
) DESC;


SELECT * ,((CASE WHEN tags LIKE '%,1,%' THEN 1 ELSE 0 END) * 0.1 )
+ ((CASE WHEN tags LIKE '%,2,%' THEN 1 ELSE 0 END) * 0.1 )
+ ((CASE WHEN tags LIKE '%,3,%' THEN 1 ELSE 0 END) * 0.1 )
FROM same_channel_info 
WHERE (tags LIKE '%,1,%') OR (tags LIKE '%,2,%') OR (tags LIKE '%,3,%') 
ORDER BY (
((CASE WHEN tags LIKE '%,1,%' THEN 1 ELSE 0 END) * 0.1 )
+ ((CASE WHEN tags LIKE '%,2,%' THEN 1 ELSE 0 END) * 0.1 )
+ ((CASE WHEN tags LIKE '%,3,%' THEN 1 ELSE 0 END) * 0.1 )
) DESC;

分享到：