Add two sql statements executed with shell and output the database name and the added value separated by the tab key
SELECT SUM (PresentSum) FROM `Lg_ consumeDetail0` WHERE ConsumeDate < '2016-11-01' AND OpId! = 300
+
SELECT SUM (PresentSum) FROM `Lg_ consumeDetail1` WHERE ConsumeDate < '2016-11-01' AND OpId! = 300
Each time the sum of two sql queries above and below the + sign is returned.
Note that it is possible that one of the sentences sql returns NULL or error messages (there is no such table).
The grid test is as follows:
Output result
The sum of the database name 1 sql1+sql2
The sum of the database name 2 sql1+sql2
The sum of the database name 3 sql1+sql2
......
The middle should be separated by tab.
The code is as follows:
#! / bin/bashUSER='root'PASSWD='123456'IP='192.168.1.1'for i in tzpay tzpay360 tzpay_179 tzpay_9377 tzpay_dx2 tzpay_dx3 tzpay_dx4 tzpay_dx5 tzpay_dx6 tzpay_lt1 tzpay_lt2 tzpay_lt3 tzpay_lt4 tzpay_yydo table1=$ (mysql-u$USER-p$PASSWD-h$IP $I-e "SELECT SUM (BasicSum) FROM Lg_ConsumeDetail0 WHERE ConsumeDate < '2016-11-01' AND OpId = 300 | | tail-1 | awk'{print $1}') table4=$ (mysql-u$USER-p$PASSWD-h$IP $I-e show tables like "Lg_ConsumeDetail0"'| wc-l) # determine whether table 0 exists. If it does not exist, print the value of table 1. Skip this cycle if [$table4-eq 0]; then mysql-u$USER-p$PASSWD-h$IP $I-e "SELECT SUM (BasicSum) FROM Lg_ConsumeDetail1 WHERE ConsumeDate < '2016-11-01' AND OpId = 300 | | tail-1 | awk'{print b "\ t" $1} 'continue fi table2=$ (mysql-u$USER-p$PASSWD-h$IP $I-e' show tables like "Lg_ConsumeDetail1"'| wc-l) # determine whether table 1 exists. If it does not exist, print the value of table 0. Skip this cycle if [$table2-eq 0]; then mysql-u$USER-p$PASSWD-h$IP $I-e "SELECT SUM (BasicSum) FROM Lg_ConsumeDetail0 WHERE ConsumeDate < '2016-11-01' AND OpId = 300 | | tail-1 | awk-vb=$i'{print b "\ t" $1} 'continue fi table3=$ (mysql-u$USER-p$PASSWD-h$IP $I-e "SELECT SUM (BasicSum) FROM Lg_ConsumeDetail1 WHERE ConsumeDate <' 2016-11-01 'AND OpId = 300 "| tail-1 | awk'{print $1}') # if both Table 1 and Table 0 are NULL, then output NULL directly and skip this cycle if [$table3 = =" NULL "] & & [$table1 = =" NULL "] Then echo NULL | awk-vb=$i'{print b "\ t" $1} 'continue fi # determines that Table 1 is NULL, then directly outputs the value of Table 0 and skips this cycle if [$table3 = = "NULL"]; then mysql-u$USER-p$PASSWD-h$IP $I-e "SELECT SUM (BasicSum) FROM Lg_ConsumeDetail0 WHERE ConsumeDate <' 2016-11-01 'AND OpId = 300 | | tail-1 | awk-vb=$i'{print b "\ t" $1} 'continue fi # determines that Table 0 is NULL, then output the value of Table 1 directly and skip this cycle if [$table1 = = "NULL"]; then mysql-u$USER-p$PASSWD-h$IP $I-e "SELECT SUM (BasicSum) FROM Lg_ConsumeDetail1 WHERE ConsumeDate <' 2016-11-01 'AND OpId = 300 | | tail-1 | awk'{print b "\ t" $1} 'continue fi #, add the values of Table 0 and Table 1 to output expr $table1 + $table3 | awk-vb=$i' {print b "\ t" $1} 'done
The implementation results are as follows:
[root@10-9-181186opt] # sh sql_sum.shtzpay 69768460tzpay360 23185990tzpay_179 1259110tzpay_9377 57750tzpay_dx2 32938190tzpay_dx3 33270350tzpay_dx4 12540690tzpay_dx5 13220880tzpay_dx6 NULLtzpay_lt1 33962100tzpay_lt2 23586690tzpay_lt3 10345880tzpay_lt4 NULLtzpay_yy NULL
