在单个工作表中具有Google Script和JDBC连接器的多个SQL查询 [英] Multiple SQL Queries with Google Script and JDBC connectors in a single Sheet

问题描述

我正在使用带JDBC连接器的应用程序脚本来与我的MySQL统计数据库建立连接. 我希望能够在一个脚本中运行多个查询，以将数据库中的特定信息提取到一个表中 我所有的查询结果都具有相同的结构(均按日期分组)YEAR，MONTH，DAY，VALUE，我想知道是否可以固定YEAR，MONTH，DATE列，而所有其他结果都填充在以下的列中我的结果.

I'm using an App Script with JDBC connector to create a connection with my MySQL stats database. I would like to be able to run multiple Queries in a single script to pull out specific info from my database into a single sheet All my Queries' results have the same structure (all are group by date) YEAR,MONTH,DAY,VALUE and I was wondering if it was possible to have the YEAR,MONTH, DATE Columns fixed and all the other results populating following columns in my results.

类似的东西 列A:YEAR，列B:MONTH，列C:DAY，列D:查询1的结果，列E:查询2的结果....

Something like Col A: YEAR, Col B: MONTH, Col C: DAY, Col D: Result from Query 1, Col E: Result from Query 2....

这就是我的开始

function loadData() {
var sheet = SpreadsheetApp.getActiveSheet();
var instanceUrl = 'jdbc:mysql://' + address;
var dbUrl = instanceUrl + '/' + db;
var conn = Jdbc.getConnection(dbUrl, user, userPwd);

var stmt = conn.createStatement();
stmt.setMaxRows(10);
var results = stmt.executeQuery('Select YEAR(dateCreated) as yearAdded,MONTH(dateCreated) as monthAdded, DAY(dateCreated) as dayAdded,sum(amount)/100 from stripeTransactions GROUP BY yearAdded ,monthAdded,dayAdded;');
var numCols = results.getMetaData().getColumnCount(); 
while (results.next()) {
  var rowArray = new Array();
   for (var col = 0; col < numCols; col++) {
    rowArray.push(results.getString(col+1));
  }
  sheet.appendRow(rowArray);
}

results.close();
stmt.close();
var stmt2 = conn.createStatement();
stmt2.setMaxRows(10);
var results2= stmt2.executeQuery('Select YEAR(dateAdded) as yearAdded,MONTH(dateAdded) as monthAdded, DAY(dateAdded) as dayAdded,sum(amount) from itunestransactions GROUP BY yearAdded ,monthAdded,dayAdded;');
var numCols2 = results2.getMetaData().getColumnCount();
 while (results2.next()) {
  var rowArray2 = new Array();
   for (var cal = numCols; cal < numCols2; cal++) {
    rowArray2.push(results2.getString(cal+1));
  }
 sheet.appendRow(rowArray2);
}

results2.close();
stmt2.close();
}

谢谢 (此脚本只为每个结果附加行，我想每个查询的每个结果可能只有一个工作表，然后将每个工作表合并为一个工作表，但我宁愿从一开始就在一个工作表中进行操作)

Thanks (this script only append rows for every results, I guess could have a single sheet per results for each query then merge every sheet in one but I'd rather do it in a single sheet from the beginning)

推荐答案

您需要了解更高级的MySQL才能实现此目的，但是您可以将子查询嵌套到主查询中.

You'll need to know more advanced MySQL to pull this off but you can nest sub-queries into your main query.

此外，如果您希望查询效率更高，可以将查询包装到MySQL存储过程中，然后通过JDBC从Apps脚本中调用该过程.

Moreover, if you want your queries to be more efficient you can wrap your queries into a MySQL stored procedure and then call that procedure from Apps Script via JDBC.

以下是一些教程的链接，可帮助您入门:

Here are links to some tutorials to get you started:

MySQL子查询

MySQL存储过程

这篇关于在单个工作表中具有Google Script和JDBC连接器的多个SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！