如何“排序”列和“包含列名与查询”？

I am trying to run a sql query in excel and I want to :

我试图在Excel中运行SQL查询,我想:

1. order the query result by my column "Stationname"
2. include the column names with the query

1.通过我的列“Stationname”对查询结果进行排序2.在查询中包含列名

Right now it is returning all the columns without the column name, and the end users do not know what it is.

现在它返回没有列名的所有列,最终用户不知道它是什么。

Could someone please help? I am stuck! Below is my current code:

有人可以帮忙吗?我卡住了!以下是我目前的代码:

    strQuery = "select pipelineflow.lciid lciid, ldate, volume, capacity, status, " & _
        "pipeline, station, stationname, drn, state, county, owneroperator, companycode, " & _
        "pointcode, pointtypeind, flowdirection, pointname, facilitytype, pointlocator, " & _
        "pidgridcode from pipelineflow, pipelineproperties " & _
        "where pipelineflow.lciid = pipelineproperties.lciid " & _
        "and pipelineflow.audit_active = 1 " & _
        "and pipelineproperties.audit_active =1 " &
_
        "and pipelineflow.ldate " & dtInDate & _
        "and pipelineproperties.stationname = '" & Stationname & "' "

3 个解决方案

#1

For part 1 of your question, add an ORDER BY clause to your query. In this case: order by stationname

对于问题的第1部分,请在查询中添加ORDER BY子句。在这种情况下:按站名排序

Part 2: Not sure why column names aren't being included in your query. You can explicitly name a column using something like the following (purely an example):

第2部分:不确定为什么列名称不包含在您的查询中。您可以使用以下内容显式命名列(纯粹是一个示例):

select mycolumn as "MyCustomizedColumnName" from mytable

That allows you to give columns names of your choosing. Having said that, you shouldn't be required to do so for every column, so I suspect something else is going on in your case.

这允许您给出您选择的列名称。话虽如此,每列都不应该要求你这样做,所以我怀疑你的情况还有其他事情发生。

I should probably add that a stored procedure (rather than dynamic SQL) will yield better runtime performance.

我应该补充一点,存储过程(而不是动态SQL)将产生更好的运行时性能。

#2

For ordering just put

对于订购刚刚放

Order By stationname

at the end of the Query.

在查询结束时。

You can iterate through the column names by using:

您可以使用以下方法遍历列名称:

rst(1).Name

where rst is your recordset, and the number is the index of the column.

其中rst是您的记录集,而数字是列的索引。

#3

To sort your query results , use 'ORDER BY' at the end of the query. The last lines of your query would look like this

要对查询结果进行排序,请在查询结尾处使用“ORDER BY”。查询的最后几行如下所示

"and pipelineproperties.stationname = '" & Stationname & "' " & _
"ORDER BY pipelineproperties.stationname"

The column heading are returned in your query data, but not automatically written to the Excel worksheet. The code snippet below shows how to loop through the recordset's column headings and write them to the active cell's row.

列标题在查询数据中返回,但不会自动写入Excel工作表。下面的代码片段显示了如何遍历记录集的列标题并将它们写入活动单元格的行。

'rst' refers to your recordset, update the name as required.

'rst'指的是您的记录集,根据需要更新名称。

If Not rst.EOF Then
    For x = 0 To rst.Fields.Count - 1
     With ActiveCell.Offset(0, lcount)
        .Value = rst.Fields(x).Name
     End With
    Next
End If

Make sure that you offset down from the active cell when writing the query results to the worksheet, otherwise your headings will be overwritten by the data.

将查询结果写入工作表时,请确保从活动单元格向下偏移,否则您的标题将被数据覆盖。

Activecell.Offset(1,0).CopyFromRecordset rst

#1