如何在动态数据透视表上显示最小值和最大值 [英] How to show Min and Max value on dynamic pivot table

问题描述

我从 dt_table join 和 dt_k 创建了一个数据透视表

I created a pivot table from dt_table join with dt_k

table dt_k
+------+--------+-----+
| id_k | name_k | ott |
+------+--------+-----+
|  1   | item 1 | ss  |
|  2   | item 2 | ss  |
|  3   | item 3 | ww  |
|  4   | item 4 | dd  |
|  5   | item 5 | asa |
|  6   | item 6 | rr  |
+------+--------+-----+

dt_table
+------+--------+------+----+
| id_t |  id_u  | id_k | k  |
+------+--------+------+----+
|  1   |   22   |  1   | 2  |
|  2   |   22   |  2   | 3  |
|  3   |   22   |  3   | 23 |
|  4   |   22   |  4   | 4  |
|  5   |   22   |  6   | 34 |
|  6   |   24   |  1   | 23 |
|  7   |   24   |  2   | 34 |
|  8   |   24   |  3   | 54 |
|  9   |   24   |  4   | 21 |
| 11   |   24   |  6   | 44 |
+------+--------+------+----+

我使用数据透视表将行转换为列，这是我的代码:

I am use pivot table to convert row to column this my code:

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
  'max(case when col = ''',
  col,
  ''' then value end) as `', 
  col, '`')
) INTO @sql
FROM
(
select concat('op_', `id_k`) col
from dt_table

) d;
SET @sql = CONCAT('SELECT  d.id_u as id,', @sql, ' 
              from
              (
                select id_k, id_u, concat(''op_'', `id_k`) col,  k value
                from dt_table

              ) d group by id_u ');


PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

此代码给出如下结果

+------+------+------+------+------+------+
| id_u | op_1 | op_2 | op_3 | op_4 | op_6 |
+------+------+------+------+------+------+
|  22  |   2  |  3   |  23  |  4   |  34' |
|  24  |  23  |  34  |  54  |  21  |  44  |
+------+------+------+------+------+------+

我需要这个表像这样显示每一列的最小值和最大值

I need this table show min and max value on each column like this

+------+------+------+------+------+------+
| id_u | op_1 | op_2 | op_3 | op_4 | op_6 |
+------+------+------+------+------+------+
|  22  |   2  |  3   |  23  |  4   |  34  |
|  24  |  23  |  34  |  54  |  21  |  44  |
+------+------+------+------+------+------+

+------+------+------+------+------+------+
| id_u | op_1 | op_2 | op_3 | op_4 | op_6 |
+------+------+------+------+------+------+
| min  |   2  |  3   |  23  |  4   |  34  |
| max  |  23  |  34  |  54  |  21  |  44  |
+------+------+------+------+------+------+

谢谢你的帮助..最近工作的这个资源http://sqlfiddle.com/#!9/297674/1

Thanks for your help.. This resource for recent work http://sqlfiddle.com/#!9/297674/1

推荐答案

以下问题没有解决哪一部分?

What part of the problem does the following fail to solve?

<?php

include('path/to/connection/statme.nts');

$query = "
SELECT id_u
     , id_k
     , k
  FROM dt_table
 ORDER 
    BY id_u
     , id_k;
";

$result = mysqli_query($conn,$query);

$array = array();

while($row = mysqli_fetch_assoc($result)){
  $array[] = $row;
}

$new_array = array();

foreach($array as $v){
  $new_array[$v['id_u']][] = $v['k'];
}

print_r($new_array);
?>

这篇关于如何在动态数据透视表上显示最小值和最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！