An SQL statement that does not know how to write

Source: Internet
Author: User
There are two tables,
Payroll gongzi
Select emp_code, fenqi, GZ form gongzi. Store all employee salary information.

Emp_code fenqi GZ
001 200905 2500
001 200906 2600
001 200907 2600
002 200905 2000
002 200906 2100
002 200907 2300

Empcode = employee ID primary key
Fenqi = primary key of monthly installment
Gz = total monthly salary

Bonus table
Select emp_code, date, money, Rem form Jiangjin

Emp_code date money Rem
001 200 May 1 bonus
001 400 Prize
001 100 Prize
001 150 Prize
001 100 Prize
002 300 May 1 bonus
002 500 prize
002 200 Prize
002 250 Prize
002 300 Prize

Emp_code = employee ID, associated with the payroll ..
Date = specifies the date on which the bonus adjustment is recorded. It must also be associated with the installment of the payroll. For example, 2009-05-08,2009-05-31 should correspond to 200905 installments of the payroll.
Money = bonus, which is subject to the last adjustment and is included in the total salary at the end of the month.
Rem = remarks: record the reason for adjusting the bonus.

Now I have written a statement
Select top 1G. emp_code, fenqi, GZ, date, money, Rem
From gongzi g, Jiangjin J
Where G. emp_code = J. emp_code and G. fenqi = '000000'
And (datename (YY, date) + datename (mm, date) = g. fenqi
And G. emp_code = '001'
Order by date DESC

Query the information of a person in a specified period, including the bonus items adjusted after the group. You do not need to accumulate the total salary, because the bonus items are included in the adjustment.

The query result is
Emp_code fenqi GZ date money Rem
001 20090525002009-05-31 00:00:00. 000 400 Prize

Write another line to query the July information of the 001 employee.
Select top 1G. emp_code, fenqi, GZ, date, money, Rem
From gongzi g, Jiangjin J
Where G. emp_code = J. emp_code and G. fenqi = '000000'
And (datename (YY, date) + datename (mm, date) = g. fenqi
And G. emp_code = '001'
Order by date DESC

The query result is
Emp_code fenqi GZ date money Rem
001 20090626002009-06-30 00:00:00. 000 100 Prize

But now there is a problem. It's okay if one employee checks it separately.
The problem is that I select multiple employees for query. The result I want is that an employee has only one data record within the specified period, which is consistent with the individual query.

I tried to change the SQL statement
Select top 1G. emp_code, fenqi, GZ, date, money, Rem
From gongzi g, Jiangjin J
Where G. emp_code = J. emp_code and G. fenqi = '000000'
And (datename (YY, date) + datename (mm, date) = g. fenqi
And G. emp_code in ('001', '002 ')
Order by date DESC

The query result is
Emp_code fenqi GZ date money Rem
001 20090525002009-05-31 00:00:00. 000 400 Prize

The result I want is

The query result is
Emp_code fenqi GZ date money Rem
001 20090525002009-05-31 00:00:00. 000 400 Prize
002 200905 2000 00:00:00. 000 500 prize

Do not know how to write? Thank you for helping me.

The scripts for the two tables are as follows:

If exists (select * From sysobjects where id = object_id ('[gongzi]') and objectproperty (ID, 'isusertable') = 1)
Drop table [gongzi]

Create Table [gongzi] (
[Emp_code] [varchar] (50) not null,
[Fenqi] [varchar] (50) not null,
[GZ] [int] Null)

Alter table [gongzi] With nocheck add constraint [pk_gongzi] primary key nonclustered ([emp_code])
Insert [gongzi] ([emp_code], [fenqi], [GZ]) values ('001', '123', 200905)
Insert [gongzi] ([emp_code], [fenqi], [GZ]) values ('001', '123', 200906)
Insert [gongzi] ([emp_code], [fenqi], [GZ]) values ('001', '123', 200907)
Insert [gongzi] ([emp_code], [fenqi], [GZ]) values ('002 ', '123', 200905)
Insert [gongzi] ([emp_code], [fenqi], [GZ]) values ('002 ', '123', 200906)
Insert [gongzi] ([emp_code], [fenqi], [GZ]) values ('002 ', '123', 200907)

If exists (select * From sysobjects where id = object_id ('[Jiangjin]') and objectproperty (ID, 'isusertable') = 1)
Drop table [Jiangjin]

Create Table [Jiangjin] (
[Emp_code] [varchar] (50) null,
[Date] [datetime] Null,
[Money] [int] Null,
[REM] [varchar] (50) null)

Insert [Jiangjin] ([emp_code], [date], [money], [REM]) values ('001', '2017-05-08 0:00:00 ', 2009, 'May 1 ')
Insert [Jiangjin] ([emp_code], [date], [money], [REM]) values ('001', '2017-05-31 0:00:00 ', 2009, 'bonus ')
Insert [Jiangjin] ([emp_code], [date], [money], [REM]) values ('001', '2017-06-15 0:00:00 ', 2009, 'bonus ')
Insert [Jiangjin] ([emp_code], [date], [money], [REM]) values ('001', '2017-06-19 0:00:00 ', 2009, 'bonus ')
Insert [Jiangjin] ([emp_code], [date], [money], [REM]) values ('001', '2017-06-30 0:00:00 ', 2009, 'bonus ')
Insert [Jiangjin] ([emp_code], [date], [money], [REM]) values ('002 ', '2017-05-08 0:00:00', 2009, 'May 1 ')
Insert [Jiangjin] ([emp_code], [date], [money], [REM]) values ('002 ', '2017-05-31 0:00:00', 2009, 'bonus ')
Insert [Jiangjin] ([emp_code], [date], [money], [REM]) values ('002 ', '2017-06-15 0:00:00', 2009, 'bonus ')
Insert [Jiangjin] ([emp_code], [date], [money], [REM]) values ('002 ', '2017-06-19 0:00:00', 2009, 'bonus ')
Insert [Jiangjin] ([emp_code], [date], [money], [REM]) values ('002 ', '2017-06-30 0:00:00', 2009, 'bonus ')

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.