[Изменить] Полное решение, если даты указаны в формате mm / dd / yyyy, а разница должно быть в днях:
<script type="text/javascript">
function daysFromString(dateString)
{
// split strings at / and return array
var splittedString = dateString.split("/");
// make a new date. Caveat: Months are 0-based in JS
var newDate = new Date(parseInt(splittedString[2], 10), parseInt(splittedString[0], 10)-1, parseInt(splittedString[1], 10));
// returns days since jan 1 1970
return Math.round(newDate.getTime() / (24*3600*1000));
}
var dateString2 = "02/09/2009";
var dateString1= "03/12/2009";
var dateDays1 = daysFromString(dateString1);
var dateDays2 = daysFromString(dateString2);
var diff = dateDays1 - dateDays2;
alert (diff);
</script>
Если версией вашей БД является 8.0 + , то предложение with recursive cte as
может использоваться как в следующем операторе select (после того, как были предоставлены необходимые DML, такие как create table и insert ):
mysql> create table tab( ID int, suggestions varchar(25));
mysql> insert into tab values(1,'A,B,C');
mysql> insert into tab values(2,'D,E,F,G,H');
mysql> select q2.*,
row_number()
over
(partition by q2.id order by q2.suggestion) as number
from
(
select distinct
id,
substring_index(
substring_index(suggestions, ',', q1.nr),
',',
-1
) as suggestion
from tab
cross join
(with recursive cte as
(
select 1 as nr
union all
select 1+nr from cte where nr<10
)
select * from cte) q1
) q2;
+------+------------+--------+
| id | suggestion | number |
+------+------------+--------+
| 1 | A | 1 |
| 1 | B | 2 |
| 1 | C | 3 |
| 2 | D | 1 |
| 2 | E | 2 |
| 2 | F | 3 |
| 2 | G | 4 |
| 2 | H | 5 |
+------+------------+--------+
Я бы предложил серию подзапросов:
select id, substring_index(suggestions, ',', 1) as suggestion, 1
from example
where suggestions is not null
union all
select id, substring_index(substring_index(suggestions, ',', 2), ',', -1) as suggestion, 2
from example
where suggestions like '%,%'
union all
select id, substring_index(substring_index(suggestions, ',', 3), ',', -1) as suggestion, 3
from example
where suggestions like '%,%,%'
union all
select id, substring_index(substring_index(suggestions, ',', 4), ',', -1) as suggestion, 4
from example
where suggestions like '%,%,%,%'
union all
select id, substring_index(substring_index(suggestions, ',', 5), ',', -1) as suggestion, 5
from example
where suggestions like '%,%,%,%,%';
Это можно легко расширить, если у вас более 5 опций на один идентификатор.
Найти здесь . Решена такая же проблема.
https://gist.github.com/avoidwork/3749973