--> liangCK小梁 于2008-07-17
--> 生成测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (name int,age varchar(2),companyName varchar(5),companyTitle varchar(5))
insert into #T
select 1,'XX','YYYYY','ZZZZZ' union all
select 1,'XX','AAAAA','BBBBB' union all
select 2,'RR','RRRRR','PPPPP' union all
select 3,'WW','WWWWW','WWWWW' union all
select 3,'WW','QQQQQ','QQQQQ'select name=case when companyTitle=maxtitle then ltrim(name) else '' end,
age,companyName,companyTitle
from
(
select *,maxtitle=max(companyTitle) over(partition by name) from #T
) t/*
name age companyName companyTitle
------------ ---- ----------- ------------
1 XX YYYYY ZZZZZ
XX AAAAA BBBBB
2 RR RRRRR PPPPP
3 WW WWWWW WWWWW
WW QQQQQ QQQQQ(5 行受影响)
*/
--> 生成测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (name int,age varchar(2),companyName varchar(5),companyTitle varchar(5))
insert into #T
select 1,'XX','YYYYY','ZZZZZ' union all
select 1,'XX','AAAAA','BBBBB' union all
select 2,'RR','RRRRR','PPPPP' union all
select 3,'WW','WWWWW','WWWWW' union all
select 3,'WW','QQQQQ','QQQQQ'select name=case when companyTitle=maxtitle then ltrim(name) else '' end,
age,companyName,companyTitle
from
(
select *,maxtitle=max(companyTitle) over(partition by name) from #T
) t/*
name age companyName companyTitle
------------ ---- ----------- ------------
1 XX YYYYY ZZZZZ
XX AAAAA BBBBB
2 RR RRRRR PPPPP
3 WW WWWWW WWWWW
WW QQQQQ QQQQQ(5 行受影响)
*/
maxtitle then ltrim(name)这句是什么意思呢?
请教
3张表做关联的时候的SQL语句么?不甚感激