使用SQL查询中的多个别名作为Excel中列的标题 [英] Use multiple alias from SQL query as header of columns in Excel

问题描述

参考这个问题，我将以下查询运行到

With reference to this question I run the below query to

a)在 A列和
中获得广告系列b)在 SQL 中将别名作为 Column A 的 header .

a) get the campaigns in Column A and
b) include the alias in the SQL as header for the Column A.

Sub ConnectDB5()
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim dateVar As Date

    Set conn = New ADODB.Connection
    conn.ConnectionString = "DRIVER={MySQL ODBC 5.1 Driver}; SERVER=localhost; DATABASE=bi; UID=username; PWD=password; OPTION=3"
    conn.Open

    strSQL = " SELECT 'campaign'  UNION ALL SELECT " & _
            " cID AS Campaign " & _
            " FROM PDW_DIM_Offers_Logistics_history " & _
            " WHERE DATE(insert_timestamp) = ""2020-02-24"" "

    Set rs = New ADODB.Recordset
    rs.Open strSQL, conn, adOpenStatic

    Sheet4.Range("A1").CopyFromRecordset rs

    rs.Close
    conn.Close

End Sub

此查询效果很好.

现在，我在SQL查询中添加了另外一列:

Now, I added one additional column in the SQL query:

Sub ConnectDB5()
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim dateVar As Date

    Set conn = New ADODB.Connection
    conn.ConnectionString = "DRIVER={MySQL ODBC 5.1 Driver}; SERVER=localhost; DATABASE=bi; UID=username; PWD=password; OPTION=3"
    conn.Open

    strSQL = " SELECT 'campaign'  UNION ALL SELECT " & _
            " cID AS Campaign, " & _
            " SUM(order_quantity) AS Quantity" & _
            " FROM PDW_DIM_Offers_Logistics_history " & _
            " WHERE DATE(insert_timestamp) = ""2020-02-24"" " & _
            " GROUP BY 1"


    Set rs = New ADODB.Recordset
    rs.Open strSQL, conn, adOpenStatic

    Sheet4.Range("A1").CopyFromRecordset rs

    rs.Close
    conn.Close

End Sub

有了这个我得到:

运行时错误'-2147217887(80040e21)'

runtime error '-2147217887 (80040e21)'

我需要将VBA更改为什么

What do I need to change in my VBA to

a)在 A列中获取广告系列，并在 B列中
获取数量.b)将 SQL 中的别名作为 Header 包含在 A列和 B列中?

a) get the campaigns in Column A and the Quantity in Column B
b) include the alias in the SQL as header for the Column A and Column B?

推荐答案

我认为您需要修复标题行:

I think you need to fix the heading row:

strSQL = " SELECT 'campaign', 'Quantity'  UNION ALL SELECT " & _

UNION要求所有数据集具有相同的列数.

UNION requires all dataset to have the same number of columns.

这篇关于使用SQL查询中的多个别名作为Excel中列的标题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！