php - 如何根据编号设置我的行跨度值。查询中的结果并根据其集合 ID 更改值(如果它是组或集合)？

Question

美好的一天!我是新来的，我希望有人或任何人可以帮助我解决我的问题。

我已经搜索过这个网站，我找到了一些接近我的问题的答案，但它现在有点像我正在寻找的答案。

我想知道在sql中如何根据返回结果合并rowspan。我有这个查询:select * from equipments。那么这是表格:

article | prop_num | value | meas | set_id
++++++++++++++++++++++++++++++++++++++++++
cpu     | desk_123 |80,000 | set  | dell01
mouse   | desk_123 |80,000 | set  | dell01
keyboard| desk_123 |80,000 | set  | dell01
monitor | desk_123 |80,000 | set  | dell01
camera  | cam123   |40,000 | unit | 1
cpu     | desk234  |5,000  | set  | 2 
mouse   | desk234  |500    | set  | 3
keyboard| desk234  |500    | set  | 4
monitor | desk234  |2000   | set  | 5
printer | print111 |7,000  | unit | 6

那么这就是我要输出的内容:

article | prop_num | value | meas | set_id
++++++++++++++++++++++++++++++++++++++++++
Desktop | desk_123 |80,000 | set  | dell01
Computer| desk_123 |       | set  | dell01
        | desk_123 |       | set  | dell01
        | desk_123 |       | set  | dell01
camera  | cam123   |40,000 | unit | 1
Desktop | desk234  |8,000  | set  | 2 
Computer| desk234  |       | set  | 3
        | desk234  |       | set  | 4
        | desk234  |       | set  | 5
printer | print111 |7,000  | unit | 6

抱歉有点乱。如您所见，有两种类型的电脑，一种是固定价格，一种是单独定价。第一台电脑是一台固定的戴尔电脑，这就是为什么它有一个 set id，另一台是组装的电脑(零件来自不同品牌)，它们的 set id 整体为正数。

我希望有人能帮助我，拜托!!提前非常感谢你!

Answer 1

drop table if exists equipments;
create table equipments(article varchar(20), prop_num varchar(20), value int, meas varchar(20), set_id varchar(20));
insert into equipments values
('cpu'     , 'desk_123' ,80000 , 'set'  , 'dell01'),
('mouse'   , 'desk_123' ,80000 , 'set'  , 'dell01'),
('keyboard', 'desk_123' ,80000 , 'set'  , 'dell01'),
('monitor' , 'desk_123' ,80000 , 'set'  , 'dell01'),
('camera'  , 'cam123'   ,40000 , 'unit' , '1'),
('cpu'     , 'desk234'  ,5000  , 'set'  , '2'),
('mouse'   , 'desk234'  ,500   , 'set'  , '3'),
('keyboard', 'desk234'  ,500   , 'set'  , '4'),
('monitor' , 'desk234'  ,2000  , 'set'  , '5'),
('printer' , 'print111' ,7000  , 'unit' , '6');

首先计算 set_id 为数字的集合的总成本(测试 ascii 数字的第一个字符)

select prop_num,
        sum(value) as sumvalue
from equipments
where substring(set_id,1,1) between char(48) and char(57) and meas = 'set'
group by prop_num
order by prop_num

结果

+----------+----------+
| prop_num | sumvalue |
+----------+----------+
| desk234  |     8000 |
+----------+----------+
1 row in set (0.00 sec)

然后加入设备到这个添加行号

select 
        if(e.prop_num <> @p, @rn:=1,@rn:=@rn+1) Rownumber,
        @p:=e.prop_num,
        case when meas = 'set' then 'Desktop Computer'
        else article
        end
        as article1,
        e.*,
        case when s.prop_num is null then value
        else s.sumvalue
        end as sumvalue
from    equipments e
left join
(
select prop_num,
        sum(value) as sumvalue
from equipments
where substring(set_id,1,1) between char(48) and char(57) and meas = 'set'
group by prop_num
order by prop_num
) s 
on s.prop_num = e.prop_num
,(select @rn:=0,@p:='') rn
order by prop_num

结果

+-----------+----------------+------------------+----------+----------+-------+------+--------+----------+
| Rownumber | @p:=e.prop_num | article1         | article  | prop_num | value | meas | set_id | sumvalue |
+-----------+----------------+------------------+----------+----------+-------+------+--------+----------+
|         1 | cam123         | camera           | camera   | cam123   | 40000 | unit | 1      |    40000 |
|         1 | desk234        | Desktop Computer | cpu      | desk234  |  5000 | set  | 2      |     8000 |
|         2 | desk234        | Desktop Computer | monitor  | desk234  |  2000 | set  | 5      |     8000 |
|         3 | desk234        | Desktop Computer | keyboard | desk234  |   500 | set  | 4      |     8000 |
|         4 | desk234        | Desktop Computer | mouse    | desk234  |   500 | set  | 3      |     8000 |
|         1 | desk_123       | Desktop Computer | cpu      | desk_123 | 80000 | set  | dell01 |    80000 |
|         2 | desk_123       | Desktop Computer | monitor  | desk_123 | 80000 | set  | dell01 |    80000 |
|         3 | desk_123       | Desktop Computer | keyboard | desk_123 | 80000 | set  | dell01 |    80000 |
|         4 | desk_123       | Desktop Computer | mouse    | desk_123 | 80000 | set  | dell01 |    80000 |
|         1 | print111       | printer          | printer  | print111 |  7000 | unit | 6      |     7000 |
+-----------+----------------+------------------+----------+----------+-------+------+--------+----------+
10 rows in set (0.00 sec)

最后根据行号决定我们要显示什么

select case when t.rownumber = 1 then t.article1 else '' end as article,
         t.prop_num,
         case when t.rownumber = 1 then t.sumvalue else '' end as value,
         t.meas,
         t.set_id
from
(
select 
        if(e.prop_num <> @p, @rn:=1,@rn:=@rn+1) Rownumber,
        @p:=e.prop_num,
        case when meas = 'set' then 'Desktop Computer'
        else article
        end
        as article1,
        e.*,
        case when s.prop_num is null then value
        else s.sumvalue
        end as sumvalue
from equipments e
left join
(
select prop_num,
        sum(value) as sumvalue
from equipments
where substring(set_id,1,1) between char(48) and char(57) and meas = 'set'
group by prop_num
order by prop_num
) s 
on s.prop_num = e.prop_num
,(select @rn:=0,@p:='') rn
order by prop_num
) t
order by t.prop_num, t.rownumber

结果

+------------------+----------+-------+------+--------+
| article          | prop_num | value | meas | set_id |
+------------------+----------+-------+------+--------+
| camera           | cam123   | 40000 | unit | 1      |
| Desktop Computer | desk234  | 8000  | set  | 2      |
|                  | desk234  |       | set  | 5      |
|                  | desk234  |       | set  | 4      |
|                  | desk234  |       | set  | 3      |
| Desktop Computer | desk_123 | 80000 | set  | dell01 |
|                  | desk_123 |       | set  | dell01 |
|                  | desk_123 |       | set  | dell01 |
|                  | desk_123 |       | set  | dell01 |
| printer          | print111 | 7000  | unit | 6      |
+------------------+----------+-------+------+--------+
10 rows in set (0.00 sec)

说了这么多可能最好用 PHP(不是我的一种语言)