mysql - 将 mysql 查询输出到 shell 脚本

Question

大家好，我有以下 mysql 查询，我需要将其输出到表中，以便通过电子邮件发送给客户!我只需要将结果格式化为表格即可。

 select division_name,
     count(*) "Total Cases on CTS",
     sum(if(Date(Filing_Date) = DATE_SUB(curdate(), INTERVAL 1 DAY), 1,0)) "entered yesterday",
     sum(if(DATE(Filing_date) = curdate(), 1, 0)) 'entered today',
     sum(if(YEAR(filing_date) = YEAR(curdate()) AND Month(filing_date) = Month(curdate()), 1,0)) "entered this month",
     sum(if(YEAR(filing_date) = YEAR(DATE_SUB(curdate(), INTERVAL 1 MONTH)) AND Month(filing_date) = Month(DATE_SUB(curdate(), INTERVAL 1 MONTH)), 1,0)) as entered_last_month
     from cases2v
     where court_rank_name like 'High Court'
          OR court_rank_name like 'Magistrate Court'
     group by division_name";

我尝试将查询输出到csv文件，然后在linux(Centos 7)中使用ssconvert转换为excel并使用postfix发送电子邮件，输出文件看起来不太好..它缺少表格标题。

我正在考虑使用 shell 脚本将查询输出到数组，然后循环结果以创建表会更好更快，但我不知道如何!这是我尝试过的

dbquery=$(mysql -u user -ppass -e "use db_name; select division_name, count(*) "Total Cases on CTS", sum(if(Date(Filing_Date) = DATE_SUB(curdate(), INTERVAL 1 DAY), 1,0)) "entered yesterday", sum(if(DATE(Filing_date) = curdate(), 1, 0)) 'entered today', sum(if(YEAR(filing_date) = YEAR(curdate()) AND Month(filing_date) = Month(curdate()), 1,0))  "entered this month", sum(if(YEAR(filing_date) = YEAR(DATE_SUB(curdate(), INTERVAL 1 MONTH)) AND Month(filing_date) = Month(DATE_SUB(curdate(), INTERVAL 1 MONTH)), 1,0)) as entered_last_month from cases2v  where court_rank_name like 'High Court' OR court_rank_name like 'Magistrate Court' group by division_name;")

array=($(for i in $dbquery; do echo $i; done )) 

Answer 1

您可以尝试使用联合将列标题添加到结果中。另外，请注意您的报价。我对列和表使用反引号，对字符串使用单引号，只是为了清晰并避免混淆。有几个地方双引号内有双引号

SELECT 
    'division_name' AS `division_name`,
    'Total Cases on CTS' AS `Total Cases on CTS`,
    'entered yesterday' as `entered yesterday`,
    'entered today' AS `entered today`,
    'entered this month' AS `entered this month`,
    'entered_last_month' AS `entered_last_month`
UNION
SELECT 
    `division_name`,
    count(*) AS `Total Cases on CTS`,
    sum(if(Date(Filing_Date) = DATE_SUB(CURDATE(), INTERVAL 1 DAY), 1,0))  AS `entered yesterday`,
    sum(if(DATE(Filing_date) = CURDATE(), 1, 0)) AS `entered today`,
    sum(if(YEAR(filing_date) = YEAR(CURDATE()) AND Month(filing_date) = Month(CURDATE()), 1,0)) AS `entered this month`,
    sum(if(YEAR(filing_date) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) AND Month(filing_date) = Month(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), 1,0)) as `entered_last_month`
FROM cases2v
WHERE court_rank_name LIKE 'High Court'
    OR court_rank_name LIKE 'Magistrate Court'
GROUP BY division_name";