select * from a left join b on a.a=b.a left join c on a.b=c.b left join d on a.c=d.c
没表达清楚: LEFT JOIN 结果如下: 1 2 3 a 1 2 3 b 1 2 3 c 要变成 1 2 3 a b c
create table #A(A int,B int,C int,D varchar(20)) insert into #A select 1,2,3,'a' union all select 1,2,3,'b' union all select 1,2,3,'c' union all select 1,2,4,'a' union all select 1,2,4,'b' union all select 1,2,4,'c' ---测试 select case when rowid=1 then cast(a AS varchar(10)) else '' end A, case when rowid=1 then cast(b AS varchar(10)) else '' end b, case when rowid=1 then cast(c AS varchar(10)) else '' end c,d from ( select *,ROW_NUMBER()over(partition by a,b,c order by d) as rowid from #A) as a
我的数据库是SQL server 2000的还是,不支持ROW_NUMBER()over
select * from ( select id,产量,(select count(*) from 表 as a2 where a2.id=a1.id and a2.产量>=a1.产量) as rank from 表 as a1) as b order by id,n desc 参考下 修改吧
from a left join b on a.a=b.a
left join c on a.b=c.b
left join d on a.c=d.c
LEFT JOIN 结果如下:
1 2 3 a
1 2 3 b
1 2 3 c
要变成
1 2 3 a
b
c
insert into #A
select 1,2,3,'a'
union all
select 1,2,3,'b'
union all
select 1,2,3,'c'
union all
select 1,2,4,'a'
union all
select 1,2,4,'b'
union all
select 1,2,4,'c'
---测试
select case when rowid=1 then cast(a AS varchar(10)) else '' end A,
case when rowid=1 then cast(b AS varchar(10)) else '' end b,
case when rowid=1 then cast(c AS varchar(10)) else '' end c,d
from (
select *,ROW_NUMBER()over(partition by a,b,c order by d) as rowid
from #A) as a
select id,产量,(select count(*) from 表 as a2 where a2.id=a1.id and a2.产量>=a1.产量) as rank from 表 as a1) as b
order by id,n desc 参考下 修改吧