1.首先利用条件筛选出基本数据
SELECT * from test where columna = 'xxx' AND columnb = 'xxx' and ......
2.对基本数据分组并利用SUBSTRING_INDEX和GROUP_CONCAT函数取出需要的字段
SELECT tab1.columnc, tab1.columnd, tab1.columne,SUBSTRING_INDEX(GROUP_CONCAT(distinct tab1.[需要合并的字段] ORDER BY tab1.[需要排序的字段] DESC), ',', '1') month from (
SELECT * from test where columna = 'xxx' AND columnb = 'xxx') tab1 GROUP BY tab1.columne
说明: group_concat函数用来分组合并字段,比如说按照ID分组,将name合并,具体用法问度娘吧。。。。
substring_index()函数可以取到某个字符串中按照给定字符分割后的第N个字符,相当于现将字符串按照某个字符分割成数据,然后去到数组的第N个元素
3.然后通过连接取到其他字段数据
SELECT tab2.* from test tab2 RIGHT JOIN (
SELECT tab1.columnc, tab1.columnd, tab1.columne,SUBSTRING_INDEX(GROUP_CONCAT(distinct tab1.[需要合并的字段] ORDER BY tab1.[需要排序的字段] DESC), ',', '1') month from (
SELECT * from test where columna = 'xxx' AND columnb = 'xxx' tab1 GROUP BY tab1.columne) tab3 on tab2.columnc = tab3.columnc and tab2.columnd = tab3.columne and tab2.columnf = tab3.columnf;
4.大功告成。综合之后的SQL:
SELECT tab2.* from test tab2 RIGHT JOIN (
SELECT tab1.a, tab1.b, tab1.c,SUBSTRING_INDEX(GROUP_CONCAT(distinct tab1.d ORDER BY tab1.create_date DESC), ',', '1') d from (
SELECT * from test where a= 'XXXX'
AND b= 'xxxx') tab1 GROUP BY tab1.c) tab3 on tab2.a= tab3.a and tab2.b= tab3.b and tab2.c= tab3.c and tab2.d = tab3.d;