简单问题 有分散

[xingxing2378]

一张表
id name point data
1 a 5 2006
1 a 5 2005
1 a 5 2004
1 a 5 2006
1 a 5 2005
1 a 5 2006

如果data 是2006年的话取此数据如果不是的话 取其他年度中 point 最大的那条

[xingxing2378]

条件是不能用函数 只能用sql语句阿

[九斤半]

如果data 是2006年的话取此数据如果不是的话 取其他年度中 point 最大的那条

不明白啊不明白~~~

[xiaoku]

取其他年度中 point 最大的那条

?要取分类吗?最大哪条

[hhhdyj]

问题说得不清楚，俺这样理解的，不知对不对

declare @t table(id int, name varchar(10), point int, data int)
insert into @t values(1, 'a' , 5, 2006)
insert into @t values(1, 'a' , 4, 2005)
insert into @t values(1, 'a' , 3, 2004)
insert into @t values(2, 'b' , 2, 2006)
insert into @t values(3, 'c' , 1, 2005)
insert into @t values(3, 'c' , 7, 2004)

select id, name, point from @t where data = 2006
union
select a.id, a.name, max(point) as point
from @t a
left join (select id from @t where data = 2006)b
on a.id = b.id
where b.id is null
group by a.id, a.name

[早起晚睡]

if (select count(*) from 表1 where data='2006')<0

begin
select a.id,a.name,a.point,a.data from 表1 a inner join (select id,max(point) as point from 表1 group by id) b
on
a.id=b.id
end
else
select * from 表1 where data='2006'

[xiaoku]

select id ,name ,case when date ='2006' then point
else (select (max(point) from tb where id =a.id and name =a.name and data =a.date ) end as point,
,date
from tb a

这样不知对不对?

[十一月猪]

select id , name , point , data from t where data = '2006'
union
select id , name , point , data from t a where data <>'2006'
and not exists (select 1 from t where data = a.data and point > a.point)