如何根据no设置我的rowspan值。 查询中的结果，如果它是一个组或根据其设置ID设置，则更改该值？

Question

Good day! I'm just new here and I hope someone or anyone can help me with my problem.

I've searched this site already and I found some near answer to my question but it's a bit now the answer I'm seeking.

I want to know how to merge rowspan based on the returned result in sql. I have this query:select * from equipments. then this is the table:

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

Then this is what I want to output:

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

Sorry it's a bit messy. as u can see, there are two types of computer set, the one with fixed price and the one with individual price. the first computer set is a fixed dell computer set that's why it has a set id, the other one is an assembled computer set(parts are like from different brands), which have their set id as a whole positive no.

I hope someone can help me, please!! thank ypu so much in advance!

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');

First Calculate total costs for sets where set_id is a number (testing the first character for an ascii number)

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

result

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

Then join equipments to this adding a rownumber

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

result

+-----------+----------------+------------------+----------+----------+-------+------+--------+----------+
| 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)

And finally decide what we want to display based on rownumber

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

Result

+------------------+----------+-------+------+--------+
| 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)

Having said all that it's probably best done in PHP (not one of my languages)