从SQL中获取一系列整数中单个事件的惟一计数。

So the title may be misleading, however I can't really think how to word it.

所以标题可能会误导人，但是我真的不知道该怎么说。

In theory, lets say I have a table of 7000 records with the column Year.

理论上来说，假设我有一个有7000条记录的表格。

Year data ranges from 1990 to 2011 but their are hundreds of repeat occurences of the same year.

年数据范围从1990年到2011年，但它们是同一年度的数百次重复发生。

Using my current SQL statement:

使用我当前的SQL语句:

SELECT MonthlySales.Year FROM MonthlySales

simply would return 7000 records.

只需返回7000条记录。

I need to get a count of each individual occurence of each year ONCE in a table (so there should be 21 records in my table). I am using PHP and SQL server 2008.

我需要把每一年的每一次发生的次数计算一次(所以我的表中应该有21条记录)。我使用的是PHP和SQL server 2008。

I originally just filled in the options form like this:

我最初只是填了这样的选项:

<option>1990</option>
<option>1991</option>

However I have to allow the user to input more other Year's if need's be in to my data so it has to be dynamic. With the data I hope to use it in a HTML form that gives the option to select a year:

但是，我必须允许用户输入更多的其他年份，如果需要我的数据，所以它必须是动态的。有了这些数据，我希望将它用在一个HTML表单中，这样可以选择一年:

<option>.$year.</option>

Could someone clarify whether this would also work.

是否有人能澄清这是否可行。

I just cannot think how to do it! Even though it appears simple.

我就是不知道该怎么做!尽管看起来很简单。

Thanks for looking.

谢谢你看。

2 个解决方案

#1

To get the number of rows that each year appears

以获得每年出现的行数。

select count(*), year
from MonthlySales
group by year;

To simply remove duplicates

简单地删除重复的

SELECT DISTINCT Year FROM MonthlySales;

#2

the below SQL will give you the number of times the year appears in the table and the year

下面的SQL将给出年份在表中出现的次数和年份。

select count(Year), year from MonthlySales group by year

if you want the amount a specific year appears, just add a where cause like

如果你想要具体年份的数量，只需添加一个原因。

select count(Year), year from MonthlySales where year = ? group by year

#1