将mysql查询输出到shell脚本

wb1gzix0 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(463)

嗨，伙计们，我有下面的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文件，然后使用 ssconvert 在linux（centos 7）中，要转换为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 ))

mysql shell bash

来源：https://stackoverflow.com/questions/50720352/output-mysql-query-to-a-shell-script

1条答案

按热度按时间

o8x7eapl1#

您可以尝试使用联合将列标题添加到结果中。另外，要注意你的语录。我对列和表使用反勾号，对字符串使用单引号，只是为了清楚和避免混淆。你有几个地方在双引号里有双引号

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";

赞(0）回复(0）举报 2021-06-20

我来回答

将mysql查询输出到shell脚本

1条答案

相关问题

热门标签

最新问答