mysql行比较游标vs连接

I need to compare all rows in a table and need to do some calculation on every comparison.

我需要比较表中的所有行,并且需要对每个比较进行一些计算。

For eg: I have 3 users.I need to compare a->b and give some marks to b and insert this in another table and then a->c do the calculation and store in the above same table and then need to compare b->c and do the calculation and insert the calculated value to a table.then c->a,c->b,-a do same as above.

例如:我有3个用户。我需要比较a-> b并给b一些标记并将其插入另一个表中然后a-> c进行计算并存储在上面的同一个表中然后需要比较b - > c并进行计算并将计算出的值插入表中。然后c-> a,c-> b,-a与上面相同。

How can we do these kind of comparison ? should I Use cursor ? any other simple ways ?

我们怎样才能进行这种比较?我应该使用光标吗?还有其他简单方法吗?

I am able to write a simple join query like this but no idea how to do calculation and insert this into another table.

我能够写这样一个简单的连接查询,但不知道如何进行计算并将其插入另一个表中。

id,userid,lat,lng is the table structure. I need to return lat and lng in each comparison ( I mean When I do a user to user) and then assign a value to second user as described above.id is pk and userid will be a non repeat value and its unique .Basically while returning lat and lng I need to check the distance calculation for each users based on one to one comparison and giving marks based on the distance then storing these marks in another table based on the user id.

id,userid,lat,lng是表结构。我需要在每次比较中返回lat和lng(我的意思是当我向用户执行用户时),然后如上所述为第二个用户分配一个值.id是pk,userid将是一个非重复值并且它是唯一的.Basically while返回lat和lng我需要根据一对一的比较检查每个用户的距离计算,并根据距离给出标记,然后根据用户ID将这些标记存储在另一个表中。

Select * from table t1 join table t2 on t1.userid!=t2.userid

从t1.userid!= t2.userid上的表t1连接表t2中​​选择*

2 个解决方案

#1

Distance formula
DELIMITER $$ 

DROP   FUNCTION  IF EXISTS `GetDistance`$$ 

CREATE   FUNCTION  `GetDistance`( 
 lat1  numeric (9,6), 
 lon1  numeric (9,6), 
 lat2  numeric (9,6), 
 lon2  numeric (9,6) 
)  RETURNS   decimal (10,5) 
BEGIN 
  DECLARE  x  decimal (20,10); 
  DECLARE  pi  decimal (21,20); 
  SET  pi = 3.14159265358979323846; 
  SET  x = sin( lat1 * pi/180 ) * sin( lat2 * pi/180  ) + cos( 
 lat1 *pi/180 ) * cos( lat2 * pi/180 ) * cos(  abs ( (lon2 * pi/180) - 
 (lon1 *pi/180) ) ); 
  SET  x = atan( ( sqrt( 1- power( x, 2 ) ) ) / x ); 
  RETURN  ( 1.852 * 60.0 * ((x/pi)*180) ) / 1.609344; 
END $$ 

DELIMITER ;

I need to apply following points based on the distance

我需要根据距离应用以下几点

30 miles or less = 2 points
    30 - 50 = 1 points
    50 and up = 0 points

#2

I have created some thing like and works

我创造了一些喜欢和工作的东西

INSERT INTO summary (user_id1, user_id2, distance, points)
SELECT a1.userid, a2.userid, 
    GetDistances(a1.lat,a1.lng,a2.lat,a2.lng) as distance,
GetDistance(a1.lat,a1.lng,a2.lat,a2.lng) as points FROM location a1 JOIN location a2 ON a1.userid <> a2.userid
  ON DUPLICATE KEY UPDATE 
    distance=VALUES(distance),
    points=points+values(points);

For points

DELIMITER $$ 

DROP   FUNCTION  IF EXISTS `GetDistance`$$ 

CREATE   FUNCTION  `GetDistance`( 
 lat1  numeric (9,6), 
 lon1  numeric (9,6), 
 lat2  numeric (9,6), 
 lon2  numeric (9,6) 
)  RETURNS   decimal (10,5) 
BEGIN 
  DECLARE  x  decimal (20,10); 
  DECLARE  pi  decimal (21,20);
  DECLARE final decimal (10,5);
DECLARE newvalue decimal (10,5);  
  SET  pi = 3.14159265358979323846; 
  SET  x = sin( lat1 * pi/180 ) * sin( lat2 * pi/180  ) + cos( 
 lat1 *pi/180 ) * cos( lat2 * pi/180 ) * cos(  abs( (lon2 * pi/180) - 
 (lon1 *pi/180) ) ); 
  SET  x = atan( ( sqrt( 1- power( x, 2 ) ) ) / x ); 
  SET final =  ( 1.852 * 60.0 * ((x/pi)*180) ) / 1.609344;

case WHEN final < 247 THEN  
SET newvalue = 2;
WHEN final > 600 THEN
SET newvalue = 1; 
else SET newvalue = 0;
END CASE;
RETURN newvalue;
END $$ 

DELIMITER ;

For distance

DELIMITER $$ 

DROP   FUNCTION  IF EXISTS `GetDistances`$$ 

CREATE   FUNCTION  `GetDistances`( 
 lat1  numeric (9,6), 
 lon1  numeric (9,6), 
 lat2  numeric (9,6), 
 lon2  numeric (9,6) 
)  RETURNS   decimal (10,5) 
BEGIN 
  DECLARE  x  decimal (20,10); 
  DECLARE  pi  decimal (21,20);
  SET  pi = 3.14159265358979323846; 
  SET  x = sin( lat1 * pi/180 ) * sin( lat2 * pi/180  ) + cos( 
 lat1 *pi/180 ) * cos( lat2 * pi/180 ) * cos(  abs( (lon2 * pi/180) - 
 (lon1 *pi/180) ) ); 
  SET  x = atan( ( sqrt( 1- power( x, 2 ) ) ) / x ); 
 RETURN ( 1.852 * 60.0 * ((x/pi)*180) ) / 1.609344;
END $$ 

DELIMITER ;