借助子查询或连接显示在不同的行中。如何生成此查询以显示适当的结果 [英] Show in out in different rows with the help of subqueries or join or.how do i generate this query to show the appropriate results

问题描述

MY table is

8:50 AM IN

10:50 AM OUT

12:50 PM

19:56 PM OUT



希望将此显示为

IN OUT

8.50 AM 10.50 AM

12.50 PM 19.56 PM



我的尝试：



选择*，（从attendance_time选择时间，其中emp_id ='18017'和status ='OUT'）为OUT，（从attendance_time选择时间，其中emp_id ='18017'，状态='IN'）为[IN]来自attendance_time

解决方案

假设每个out都有一个相应的in，这样的东西应该可以工作：

< pre lang =SQL> SELECT
I.emp_id，
I。[时间] 作为 [在]，
O。[时间] 作为 [Out]
FROM
dbo.attendance_time 作为 I
OUTER APPLY
（
SELECT TOP 1
O。[时间]
FROM
attendance_time 作为 O
WHERE
O.emp_id = I.emp_id
和
O。[时间]> = I。[时间]
和
O .Status = ' OUT'
ORDER BY
O。[时间]
）作为 O
WHERE
I.Status = ' IN'
;

您需要使用 pivot进行单个查询

在SQL查询中使用Pivot的简单方法

[ ^ ] 使用PIVOT和UNPIVOT [ ^ ]

使用&＃39;将行转换为列枢轴和放大器;＃39;在SQL Server中 - 堆栈溢出 [ ^ ]

Sql Server中的PIVOT和UNPIVOT | SqlHints.com [ ^ ]

MY table is
8:50 AM IN
10:50 AM OUT
12:50 PM IN
19:56 PM OUT

want to show this as
IN OUT
8.50 AM 10.50AM
12.50 PM 19.56 PM

What I have tried:

select * ,(select time from attendance_time where emp_id='18017' and status='OUT') as OUT,(select time from attendance_time where emp_id='18017' and status='IN') as [IN] from attendance_time

解决方案

Assuming every "out" will have a corresponding "in", something like this should work:

SELECT
    I.emp_id,
    I.[Time] As [In],
    O.[Time] As [Out]
FROM
    dbo.attendance_time As I
    OUTER APPLY
    (
        SELECT TOP 1
            O.[Time]
        FROM
            attendance_time As O
        WHERE
            O.emp_id = I.emp_id
        And
            O.[Time] >= I.[Time]
        And
            O.Status = 'OUT'
        ORDER BY
            O.[Time]
    ) As O
WHERE
    I.Status = 'IN'
;

You need to do a single query using a pivot
Simple Way To Use Pivot In SQL Query
[^]Using PIVOT and UNPIVOT[^]
Convert Rows to columns using &#39;Pivot&#39; in SQL Server - Stack Overflow[^]
PIVOT and UNPIVOT in Sql Server | SqlHints.com[^]

这篇关于借助子查询或连接显示在不同的行中。如何生成此查询以显示适当的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！