在SQL SERVER中格式化输出

问题描述：

我有一个表，其中数据以以下格式存储:

Hi,

I have a table where data is stored in following format:

UseriD      IN                          OUT
90000024    2012-01-05 10:55:00.000     NULL
90000024    NULL                        2012-01-05 18:29:00.000
90000027    2012-01-04 14:09:00.000     NULL
90000027    2012-01-05 13:46:00.000     NULL
90000031    2012-01-03 09:56:00.000     NULL
90000031    NULL                        2012-01-03 18:09:00.000
90000031    2012-01-04 09:56:00.000     NULL
90000031    NULL                        2012-01-04 18:05:00.000
90000031    2012-01-05 09:54:00.000     NULL
90000031    NULL                        2012-01-05 18:03:00.000
90000033    2012-01-03 09:31:00.000     NULL

我想以以下方式选择记录，其中相应的IN记录将匹配相应的OUT记录:

I want to select record in following manner where the corresponding IN record will match the corresponding OUT record:

UseriD      IN                          OUT
90000024    2012-01-05 10:55:00.000     2012-01-05 18:29:00.000
90000027    2012-01-04 14:09:00.000     NULL
90000027    2012-01-05 13:46:00.000     NULL
90000031    2012-01-03 09:56:00.000     2012-01-03 18:09:00.000
90000031    2012-01-04 09:56:00.000     2012-01-04 18:05:00.000
90000031    2012-01-05 09:54:00.000     2012-01-05 18:03:00.000
90000033    2012-01-03 09:31:00.000     NULL

有时可能会出现对应的IN记录没有OUT记录的情况，反之亦然.我想过滤记录，使对应的IN记录与对应的OUT记录相匹配，并且如果某些IN/OUT没有IN/OUT记录记录，那么NULL应该在那儿.

请帮助我.
感谢

It may happen that sometimes there may be no OUT records for corresponding IN records OR vice versa.I want to filter the records where the corresponding IN record will match the corresponding OUT record and if there is no IN/OUT record for some IN/OUT records then NULL should be there.

Please help me with this.
Thanks

答

创建两个查看器，并使用输入和输出日期将它们加入.

例如:.具有UserID和IN时间的第一个查看器
第二个具有UserID和OUT时间的查看器
最终将这两个具有用户ID的查看者加入其中，并且只添加日期部分以比较CONVERT(VARCHAR(8)，IN，112)= CONVERT(VARCHAR(8)，OUT，112)

我希望这能解决您的问题.

create two viewers and join them with IN and OUT dates.

Eg:. First viewer with UserID and IN time
Second viewer with UserID and OUT time
finally join this two viewers with User ID and only dates part like this to compare CONVERT(VARCHAR(8), IN, 112)=CONVERT(VARCHAR(8), OUT, 112)

I hope this will solve your problem.

在SQL SERVER中格式化输出

相关推荐