1,行内多字段拼接
CONCAT( date_format( sd.create_time, '%Y-%m- %d' ), d.NAME, '了', sd.amount DIV 60, 'h' ) demo
date_frommat 格式化时间
返回结果为连接参数产生的字符串。如有任何一个参数为NULL ,则返回值为 NULL。
若是不像返回null可以使用contcat_ws(separator,str1,str2,...)
contcat_ws() 代表 CONCAT With Separator ,是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串,也可以是其它参数。
注意:
如果分隔符为 NULL,则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。
2,分组拼接多行
GROUP_CONCAT( scc.demo SEPARATOR ' ' '\n' ) demo
语法:group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )
说明:通过使用distinct可以排除重复值;如果希望对结果中的值进行排序,可以使用order by子句;separator是一个字符串值,缺省为一个逗号。
例子
SELECT DISTINCT
base.id,
base.CODE,
base.`name`,
base.main_org_id org_id,
org.layer_id,
org.`name` org_name,
s.*
FROM
hr_emp_base_info base
INNER JOIN hr_org org ON org.id = base.main_org_id
AND org.is_deleted = 0
LEFT JOIN (
SELECT
st.user_id,
st.years,
st.amount_total DIV 60,
st.amount_balance DIV 60,
st.type,
wf.`name` type_name,
sdd.kucun,
sss.kucun detail
FROM
stock_total st
LEFT JOIN wf_type wf ON wf.`code` = st.type
AND wf.is_deleted = 0
LEFT JOIN (
SELECT
v.type,
v.user_id,
v.years,
GROUP_CONCAT( v.kucun SEPARATOR ' ' '\n' ) kucun
FROM
(
SELECT
sd.years,
sd.type,
sd.user_id,
CONCAT(
date_format( sd.create_time, '%Y-%m-%d' ),
'发放了库存',
sd.amount DIV 60,
'h,有效期: ',
date_format( sd.valid_begin, '%Y-%m-%d' ),
'~',
date_format( sd.valid_end, '%Y-%m-%d' )
) kucun
FROM
stock_data sd
WHERE
sd.is_deleted = 0
AND sd.`status` = 1
AND sd.bus_type = '10'
GROUP BY
sd.user_id,
sd.type,
sd.years,
kucun
ORDER BY
sd.user_id
) v
GROUP BY
v.user_id,
v.type,
v.years
) sdd ON sdd.user_id = st.user_id
AND sdd.type = st.type
AND sdd.years = st.years
LEFT JOIN (
SELECT
scc.type,
scc.user_id,
scc.years,
GROUP_CONCAT( scc.kucun SEPARATOR ' ' '\n' ) kucun
FROM
(
SELECT
sd.years,
sd.type,
sd.user_id,
CONCAT( date_format( sd.create_time, '%Y-%m-%d' ), d.NAME, '了', sd.amount DIV 60, 'h' ) kucun
FROM
stock_data sd
LEFT JOIN sys_dict_item d ON d.dict_code = 'LEAVE_OPERATION_TYPE'
AND d.`code` = sd.bus_type
AND d.is_deleted = 0
WHERE
sd.is_deleted = 0
AND sd.`status` = 1
AND sd.bus_type != '10'
GROUP BY
sd.user_id,
sd.type,
sd.years,
kucun
ORDER BY
sd.user_id
) scc
GROUP BY
scc.user_id,
scc.type,
scc.years
) sss ON sss.user_id = st.user_id
AND sss.type = st.type
AND sss.years = st.years
WHERE
st.is_deleted = 0
) s ON s.user_id = base.id
WHERE
base.is_deleted = 0
AND base.voidpf = 0
AND base.STATUS IN (
'10',
'20',
'40',
'30'
)