我有这个表
------------------------------------------
id | Machines
------------------------------------------
1. | 1 Truck
------------------------------------------
2. | 1 Bobcat
------------------------------------------
3. | 2 Platform
------------------------------------------
4. | Telehender
------------------------------------------
5. | Teodolit, 3 Platform
------------------------------------------
6. | 2 Tractor
------------------------------------------
7. | NULL
------------------------------------------
Result: | 11
------------------------------------------
我想首先对整数(1+1+2+3+2(求和,然后对没有整数的值求和计数(Telehender和Teodolit = 2(并跳过NULL 值。 此表的结果需要为 11。
我正在使用这个查询
SELECT Sum((Char_length(machines) - Char_length(Replace(machines, ',','')) + 1)) AS ukupno
FROM izvestaji
WHERE projekatid='8'
AND datum='2019-10-03'
但我得到的结果 = 9。
有没有办法进行该查询?
糟糕的数据库设计... :/但是,这是一个解决方案,我想您在其中不超过 3 个元素 进入机器领域
SELECT
sum(case
when length(machines)-length(replace(machines,',','')) = 0
then if( CAST(machines AS UNSIGNED) = 0, 1, cast(machines as unsigned))
when length(machines)-length(replace(machines,',','')) = 1
then ( if( CAST(SUBSTRING_INDEX(machines, ',', 1) AS UNSIGNED) = 0, 1, cast(SUBSTRING_INDEX(machines, ',', 1) as unsigned))) +
(if( CAST(replace(machines , concat(SUBSTRING_INDEX(machines, ',', 1),','),'') AS UNSIGNED) = 0, 1, cast(replace(machines , concat(SUBSTRING_INDEX(machines, ',', 1),','),'') as unsigned)))
when length(machines)-length(replace(machines,',','')) = 2
then ( if( CAST(SUBSTRING_INDEX(machines, ',', 1) AS UNSIGNED) = 0, 1, cast(SUBSTRING_INDEX(machines, ',', 1) as unsigned))) +
(if( CAST(replace(machines , concat(SUBSTRING_INDEX(machines, ',', 1),','),'') AS UNSIGNED) = 0, 1, cast(replace(machines , concat(SUBSTRING_INDEX(machines, ',', 1),','),'') as unsigned))) +
(if( CAST(replace(machines , concat(SUBSTRING_INDEX(machines, ',', 2),','),'') AS UNSIGNED) = 0, 1, cast(replace(machines , concat(SUBSTRING_INDEX(machines, ',', 2),','),'') as unsigned)))
end) AS ukupno
FROM izvestaji
#WHERE projekatid='8'
#AND datum='2019-10-03'
您可以获得从 2 到 M 的其他案例,包括这样的案例
when length(machines)-length(replace(machines,',','')) = M
then ( if( CAST(SUBSTRING_INDEX(machines, ',', 1) AS UNSIGNED) = 0, 1, cast(SUBSTRING_INDEX(machines, ',', 1) as unsigned))) +
(if( CAST(replace(machines , concat(SUBSTRING_INDEX(machines, ',', 1),','),'') AS UNSIGNED) = 0, 1, cast(replace(machines , concat(SUBSTRING_INDEX(machines, ',', 1),','),'') as unsigned))) +
(if( CAST(replace(machines , concat(SUBSTRING_INDEX(machines, ',', 2),','),'') AS UNSIGNED) = 0, 1, cast(replace(machines , concat(SUBSTRING_INDEX(machines, ',', 2),','),'') as unsigned))) +
...
(if( CAST(replace(machines , concat(SUBSTRING_INDEX(machines, ',', j),','),'') AS UNSIGNED) = 0, 1, cast(replace(machines , concat(SUBSTRING_INDEX(machines, ',', j),','),'') as unsigned))) +
...
(if( CAST(replace(machines , concat(SUBSTRING_INDEX(machines, ',', M),','),'') AS UNSIGNED) = 0, 1, cast(replace(machines , concat(SUBSTRING_INDEX(machines, ',', M),','),'') as unsigned)))
如果你看到案例1或案例2,我想你可以理解它。
您可以使用sign()
、abs()
、substr()
、instr()
和length()
函数的组合,并可以通过公式过滤整数值string + 0
:
select sum( int_value_1 + int_value_2 ) +
sum( case when int_value_1 = 0 or int_value_2 = 0 then
sign( length(before_comma) ) + abs( sign( after_comma+0 ) - 1 )
end) as total
from
(
select substr( Machines, 1, instr(Machines,',') - 1 ) + 0 as int_value_1,
substr( Machines, instr(Machines,',')+1, length(Machines) ) as int_value_2,
substr( Machines, 1, instr(Machines,',') - 1 ) as before_comma,
substr( Machines, instr(Machines,',')+1, length(Machines) ) as after_comma
from izvestaji
) i;
演示