id processDefinitionId
1 camelService187
2 camelService187_1
3 camelService187_2
4 camelService188
5 camelService188_1
6 camelService188_2
7 camelService189_3
9 camelService171
10 camelService171_1
11 camelService171_2
12 camelService171_3
13 camelService171_4分析上面的数据,camelService后面接着数字,然后这个数字还有下划线后面跟着的数据
我们把这几个数据当成一组,现在与 187,188,171 这三个组。
要求是 写一个sql ,如果每组中没出现下划线的数据拿就取出没有下划线的数据
,如果存在 就把每组带下划线后的最大的数的数据取出来
谢谢了!!!
1 camelService187
2 camelService187_1
3 camelService187_2
4 camelService188
5 camelService188_1
6 camelService188_2
7 camelService189_3
9 camelService171
10 camelService171_1
11 camelService171_2
12 camelService171_3
13 camelService171_4分析上面的数据,camelService后面接着数字,然后这个数字还有下划线后面跟着的数据
我们把这几个数据当成一组,现在与 187,188,171 这三个组。
要求是 写一个sql ,如果每组中没出现下划线的数据拿就取出没有下划线的数据
,如果存在 就把每组带下划线后的最大的数的数据取出来
谢谢了!!!
max(processDefinitionId) val
from table
group by regexp_substr(processDefinitionId,'[[:alnum:]]+')
CREATE TABLE `data` (
`id` int(11) NOT NULL,
`processDefinitionId` varchar(50) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;insert into data(id, processDefinitionId ) values
(1, 'camelService187'),
(2, 'camelService187_1'),
(3, 'camelService187_2'),
(4, 'camelService188'),
(5, 'camelService188_1'),
(6, 'camelService188_2'),
(7, 'camelService189_3'),
(9, 'camelService171'),
(10, 'camelService171_1'),
(11, 'camelService171_2'),
(12, 'camelService171_3'),
(13, 'camelService171_4'),
(14, 'camelService190'),
(15, 'camelService191')
;select replace(max(a.processDefinitionId), '_#', '') from
(
select id, case when instr(processDefinitionId,'_') <= 0 then concat(processDefinitionId, '_#') else processDefinitionId end as processDefinitionId from data
) as a
group by
substring(processDefinitionId, 1, case when instr(processDefinitionId, '_') > 0 then instr(processDefinitionId, '_') else char_length(processDefinitionId) end);
p.s.我觉得你的表设计不恰当
结果:+-----------------------------------------------+
| replace(max(a.processDefinitionId), '_#', '') |
+-----------------------------------------------+
| camelService171_4 |
| camelService187_2 |
| camelService188_2 |
| camelService189_3 |
| camelService190 |
| camelService191 |
+-----------------------------------------------+
6 rows in set (0.00 sec)