一、mysql中子查询与百分率的用法
子查询:select (select pe.pname from people pe where pe.pid=j.id) name from job j where j.id='1' ;
讲解:一般来说,在mysql中,表与表之间的关联,不要用join来解决,因为join查询速度比子查询要慢的多,故在数据库统计中尽量不用join来关联表与表之间的关系。
百分率:CONCAT(actualNum*100/SUM(t1.totalNum),'%')可以使表中以百分率的形式出现(其中,sum是求和函数,concat(str1,str2,.....)函数将str1,str2,.....合并在一起)
如右图:
count(*)函数用于统计符合某一条件的数据总数,如
SELECT COUNT(*) FROM people pe WHERE pe.id='1'是指表people在id='1'的条件下数据的个数,然后再用sum函数对其进行求和
group by将数据进行分组表示:
如:select t1.id,t1.name,sum(t1.money) from (select p.id,p.name,p.money from people p,job j where j.id=p.id)t1 group by t1.id
解释:对新表t1中按t1.id进行分组统计,并对每组中money进行求和
二、表中套表
简单例子:
select t1.id,t1.name,sum(t1.money) from (select p.id,p.name,p.money from people p,job j where j.id=p.id)t1
解释:将查出来的数据组成一个新表t1,然后在新表中将数据查出来
对于这样做的好处,如下面的综合例子,将复杂的子查询在新表t1中实现(如:SELECT pa.id,(SELECT COUNT(*) FROM people pe WHERE pl.id=pe.id),这样有利于在查询统计时方便实现,如CONCAT(actualNum*100/SUM(t1.totalNum),'%') actalper。如果不这样,直接对表进行查询统计,
就是这样的:
CONCAT(sum(,(SELECT COUNT(*) FROM people pe WHERE pl.id=pe.id AND pe.state='3'))*100/SUM( (SELECT pa.id,(SELECT COUNT(*) FROM people pe WHERE pl.id=pe.id)),'%')
可以(上述红色字体)比较着两者的复杂程度,可见前一个是比较简单易懂的,而且有些方法可以共用
综合例子:
SELECT SUM(t1.totalNum) totalNum,SUM(t1.actualNum) actualNum,CONCAT(actualNum*100/SUM(t1.totalNum),'%') actalper FROM (SELECT pa.id,(SELECT COUNT(*) FROM people pe WHERE pl.id=pe.id)totalNum ,(SELECT COUNT(*) FROM people pe WHERE pl.id=pe.id AND pe.state='3')actualNum FROM paper pa, plan pl,organization tpe where pa.id =pl.id AND pl.id in (SELECT peo.id FROM people peo WHERE peo.orgId in (SELECT org.orgId from organization org where org.pIds LIKE CONCAT('%/',1,'/%')))) t1 GROUP BY t1.id
如有疑问,欢迎指教!如有不足,请指教,欢迎各位留言!
这是本人原创,如要转载请注明出处