我试图将数据导出到CSV文件中,但是我的领先零被切碎了...我无法在数据拉动后修改数据,因此LPAD不是一个选择。此外,ID不是设定的长度...它可能长6个字符,也可能是11个。它也可能具有字母(在这种情况下,CSV将这些值显示为字符串。数据库中的领先零并存储为varchar。我需要提取这些值并用零以:
在CSV中显示它们:SELECT
'ID',
'Par 1',
'Par 2'
UNION
(SELECT
result.id as 'ID',
COALESCE(sum(if(result.parameter = '10001', result.result, NULL)),'-') as 'Par 1',
COALESCE(sum(if(result.parameter = '10002', result.result, NULL)),'-') as 'Par 2'
INTO OUTFILE '/tmp/file.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"'
FROM (
SELECT
event.id,
resultset.parameter_cd,
resultset.result
FROM event
Inner Join resultset ON resultset.id = event.id
) as result
group by result.id
order by
result.id ASC )
在这种情况下," ID"可能具有领先的零... MySQL设置的结果显示了它们,但是在导出的CSV中,它们被完全删除了????
作为解决方法,用双引号围绕ID,并将enclosed by
取出,因为数字字段不需要它们
(SELECT
concat ('"', result.id, '"') as 'ID',
COALESCE(sum(if(result.parameter = '10001', result.result, NULL)),'-') as 'Par 1',
COALESCE(sum(if(result.parameter = '10002', result.result, NULL)),'-') as 'Par 2'
INTO OUTFILE '/tmp/file.csv' FIELDS TERMINATED BY ','