如何显示表结果列并在行上显示它

问题描述：

enter image description here

Can someone help me create a table in where I can use the other rows as columns?

My sample database looks like this:

|    NAME | DATE             |    TIME     |TYPE |
|    john    | 03-05-2017  |    06:00    | AM IN
|    john    | 03-05-2017  |    11:00    | AM OUT
|    john    | 03-05-2017  |    13:00    | PM IN
|    john    | 03-05-2017  |    16:00    | PM OUT

| john | 03-06-2017  | 06:00 | AM IN
| john | 03-06-2017  | 11:00 | AM OUT

| john | 03-07-2017  | 06:00 | AM IN
| john | 03-07-2017  | 11:00 | AM OUT
| john | 03-07-2017  | 13:00 | PM IN
| john | 03-07-2017  | 16:00 | PM OUT

How can I show it like this in PHP table:

|    DATE      | AM IN  |AM OUT   |    PM IN    | PMOUT
03-05-2017   06:00     |11:00        |   13:00      |16:00
03-06-2017   06:00     |11:00        |   NULL       | NULL
03-07-2017   06:00    |11:00         |    13:00      |16:00

I have uploaded the image so that I can explain it better

有人可以帮我创建一个表，我可以将其他行用作列吗？ p>

我的示例数据库看起来像这样： p>

  |  NAME | 日期|  TIME | TYPE | 
 | 约翰|  03-05-2017 |  06:00 |  AM IN 
 | 约翰|  03-05-2017 |  11:00 |  AM OUT 
 | 约翰|  03-05-2017 |  13:00 |  PM IN 
 | 约翰|  03-05-2017 |  16:00 |  PM OUT 
 
 | 约翰|  03-06-2017 |  06:00 |  AM IN 
 | 约翰|  03-06-2017 |  11:00 |  AM OUT 
 
 | 约翰|  03-07-2017 |  06:00 |  AM IN 
 | 约翰|  03-07-2017 |  11:00 |  AM OUT 
 | 约翰|  03-07-2017 |  13:00 |  PM IN 
 | 约翰|  03-07-2017 |  16:00 |  PM OUT 
  code>  pre> 
 
 如何在PHP表格中显示如下： p> 
 
 
  | 日期|  AM IN | AM OUT |  PM IN |  PMOUT 
03-05-2017 06:00 | 11:00 |  13:00 | 16:00 
03-06-2017 06:00 | 11:00 |  NULL |  NULL 
03-07-2017 06:00 | 11:00 |  13:00 | 16:00 
  code>  pre> 
 
 我上传了图片以便我能更好地解释 p> 
  div>

答

I think you just want a pivot query:

SELECT
    NAME,
    DATE,
    MAX(CASE WHEN TYPE = 'AM IN'  THEN TIME END) AS AM_IN,
    MAX(CASE WHEN TYPE = 'AM OUT' THEN TIME END) AS AM_OUT,
    MAX(CASE WHEN TYPE = 'PM IN'  THEN TIME END) AS PM_IN,
    MAX(CASE WHEN TYPE = 'PM OUT' THEN TIME END) AS PM_OUT
FROM yourTable
GROUP BY
    NAME, DATE;

如何显示表结果列并在行上显示它

相关推荐