sql 分组，效率，抽样 

 1 select counters ,input_user ,hours,minutes,counters/minutes as perMinute,counters/hours as perHour from
 2 (
 3 SELECT count(*) counters,`录入员` input_user,
 4        timestampdiff(hour, '2013-04-21 14:21:43', '2013-04-21 14:35:12') as
 5        hours,
 6        timestampdiff(minute, '2013-04-21 14:21:43', '2013-04-21 14:35:12') as
 7        minutes
 8 FROM `zinvest`
 9 where  `录入时间` between '2013-04-21 14:21:43' and
10       '2013-04-21 14:35:12'
11   group by `录入员`
12 ) as t1
13 
14 
15 
16 
17 -- 统计调查表完成数 
18 select `区县`,
19        `乡`,
20        `村`,
21        `区编码`,
22        `乡编码`,
23        `村编码`,
24        `档案号`,
25        `卷号`,
26        count(0) as '数量'
27 from zinvest
28 group by `区编码` , `乡编码`, `村编码`, `档案号` , `卷号`
29 order by '数量' desc
30 
31 
32 --按人员查看工作量
33 select `区县`,
34        `乡`,
35        `村`,
36        `区编码`,
37        `乡编码`,
38        `村编码`,
39        `档案号`,
40        `卷号`,
41        `录入员`,
42        count(0) as '数量'
43 from zinvest
44 group by `区编码` , `乡编码`, `村编码`, `档案号` , `卷号`,  `录入员`
45 order by '数量' desc
46 
47 
48 
49 
50 -- 随机抽取n条样式查看  
51 select * from zinvest as t1
52 join (
53 select round(rand()*(select max(id) from zinvest t2)) as id
54 ) as t2
55 
56 where t1.id >=t2.id and t1.`录入员`='test2'
57  limit 10

 

1 --select minzu ,count(case  when gender=1 then 1  end )as 'male',count(case when gender=2 then 1 end ) as 'female' from  wj_vaccine20131010 group by minzu

 

posted @ 2013-12-11 14:35  zhangxiaodel  阅读(1482)  评论(0)    收藏  举报