将Excel表达式添加到SQL查询结果 [英] Add Excel Expressions to a SQL Query Result

问题描述

我有一个要求，我需要执行一个SQL查询，将被输出到.csv文件。这将在Excel中打开。然后可以在电子表格中操作数据，并在计算列中自动更新数据（感谢Excel表达式）

以下是一个示例：

 列A | B栏列C 
 1 | 2 | A + B 
 4 | 10 | A + B 
 | 12 | A + B 
 0 | 1 | A + B 
  

列C是简单的列A +列B，但是我不能简单地写在查询，因为电子表格的目的是允许某人在Excel中编辑列A和B，然后列C将自动更新。

我已经考虑在查询中包含行号，并将列C写为= A {N} + B {N}，其中N是行索引。

解决方案

听起来你已经有一个计划：

  SELECT [COLUMN A]，[COLUMN B]，'A'+ CAST（ROW_NUMBER（）OVER（ORDER BY（SELECT 1））AS VARCHAR） +'+ B'+ CAST（ROW_NUMBER（）OVER（ORDER BY（SELECT 1））AS VARCHAR）AS [COLUMN C] 
 FROM [TABLE1] 
  

I have a requirement where I need to execute a SQL Query which will be outputted to a .csv file. This will will then be opened in Excel. The data can then be manipulated in the spreadsheet and values automatically updated in computed columns (thanks to Excel expressions)

Here's an example:

Column A | Column B | Column C
1        | 2        | A+B
4        | 10       | A+B
         | 12       | A+B
0        | 1        | A+B

Column C is simple Column A + Column B, however I cannot simply write this in the query, as the purpose of the spreadsheet is to allow someone to edit columns A and B in Excel and then Column C will automatically be updated.

I've thought about including the Row Number in the query and writing Column C as "=A{N}+B{N}" where N is the row index.

Is it possible to achieve something like this?

解决方案

Sounds like you already have a plan:

SELECT [COLUMN A], [COLUMN B], 'A' + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS VARCHAR) + '+B' + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS VARCHAR) AS [COLUMN C]
FROM [TABLE1]

这篇关于将Excel表达式添加到SQL查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！