sql常用查询语句
count(*)
1 2 3 4 | $sql_page_xy= "select count(*) from xy_tk_xueyuan where bianma_dw='" .$vv_bianma_dw. "' " ;
$result_page_xy=$mysqli->query($sql_page_xy);
$rs_page_xy=$result_page_xy->fetch_array();
$xueyuan_count=$rs_page_xy[0];
|
distinct取重复(此处不用group by可实现按组统计总数)
1 2 3 4 | $sql_page_cc= "select count(distinct xingming) from xy_tk_xueyuan where bianma_dw='" .$vv_bianma_dw. "' and banji_id='" .$rs[ "id" ]. "' " ;
$result_page_cc=$mysqli->query($sql_page_cc);
$rs_page_cc=$result_page_cc->fetch_array();
$chongming_count=$rs_page_cc[0];
|
SUM求和
1 2 3 4 5 | $sqlhg= "select SUM(keshi) AS keshi from xy_dingding_keshi where bianma_dw='" .$vv_bianma_dw. "' and banji_id='" .$banji_id. "' and kecheng_name in (" .$kaicheng_name_list. ") " ;
$resulthg=$mysqli->query($sqlhg);
if($rshg=$resulthg->fetch_assoc()){
$jieye_keshi=$rshg[ "keshi" ];
}
|
order by field 字符串排序
1 2 3 | $dd_id=trim($dd_id, "," );
$dd_id= "'" .str_replace( "," , "','" ,$dd_id). "'" ;
$sql= "select kaishi_time from xy_dingding where bianma_dw='" .$vv_bianma_dw. "' and id in (" .$dd_id. ") order by field(id," .$dd_id. ")" ;
|
like按匹配度倒序唯一查询结果
1 | $sqle= "select * from xy_dingding_info where kecheng_name='" .$e_kecheng_name. "' and bianma_dw='" .$vv_bianma_dw. "' and (xingming like '%" .$rs[ "xingming" ]. "%' or nicheng like '%" .$rs[ "xingming" ]. "%') order by ((xingming like '%" .$rs[ "xingming" ]. "%') + (nicheng like '%" .$rs[ "xingming" ]. "%') + (xingming like '%" .$rs[ "tel" ]. "%') + (nicheng like '%" .$rs[ "tel" ]. "%') ) desc limit 1 " ;
|
多条件求合
1 2 3 4 5 6 | 一次查询中,多个条件的数字分别统计
$sql_2= "select count(*) AS all_nums,SUM(CASE WHEN zhuangtai='未归还' THEN 1 ELSE 0 END) AS weiguihuan_num from dangxiao_zichan_jieyong_list where zhangtao='" .$_SESSION['dangxiao_zc_zhangtao ']."' and dh= '".$rs["dh"]."' ";
$result_2=$mysqli->query($sql_2);
$rs_2=$result_2->fetch_array();
$all_num=$rs_2[ "all_nums" ];
$weiguihuan_num=$rs_2[ "weiguihuan_num" ];*/
|
嵌套查询
1 2 3 4 5 6 7 8 9 10 11 12 13 | $sql_zidian= "select sfz from xy_tk_qiandao where banji_id='" .$banji_id. "' and (q_time='" .$date1. "') and sfz in (" .$search_pid_id. ") and " .$ziduan_v. "<>'' " ;
$result_zidian=$mysqli->query($sql_zidian);
$pm_str= '' ;
while ($rs_zidian=$result_zidian->fetch_assoc()){
$pm_str=$pm_str. "'" .$rs_zidian[ "sfz" ]. "'," ;
}
$search_pid_id=substr($pm_str,0,-1);
if ($search_pid_id== '' ){
$search_pid_id= "'xxx'" ;
}
$z_sql_str.= " and ( sfz in (" .$search_pid_id. ") ) " ;
|