求sql语句，急？？？？？？？？？？

Question

select item, (select count(*) from 表 
              where year(date)=year(date) and item <=a.item) no,
       year(date) [year]
from 表 order by year(date),item

Answer 1

select item, (select count(*) from 表 
              where year(date)=year(date) and item <=a.item) no,
       year(date) [year]
from 表 a order by year(date),item

Answer 2

select item,(select count(*) from 表 where item<=tem.item and year([date])=year(tem.[date])) no,year([date]) from 表 tem

Answer 3

select item,(select count(1) from 表 a where a.date<b.date and a.date=b.date) [no],year([date]) [year] from 表 b

Answer 4

sorry,应该是
select item,(select count(1) from 表 a where a.date<b.date and a.year(date)=b.year(date)) [no],year([date]) [year] from 表 b

Answer 5

select item
  ,no=(select sum(1) from 表 where year([date])=year(a.[date]) and item<=a.item)
  ,[year]=year([date])
from 表 a

Answer 6

try:
select a.item ,a.date,
       no = count(case when year(a.date) = year(b.date) 
                       and a.date >= b.date then 1 else null end)
from 表 a
cross join 表 b
group by a.item ,a.date

Answer 7

drop table tablename
create table tablename (item int identity(1,1),Tdate datetime)
insert into tablename (tdate) values ('2003-1-1')
insert into tablename (tdate) values ('2003-2-2')
insert into tablename (tdate) values ('2004-5-5')select 
    item,
    year([tdate]) as "year",
    (select 
         sum(1) 
     from 
         tablename a 
     where 
         a.item<=b.item 
           and 
         year(a.[tdate])=year(b.[tdate])
    ) as no 
from 
    tablename b 
order by 
    year(b.[tdate])
drop table tablename

Answer 8

declare @i int
declare @date datetime
declare @year datetime
declare @count int
set @count = 0declare tmp_cursor cursor for select item from 表open tmp_cursor
fetch tmp_cursor into @i,@date
while @@fetch_status=0
begin
  if @year = year(@date)
    @count = @count + 1
  else
    begin
      @count = 1
      @year = year(@date)
    end  print @i
  print @count
  print @year  fetch tmp_cursor into @i,@date
end
close tmp_cursor
deallocate tmp_cursor

Answer 9

try:
select a.item ,year(a.date),
       no = count(case when  year(a.date) = year(b.date) and a.item >= b.item then 1 else null end)
from 表 a
cross join 表 b
group by a.item ,year(a.date)

Answer 10

try:
select a.item, year(a.date) [year],
no = (select count(*) from #temp b where year(a.date) = year(b.date) and a.item >= b.item)
from 表 a