如果data 是2006年的话取此数据如果不是的话 取其他年度中 point 最大的那条不明白啊不明白~~~
取其他年度中 point 最大的那条?要取分类吗?最大哪条
问题说得不清楚,俺这样理解的,不知对不对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')<0begin 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'
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)
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
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'
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 这样不知对不对?
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)