随笔分类 -  sql

mysql 计算经纬度函数（米）
摘要：CREATE DEFINER=`root`@`%` FUNCTION `latlon`(lat1 DOUBLE,lon1 DOUBLE,lat2 DOUBLE,lon2 DOUBLE) RETURNS varchar(200) CHARSET utf8mb4 begin return ROUND( 6378.138 * 2 * ASIN( SQRT( ... 阅读全文
posted @ 2018-10-16 16:29 itvita 阅读(2533) 评论(0) 推荐(0) 编辑
mysql 查询所有父级名称
摘要：SELECT T2.id, T2.name FROM ( SELECT @r AS _id, @stop:=@stop+if(@r=2,1,@stop) as stop, (SELECT @r := p_id FROM goods_class WHERE id = _id) AS p_id, @l := @l + ... 阅读全文
posted @ 2018-08-09 17:04 itvita 阅读(2537) 评论(0) 推荐(0) 编辑
mysql 统计一个列不同值的数量 /统计某表不同状态值的数量
摘要：SELECT count(status = 0 OR NULL) AS a, count(status = 1 OR NULL) AS b, count(status = 2 OR NULL) AS c, count(status = 3 OR NULL) AS d, count(status = 阅读全文
posted @ 2018-07-02 16:16 itvita 阅读(1929) 评论(1) 推荐(0) 编辑
SQL语句 合并列值 将一列的多个值合并成一行
摘要：效果: oralce写法： select WM_CONCAT(A.title) as citys from tmpcity A sql server写法： select stuff((select ','+A.title from tmpCity A FOR xml PATH('')), 1, 1, 阅读全文
posted @ 2017-12-14 18:02 itvita 阅读(24938) 评论(10) 推荐(1) 编辑