sql 总结

### sql语句select group by order by where一般先后顺序

写的顺序：select … from… where…. group by… having… order by..
执行顺序：from… where…group by… having…. select … order by…

### ###### order by和group by一起使用的注意事项

1.order by 的列，必须是出现在group by 子句里的列
2.order by 要 放在 group by的 后面

distinct 与 group by

去重和统计是用 distinct 还是 group by，distinct 去重的，而group by 则是用来聚合统计的。单纯的去重操作使用distinct，速度是快于group by的。

获取已下单用户统计，示例数据
[ { "uid": "2190", "oid": "158" }, { "uid": "2190", "oid": "159" }, { "uid": "2190", "oid": "160" }, { "uid": "2190", "oid": "161" }, { "uid": "2190", "oid": "167" }, { "uid": "2193", "oid": "169" }, { "uid": "2193", "oid": "170" }, { "uid": "2193", "oid": "177" }, { "uid": "2193", "oid": "178" } ]
使用 group by uid 结果
{ "uidcount": "5" }, { "uidcount": "4" }
使用 count(distinct uid) 结果
{ "uidcount": "2" }

##### #### ### CAST(x AS type) 转换数据类型函数

字符串日期转换为日期：SELECT CAST(“2017-08-29” AS DATE);
-> 2017-08-29

使用 sum 统计精度丢失，数值类型转换
$total = pdo_fetchcolumn(‘SELECT cast(SUM(fee) as DECIMAL(10,2)) as fee FROM ‘. tablename(‘mc_credits_recharge’) .’ WHERE openid=:openid AND uniacid=:uniacid AND type='credit' AND status=1’, array(‘:openid’ => $openid, ‘:uniacid’ => $weid));

##### #### #### GROUP_CONCAT 分组查询 group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator ‘分隔符’])

GROUP_CONCAT(字段) 查询出来的字段会默认使用英文逗号分隔，查询id集合很有用