如何在 sql 查询中插入计数列

How to insert a count column into a sql query

提问by chachi

I need the second column of the table retrieved from a query to have a count of the number of rows, so row one would have a 1, row 2 would have a 2 and so on. I am not very proficient with sql so I am sorry if this is a simple task.

我需要从查询中检索到的表的第二列来计算行数，所以第一行会有一个 1，第二行会有一个 2，依此类推。我对 sql 不是很精通，所以如果这是一项简单的任务，我很抱歉。

A basic example of what I am doing would be is:

我正在做的一个基本例子是：

SELECT [Name], [I_NEED_ROW_COUNT_HERE],[Age],[Gender]
FROM [customer]

The row count must be the second column and will act as an ID for each row. It must be the second row as the text file it is generating will be sent to the state and they require a specific format.

行数必须是第二列，并将作为每一行的 ID。它必须是第二行，因为它生成的文本文件将被发送到状态，并且它们需要特定的格式。

Thanks for any help.

谢谢你的帮助。

回答by Palu Macil

With your edit, I see that you want a row ID(normally called row numberrather than "count") which is best gathered from a unique ID in the database (person_id or some other unique field). If that isn't possible, you can make one for this report with ROW_NUMBER() OVER (ORDER BY EMPLOYEE_ID DESC) AS ID,in your select statement.

通过您的编辑，我看到您需要一个行ID（通常称为行号而不是“计数”），它最好从数据库中的唯一 ID（person_id 或其他一些唯一字段）中收集。如果这是不可能的，你可以ROW_NUMBER() OVER (ORDER BY EMPLOYEE_ID DESC) AS ID,在你的选择语句中为这个报告制作一个。

select Name, ROW_NUMBER() OVER (ORDER BY Name DESC) AS ID,
Age, Gender
from customer

This function adds a field to the output called ID (see my tips at the bottom to describe aliases). Since this isn't in the database, it needs a method to determine how it will increment. After the over keyword it orders by Name in descending order.

此函数向输出添加一个名为 ID 的字段（请参阅我在底部的提示以描述别名）。由于这不在数据库中，因此它需要一种方法来确定它将如何递增。在 over 关键字之后，它按名称降序排列。

---

---

Information on Countingfollows (won't be unique by row):

计数信息如下（按行不唯一）：

If each customer has multiple entries but the selected fields are the same for that user and you are counting that user's records (summed in one result record for the user) then you would write:

如果每个客户有多个条目，但该用户的所选字段相同，并且您正在计算该用户的记录（汇总为该用户的一个结果记录），那么您将编写：

select Name, count(*), Age, Gender
from customer
group by name, age, gender

This will count (see MSDN) all the user's records as grouped by the name, age and gender (if they match, it's a single record).

这将按姓名、年龄和性别分组（如果匹配，则为单个记录）计算（请参阅 MSDN）所有用户的记录。

However, if you are counting all records so that your whole report has the grand total on every line, then you want:

但是，如果您正在计算所有记录，以便整个报告的每一行都有总计，那么您需要：

select Name, (select count(*) from customer) as "count", Age, Gender
from customer

TIP:If you're using something like SSMS to write a query, dragging in columns will put brackets around the columns. This is only necessary if you have spaces in column names, but a DBA will tend to avoid that like the plague. Also, if you need a column header to be something specific, you can use the as keyword like in my first example.

提示：如果您使用 SSMS 之类的东西来编写查询，拖入列会在列周围放置括号。仅当列名中有空格时才需要这样做，但 DBA 会倾向于避免这种情况，就像瘟疫一样。此外，如果您需要一个特定的列标题，您可以像在我的第一个示例中一样使用 as 关键字。

W3Schools has a good tutorial on count()

The COUNT(column_name)function returns the number of values (NULL values will not be counted) of the specified column:

W3Schools 有一篇关于 count() 的好教程

该COUNT(column_name)函数返回指定列的值的数量（NULL 值不会被计算在内）：

SELECT COUNT(column_name) FROM table_name;

The COUNT(*)function returns the number of records in a table:

该COUNT(*)函数返回表中的记录数：

SELECT COUNT(*) FROM table_name;

The COUNT(DISTINCT column_name)function returns the number of distinct values of the specified column:

该COUNT(DISTINCT column_name)函数返回指定列的不同值的数量：

SELECT COUNT(DISTINCT column_name) FROM table_name; 

COUNT(DISTINCT)works with ORACLE and Microsoft SQL Server, but not with Microsoft Access.

COUNT(DISTINCT)适用于 ORACLE 和 Microsoft SQL Server，但不适用于 Microsoft Access。

回答by shawnt00

It's odd to repeat the same number in every but it sounds like this is what you're asking for. And not that this might not work in your flavor of SQL. MS Access?

在每个中重复相同的数字很奇怪，但听起来这就是您所要求的。并不是说这可能不适用于您的 SQL 风格。微软访问？

SELECT [Name], (select count(*) from [customer]), [Age], [Gender]
FROM [customer]