mysql 多表联合做运算（求俩点的距离）

1. 现有俩张表

表一 PL 为管道表 p1.p2 的xy坐标在坐标表里 p1,p2为开始点和结束点

 

 

表二为坐标表 PP

需求：根据开始点结束点坐标。算出所有管道距离

 

 给出sql

SELECT
	a.NAME name,SUM(a.s1) length
FROM
	(
		SELECT
			r1.id,
			r1. NAME,
			(r1.x - r2.x) dif_x,
			(r1.y - r2.y) dif_y,
			((r1.x - r2.x) *(r1.x - r2.x)) + ((r1.y - r2.y) *(r1.y - r2.y)) s,
			POWER(
				((r1.x - r2.x) *(r1.x - r2.x)) + ((r1.y - r2.y) *(r1.y - r2.y)),
				1 / 2
			) s1
		FROM
			(
				SELECT
					(@rownum := @rownum + 1) AS rownum,
					tab.id,
					tab. NAME,
					tab.x,
					tab.y
				FROM
					(
						SELECT
							PL.id,
							PL. NAME,
							pp.x,
							pp.y
						FROM
							PL
						LEFT JOIN PP ON pl.p1 = pp.id
						OR pl.p2 = pp.id
					) tab,
					(SELECT @rownum := 0) r
			) r1
		LEFT JOIN (
			SELECT
				(@rownum := @rownum + 1) AS rownum,
				tab.id,
				tab. NAME,
				tab.x,
				tab.y
			FROM
				(
					SELECT
						PL.id,
						PL. NAME,
						pp.x,
						pp.y
					FROM
						PL
					LEFT JOIN PP ON pl.p1 = pp.id
					OR pl.p2 = pp.id
				) tab,
				(SELECT @rownum := 0) r
		) r2 ON r1.id = r2.id
		AND r1.rownum = r2.rownum - 1
	) a GROUP BY a.`NAME`