在进行sql查询时需要帮助 [英] Need help in making sql query

问题描述

您好，

我的表中有两行对应员工代表，如下表所示。第一行记录In Time，第二行记录Out Time。

EmpId，AttendanceTime

1 07:30

1 04:30

2 07:35

2 04:45



现在我想读取Table1中的数据并存储每个表2中单行Empid，Intime，OutTime中的员工数据。如何为此目的进行SQL查询

Hello,
I have two rows corresponding to employee attendace in my table say Table1 as shown below. First row records the In Time and second row records the Out Time.
EmpId ,AttendanceTime
1 07:30
1 04:30
2 07:35
2 04:45

Now i want to read data from Table1 and store each employee data in the columns Empid,Intime,OutTime in single row of Table2. How to make sql query for this purpose

推荐答案

我不太清楚，但我认为你需要类似的。这只是一个例子。

i am not much clear but i think you need similar. this is just an example.

create table Attendance
(
	Emp varchar(10), 
	LoginDateTime datetime, 
	AttendanceType tinyint
)

insert into Attendance values ('John', GETDATE()-1,0)
insert into Attendance values ('John', GETDATE(), 1)

SELECT Emp as 'Employee', [0] as 'Intime', [1] as 'Outtime'
FROM (
    SELECT
    AttendanceType,
        LoginDateTime, Emp
    FROM Attendance
) as temp
PIVOT
(
    max(LoginDateTime)
    FOR AttendanceType in ([0], [1])
)AS pvoit

你好，



你可以试试这个SQL查询

Hello,

You can try this sql query

SELECT EmpID AS EmployeeID
	, AttendanceTime AS InTime
	, (SELECT AttendanceTime FROM Employee WHERE EmpID = EmployeeID 
           AND InTime < AttendanceTime) AS OutTime
FROM Employee
GROUP BY EmpID;

希望这会有所帮助



http：// streetrat .in / Articles / Content / Sample-Sql-Server-Pivot-Query104.aspx？RatHistory = 104 [ ^ ]



这类似于上下文你的。





插入table2（用户名，银泰，停机时间）

select * from pvresult

--pivotquery结果



希望你能得到这个

Hope this helps

http://streetrat.in/Articles/Content/Sample-Sql-Server-Pivot-Query104.aspx?RatHistory=104[^]

This is similar to the context of your's.


insert into table2 (username, Intime, outtime)
select * from pvresult
--pivotquery result

hope u get this

这篇关于在进行sql查询时需要帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！