mysql使⽤存储过程，给临时列创建索引排序, CREATE PROCEDURE `testOrgan`()
BEGIN
DROP TABLE IF EXISTS tmp_table;
CREATE TEMPORARY TABLE `tmp_table` (
`city_code` varchar(32) DEFAULT NULL COMMENT '城市代码',
`ins_code` varchar(32) DEFAULT NULL COMMENT '城市代码',
`organ_type` varchar(12) DEFAULT NULL COMMENT '机构类型，H：医院；P：药店',
`organ_name` varchar(64) NOT NULL COMMENT '机构名称',
`organ_address` varchar(512) DEFAULT NULL COMMENT '机构地址',
`organ_access_url` varchar(512) DEFAULT NULL COMMENT '机构跳转地址',
`state` varchar(1) DEFAULT NULL COMMENT '状态',
`longitude` double DEFAULT NULL COMMENT '经度',
`latitude` double DEFAULT NULL COMMENT '维度',
`area` varchar(32) DEFAULT NULL COMMENT '区域',
`organ_category` varchar(32) DEFAULT NULL COMMENT '机构类⽬：医院资质、药店品牌',
`adCode` varchar(32) DEFAULT NULL COMMENT '区域编码',
`pyCode` varchar(32) DEFAULT NULL COMMENT '拼⾳⾸字母',
`organ_catagery` varchar(20) DEFAULT NULL COMMENT '机构类别，现在区分少⼉与成⼈。少⼉：children',
`dis` double DEFAULT NULL COMMENT '距离',
KEY `citycode` (`city_code`),
KEY `inscode` (`ins_code`) USING BTREE,
KEY `diS` (`dis`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin COMMENT = '' ROW_FORMAT = Compact;
insert into tmp_table(
city_code,
ins_code,
organ_type,
organ_name,
organ_address,
organ_access_url,
longitude,
latitude,
area,
organ_category,
adCode,
pyCode,
organ_catagery,
dis ) SELECT
city_code,
研究生复试自我介绍ins_code,
organ_type,
organ_name,
organ_address,
organ_access_url,
longitude,
latitude,
立夏的习俗是什么?area,
organ_category,
adCode,
pyCode,
organ_catagery,
ROUND(
6378.393 * 2 * ASIN(
SQRT(
POW(
SIN(
(
30.0
* PI() / 180 - latitude * PI() / 180
) / 2
),
2
) +
COS(30.0 * PI() / 180) * COS(latitude * PI()
/
180) * POW(
南瓜饼怎么做才好吃又简单SIN(
(
130.0 * PI() / 180 - longitude
上海经济适用房条件* PI() / 180
) / 2
交通银行信用卡进度),
2
)
)
) * 1000 * 1
)/1000 as dis
FROM
wh_institution
WHERE
city_code = 'SZ0755' and ins_code = 'SZHRSS' and organ_type = 'P' and state = '1' ;
SELECT
city_code,
ins_code,
organ_type,
organ_name,
organ_address,
organ_access_url,
longitude,
latitude,
area,
organ_category,
adCode,
pyCode,
organ_catagery,
dis from tmp_table order by dis;
END
>洪泽县邮编
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论