在foxpro中计数有条件

Question

我有一个表，

id name date
1  a    08/09/2003
2  b    02/03/2003
3  c    10/08/2004
4  c    25/08/2007
5  a    01/01/2008

我想要计数数据库。 下表及结果如下：

2003 = 2
2004 = 1
2007 = 0 because c has in 2004-year
2008 = 0 because a has in 2003-year

Answer 1

首先，获取名称及其出现的最早年份：

select name, min(year(date)) as year from table
group by name into cursor temp

然后获取该年份内的计数：

select count(table.name) 
from table join temp on table.name = temp.name 
and year(table.date) = temp.year

Answer 2

我可能正在解决一个不同的问题，但此代码给出了每年未出现在前几年的名称的计数：

*-- Get the firstyear for each name
Select Name, Min(Year(Date)) As firstyear ;
  From table1;
  Group By Name Into Cursor temp1

*-- Get the year of the date for each entry
Select Id, Name, Year(Date) As yr From table1 Into Cursor temp2

*-- Identify those rows that appear for the first time
Select temp2.*, temp1.firstyear, Iif(temp2.yr = temp1.firstyear, 1, 0) As countme ;
  FROM temp2 INNER Join temp1 ;
  ON temp2.Name = temp1.Name Into Cursor temp3

*-- Add up the "CountMe" fields to get the sum.
Select yr, Sum(countme) From temp3 Group By yr

Answer 3

*/ 首先，在他们进行交易的第一年按姓名计算
*/ 对于...除了此人拥有的总交易量之外
*/ 年度.. 例如：“a”和“c”人的两个重叠

SELECT ;
        YT.Name,;
        MIN( YEAR( YT.DATE )) as FirstYear,;
        COUNT(*) as TotalPerName;
    FROM ;
        YourTable YT;
    GROUP BY ;
        1;
    INTO ;
        CURSOR C_ByNameTotals

*/ 现在您已经根据每个人的第一年及其总数得出了总计
*/ 不管年份的条目，获取年份和 HAVE 的总数
*/ 给定年份的条目....然后对所有原始年份的可能性进行联合
*/ 不在 C_ByNameTotals 结果集中。 （因此是 2007 年和 2008 年）

SELECT;
        FirstYear as FinalYear,;
        SUM( TotalPerName ) as YrCount;
    FROM ;
        C_ByNameTotals;
    GROUP BY ;
        1;
    INTO ;
        CURSOR C_FinalResults;
UNION;
SELECT DISTINCT;
        YEAR( Date ) as FinalYear,;
        0 as YrCount;
    FROM ;
        YourTable ;
    WHERE ;
        YEAR( Date ) NOT IN ;
            ( select FirstYear FROM C_ByNameTotals )