1.FOR XML PATH
碰到一个这样的需求,需要查询出电影的所有场次,于是就接触到了FOR XML PATH。
想要得到的就是这样的效果。首先看一下sql语句。
SELECT DISTINCT
show_time + '|'
FROM
api_film_show tt
WHERE
tt.cinema_id = t1.cinema_id FOR XML PATH ('')
) AS show_time
通过”|“来分割出不同的时间段的场次。其次比较重要的就是path这个参数,通过里面定义相应的值,可以定义为相应的根节点名称,如果不给值则无显示,通过这个函数语句,可以达到获取单个电影院下该电影的所有场次。这个语句对我来讲是很不错的了。
2.坐标计算
简单的讲,就是通过经纬度坐标进行计算两地之间的直线距离,可以通过坐标拾取系统获取相应的经纬度来进行测试。
接下来说一下sql语句。
ALTER FUNCTION [f_GetDistance]
(
@GPSLng DECIMAL(12,6),
@GPSLat DECIMAL(12,6),
@Lng DECIMAL(12,6),
@Lat DECIMAL(12,6)
)
RETURNS DECIMAL(12,4)
AS
BEGIN
DECLARE @result DECIMAL(12,4)
SELECT @result = 6378137.0*ACOS(SIN(@GPSLat/180*PI())*SIN(@Lat/180*PI())+COS(@GPSLat/180*PI())*COS(@Lat/180*PI())*COS((@GPSLng-@Lng)/180*PI()))
RETURN @result
END
看sql语句,总共需要传入四个参数,代入到电影票业务上来说,依次代表本人所处的经度,纬度,电影院的经度,纬度,通过相应的运算获取到两点之间的直线距离。值得注意的是,如果你对sqlserver不熟悉(比如LZ),一定记住在调用的时候加入dbo.表名,不然会一直报错不成功。
接下来我把完整的sql语句展现出来,说老实话,写完还是对自己有点作用的。
SELECT
a.cinema_id,
a.cinema_name,
a.cinema_logo,
a.cinema_add,
a.region_id,
a.contact,
a.get_ticket_way,
a.latitude,
a.longitude,
b.user_price,
b.show_time,
dbo.f_GetDistance (
121.508186,31.289124,
a.longitude,
a.latitude
) AS distance
FROM
api_film_cinema a
JOIN (
SELECT
t1.cinema_id,
MIN (t1.user_price) AS user_price,
(
SELECT DISTINCT
show_time + '|'
FROM
api_film_show tt
WHERE
tt.cinema_id = t1.cinema_id FOR XML PATH ('')
) AS show_time
FROM
api_film_show t1
JOIN api_film_cinema t2 ON t1.cinema_id = t2.cinema_id
JOIN api_film_city t3 ON t2.city_id = t3.city_id
AND t3.city_name = '上海'
AND t1.film_id = '201805370875'
AND
t1.show_date = '2018-05-14'
GROUP BY
t1.cinema_id
) b ON a.cinema_id = b.cinema_id;
3.String转int进行数值运算
CAST (t1.show_time AS INT) + CAST (t1.duration AS INT) AS end_time,
将相应的string类型转化为int,关键就是cast语句块,调用完成即可进行相应的数值运算。
4.sql优化自己理解
4.1,查询出需要展现的字段,不要查询出多余的字段,以免影响速度,比如电影票中一张表会有超多的字段,这个时候就需要根据实际场景需要进行定义dto取值即可。
4.2,在费时费力的字段上面建立索引,比如日期上面我们就可以建立一个非聚集索引,这样可以大大加强我们的查询速度。
4.3,对于多表之间的查询,少用left join,慎用子查询嵌套,尽可能的去进行优化。
4.4,仔细仔细再仔细。