Daren help me.

gaofei1114

The table is as follows:
gh rq zbs jbgs
810364 2016/2/21 8 0
800004 2016/4/17 8 2.5
810065 2016/7/13 8 0
810068 2016/2/14 8 2.5
810003 2016/10/25 8 ​​2.5
   ...
   ...
   ...
   ...
   ...

All I have to do now is
1. Statistics of all employees in 2016 for each month and overtime each year. The requirement is to total the employee gh for each month's jbgs greater than 36, and the monthly output
2.The gh output of employees whose jbgs totaled more than (36 * 12) 432 in 2016.
3. Compare the result of 2 with zgs.jbs in another table (gzb). Output the GH of different employees and the month of the current month.
zbs = sum of all zbs in the above table for one month
jbs = the sum of all jbgs in the table above (the two groups are originally the same for several years. But some data in GZB have been manually changed. So I want to output different output.)
Thank you.

sinexe

1
select gh, year (rq), month (rq), sum (jbgs)
group by gh, year (rq), month (rq)
having sum (jbgs)> 36

2
select gh, year (rq), sum (jbgs)
group by gh, year (rq)
having sum (jbgs)> 36 * 12

3 don't understand

gaofei1114

Hehe. The first table records the daily GH.JBGS. There is also a zbs in the table (gzb), jbgs is the sum of the corresponding time of each day in the first table. It is also the zbs, jbgs of each employee in the month. And. The two columns of data in the second table have been manually changed, so they are not compatible with that result. Now I want to check them. Which ones have been manually changed.

wgfxman

--1\statistics of all employees in 2016, each month, overtime every year. The requirement is to output the total employee gh of each month's jbgs greater than 36, and the monthly output.

select gh, convert (char (7), rq, 111) YF, SUM (zbs) zbs, sum (jbgs) jbgs
--into GZB_MONTH
 FROM GZMXB WHERE YEAR (rq) = '2016'
 group by gh, convert (char (7), rq, 111)
having sum (jbgs)> 36

--2\outputs the gh output of employees whose total jbgs exceeded (36 * 12) 432 in 2016.
select gh, YEAR (rq) NF, SUM (zbs) zbs, sum (jbgs) jbgs
--into GZB_YEAR
 FROM GZMXB WHERE YEAR (rq) = '2016'
 group by gh, YEAR (rq)
having sum (jbgs)> 432

--3\compares the result of 2 with zgs.jbs in another table (gzb). The GH of different employees and the month of the current month are output.

SELECT A.gh, B.gh, A.YF, B.YF, A.zbs, B.zbs, A.jbgs, B.jbgs
FROM GZB_MONTH A FULL JOIN gzb B ON A.gh = B.gh and A.YF = B.YF
WHERE A.zbs <> B.zbs OR A.jbgs <> B.jbgs

sinexe

Result 2 is a summary of the year, but gzb is a summary of the month?

gaofei1114

Hehe. I made a mistake. Sorry.

gaofei1114

Seems to work. Thank you. Thank you so much.