sql - 我可以使用 SQL 语句从工作表 A + 工作表 B 中提取数据并仅使用 Excel 转储到工作表 C 中吗

Question

我在 Excel 中有这样的数据

table Cost_per_period
---------------------
ProjectId
FaseID
Period
Percentage

table cost_per_partner_per_fase
-------------------------------
ProjectID
FaseID
PartnerID
Amount

table partners
--------------
PartnerID
name

这是我想要的输出。

                 2012             2013                2014     2015
Project  fase    jan  feb ... dec  Q1    Q2   Q3   Q4  wholeY  wholeY
------------------------------------------------------------------------
A310     1       100k 20k     10k  100k   -   10k  10k 1000k   2000k
A310     2       110k   -     20k   99k   -   40k  50k 5000k   3000k
......

为了合并这些数据，我正在考虑执行如下 SQL 语句

SELECT cp.projectID, cp.faseID
  , case when cp.period between '2012/01/01' and '2012/01/31' 
         THEN sum(cpf.amount)*cp.percentage as jan2012 end
  , case when ..... as feb2012 end
  , case .......
FROM cost_per_period as cp
INNER JOIN cost_per_partner_per_fase as cpf 
  on (cp.postjectid = cpf.projectid) and (cp.faseid = cpf.faseid)
GROUP BY cp.Projectid, cp.faseid
ORDER BY cp.ProjectID, cp.FaseID

我可以仅使用 Excel 来执行此操作吗？我使用的是 Excel 2007

Answer 1

以下是最简单的 Excel 查询，可以帮助您入门:

Sub test()
  Dim rs As Object
  Set rs = CreateObject("ADOR.Recordset")
  rs.Open _
      "SELECT CompanyName FROM Customers;", _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\Tempo\db.xls;" & _
        "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
    Sheet3.Range("A1").CopyFromRecordset rs
  End Sub