mysql 多表联合做运算

1. 现有俩张表

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

mysql 多表联合做运算

 

 

表二为坐标表 PP

mysql 多表联合做运算

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

mysql 多表联合做运算

 

 给出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`

 

mysql 多表联合做运算

上一篇:CAS 5.3.1系列之支持JDBC认证登录(二)


下一篇:网络对抗技术实验二