将日期时间的值显示到 DataGridView 中的不同行 [英] Display values of datetime to different rows in a DataGridView

问题描述

这是我的桌子.

attendance
empID          int(11)
time_stamp     datetime
in_out         char(3)      *determines in / out of employee
am_pm          char(2)      *determines if morning/afternoon shift

下图显示了我试图想出的输出.我需要将 datetime 数据类型的 time_stamp 的值显示到 vb.net 中的 DataGrid 中.

The image below shows the output that I am trying to come up with. I need to display the values of time_stamp of datetime datatype into a DataGrid in vb.net.

我知道如何使用 date_format.我只是不知道如何选择数据让它们以这种方式显示.

I know how to use date_format. I just don't know how to SELECT the data to have them displayed that way.

数据库输入

empID  |  time_stamp          |   in_out  |   am_pm
  152  | 2016-01-01 12:00:00  |   out     |   am
  152  | 2016-01-01 08:00:00  |   in      |   am
  152  | 2016-01-02 12:00:00  |   out     |   am
  152  | 2016-01-02 08:00:00  |   in      |   am
  152  | 2016-01-03 12:00:00  |   out     |   am
  152  | 2016-01-03 08:00:00  |   in      |   am
  152  | 2016-01-01 05:00:00  |   out     |   pm
  152  | 2016-01-01 01:00:00  |   in      |   pm
  152  | 2016-01-02 05:00:00  |   out     |   pm
  152  | 2016-01-02 01:00:00  |   in      |   pm
  152  | 2016-01-03 05:00:00  |   out     |   pm
  152  | 2016-01-03 01:00:00  |   in      |   pm

员工 152 的期望输出

DESIRED OUTPUT for employee 152

DAY  |        AM           |        PM           |
1    | 8:00:00   12:00:00  |  1:00:00   5:00:00  |
2    | 8:00:00   12:00:00  |  1:00:00   5:00:00  |
3    | 8:00:00   12:00:00  |  1:00:00   5:00:00  |

推荐答案

您可以使用这样的查询:

You can use a query like this:

SELECT empID, emp_day, MAX(am_in1) as am_in, MAX(am_out1) as am_out, MAX(pm_in1) as pm_in, MAX(pm_out1) as pm_out
FROM (
SELECT empID, DATE_FORMAT(`time_stamp`,'%e') as emp_day,
CASE WHEN am_pm='am' AND in_out='in' THEN DATE_FORMAT(`time_stamp`,'%T') ELSE '' END as am_in1,
CASE WHEN am_pm='am' AND in_out='out' THEN DATE_FORMAT(`time_stamp`,'%T') ELSE '' END as am_out1,
CASE WHEN am_pm='pm' AND in_out='in' THEN DATE_FORMAT(`time_stamp`,'%T') ELSE '' END as pm_in1,
CASE WHEN am_pm='pm' AND in_out='out' THEN DATE_FORMAT(`time_stamp`,'%T') ELSE '' END as pm_out1
FROM attendance
) as o
GROUP BY empID, emp_day

子查询格式化所需的列和主查询组，以便每个员工每天获得一行.

The subquery formats the desired columns and the main query groups to get one row per employee per day.

这里有一个小提琴

这篇关于将日期时间的值显示到 DataGridView 中的不同行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！