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
