英文:
Get count and result from SQL query in Go
问题
我正在使用database/sql和lib/pq（postgres）包运行一个非常简单的查询，并且我想将一些字段的结果放入一个切片中，但我需要知道切片的大小。
我能找到的唯一解决方案是执行另一个查询，即SELECT COUNT(*) FROM tableName;。
有没有一种方法可以在一个查询中同时获取查询结果和返回行的计数？
英文:
I'm running a pretty straightforward query using the database/sql and lib/pq (postgres) packages and I want to toss the results of some of the fields into a slice, but I need to know how big to make the slice.
The only solution I can find is to do another query that is just SELECT COUNT(*) FROM tableName;.
Is there a way to both get the result of the query AND the count of returned rows in one query?
答案1
得分: 3
概念上，问题在于数据库游标可能没有枚举到最后，因此在实际读取所有记录之前，数据库并不知道你将获得多少条记录。在一般情况下，唯一的计数方法是遍历结果集中的所有记录。
但在实际操作中，你可以通过使用子查询来强制执行这样的操作：
select *, (select count(*) from table) from table
对于除第一条记录以外的记录，只需忽略第二列即可。但这样做非常粗鲁，我不建议这样做。
英文:
Conceptually, the problem is that the database cursor may not be enumerated to the end so the database does not really know how many records you will get before you actually read all of them. The only way to count (in general case) is to go through all the records in the resultset.
But practically, you can enforce it to do so by using subqueries like
select *, (select count(*) from table) from table
and just ignore the second column for records other than first. But it is very rude and I do not recommend doing so.
答案2
得分: 0
不确定这是否是您要求的，但您可以调用@@Rowcount函数来返回已执行的前一个select语句的计数。
SELECT mytable.mycol FROM mytable WHERE mytable.foo = 'bar'
SELECT @@Rowcount
如果您希望在结果集中包含行计数，可以使用OVER子句（MSDN）。
SELECT mytable.mycol, count(*) OVER(PARTITION BY mytable.foo) AS 'Count' FROM mytable WHERE mytable.foo = 'bar';
您还可以使用分号将两个SQL语句分开。这将返回执行的两个语句的结果集。
英文:
Not sure if this is what you are asking for but you can call the @@Rowcount function to return the count of the previous select statement that has been executed.
SELECT mytable.mycol FROM mytable WHERE mytable.foo = 'bar'
SELECT @@Rowcount
If you want the row count included in your result set you can use the the OVER clause (MSDN)
SELECT mytable.mycol, count(*) OVER(PARTITION BY mytable.foo) AS 'Count' FROM mytable WHERE mytable.foo = 'bar'
You could also perhaps just separate two SQL statements with the a ; . This would return a result set of both statements executed.
答案3
得分: -2
你会使用count(*)函数
SELECT count(distinct last)
FROM (XYZTable)
WHERE date(FROM_UNIXTIME(time)) >= '2013-10-28' AND
id = 90;
英文:
You would used count(*)
SELECT count(distinct last)
FROM (XYZTable)
WHERE date(FROM_UNIXTIME(time)) >= '2013-10-28' AND
id = 90 ;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论