mysql - 如何在where语句mysql中找到不同的通配符

Question

 CREATE TABLE table1
        (`id` int not null auto_increment primary key, `data` char(3),`subject` varchar(250)) ;

INSERT INTO table1
    (`id`, `data`,`subject`)
VALUES
    (1, 'xa','aad'),
    (2, 'ya','aac'),
    (3, 'yc','aae'),
    (4, 'zb','aaf'),
    (5, 'yb','aag'),
    (6, 'xc','aah'),
    (7, 'za','aai');

如表所示如何找到所有以数据列中第一个字母开头的不同值

例如:

select * from table1 where data like '%x%' limit 1;
select * from table1 where data like '%y%' limit 1;
select * from table1 where data like '%z%' limit 1;

进入一个查询并得到如下所示的输出

输出

1, 'xa', 'aad'
2, 'ya','aac'
3, 'zb','aaf'

sql fiddle

Answer 1

在一次查询中完成

select 
  id,
  data,
  LEFT(group_concat(subject order by id),LOCATE(',',group_concat(subject order by id)) - 1),
from table1
group by SUBSTRING(data, 1, 1)