2019独角兽企业重金招聘Python工程师标准>>>  
 
 
 
 
sql使用相同的条件： 给出统计页面和查询内容 
 
 
public List<MyInfo>  Get_MyInfoList(string name,string address,string gender,out int total)
{
List<MyInfo> myInfoList=new List<MyInfo>();
try
{
 IDbShell shell = Common.GetIDbShell(ActionType.Read);
 string sqlDetails=@"select a.code,a.name,a.`no` ,b.address,GROUP_CONCAT(distinct ifnull(a.`name`,a.`no`)) as username,GROUP_CONCAT(distinct b.no) as  infono from registinfo a
	            inner join userinfo b on a.code=b.org_code and b.`status`='A'   
	            inner join reluser c on c.no=a.no and c.`status`='A'
	            left join  trueuser d  on d.no=a.`no` and d.`status`='A'	          
	            left join  user_detail as e on e.no=a.`no` e.`status`='A'
	            where e.`status`='A' and ( e.code like  CONCAT(@0,'.','%') or e.code =@0) 
                 and (@1 is null) or (a.`name` like concat('%',@1,'%'))
                 and (@2 is null) or (a.`gender` like concat('%',@2,'%'))
              group by no;";
string sqlCount="select count(1) from ("+sqlDetails+")  as  total;";
sqlDetails=sqlDetails+"limit @3,@4;";
DataSet ds = shell.ExecuteQueryDataSet(sqlDetails+ sqlCount, code, name, gender,(pageIndex - 1) * pageSize, pageSize);
total = int.Parse(ds.Tables[0].Rows[0][0].ToString());
foreach(DataRow item in ds.Tables[1].Rows)
{
//遍历
}
}
catch(Exception ex)
{
throw ex;
}
}
 
 
 
 
 
 
  
 
    
  
 
 
 
 
转载于:https://my.oschina.net/u/3544533/blog/1788607

查询某一字段的某一个值的总条数
 
比如：orderprogressno字段的值分别为1,2,3,4,那么就是查询值为1的总条数和值为2的总条数
 
select orderno,count(orderprogressno)from servicerepair
 已阅读  = sum(case when orderprogressno =1 then 1 else 0 end)，
 未阅读  = sum(case when orderprogressno =2 then 1 else 0 end)，
 from servicerepair
 

 select orderno ,
 sum(case when orderprogressno =1 then 1 else 0 end)，as yiyuedu
 sum(case when orderprogressno =1 then 1 else 0 end)，as weiyuedu
 from servicerepair group by orderno
 

 mybatis下代码：
 
<select id="selectCountBylevel" parameterType="java.util.Map" resultType="java.util.Map">
 	select count (case when orderprogressno =1 then 1 else null end) 已阅读,
			count (case when orderprogressno =2 then 1 else null end) 已联系,
			count (case when orderprogressno =3 then 1 else null end) 已预约,
			count (case when orderprogressno =4 then 1 else null end) 正在服务,
			count (case when orderprogressno =5 then 1 else null end) 已经完成,
			count (case when orderprogressno =6 then 1 else null end) 已经结束,
			count (case when orderprogressno =7 then 1 else null end ) 已经取消
			from servicerepair where deptno =#{deptno}
</select>
 
 

Mysql 分组之后如何统计记录条数并且去重复?
 
gourp by 之后的 count，把group by查询结果当成一个表再count一次:
 
select count(*) as count from
(SELECT count(*) FROM 表名 WHERE 条件 GROUP BY id ) a;
 

  

MySQL 分组后，统计记录条数
 
分组后，统计记录条数：
 
　　SELECT num,count(*) AS counts from test_a GROUP BY num; 
 
　　查询结果如下：
 
　　
 
 
 
对num去重后的数量的统计：
 
　　SELECT count(t.counts) FROM ( SELECT num,count(*) AS counts from test_a GROUP BY num ) AS t;
 
　　SELECT count(DISTINCT num) AS count FROM test_a;
 
　　它俩结果一样，都是5；只是一个是子查询（嵌套），一个是内置函数 distinct（）；
 
 
 
 
 
数据库结构
 
 
转载：https://www.cnblogs.com/laowenBlog/p/6081949.html