create table #a(Fname varchar(30),fqun decimal(18,2),fclass int )
create table #b ( fclass int,name varchar(30))
insert into #a
select 'a',20,1
union all
select 'a',20,2
union all
select 'a',20,3
union all
select 'B',10,1
union all
select 'B',10,2insert into #b
select 1,'A'
union
select 2,'B'
union
select 3,'C'select a.*,b.name from #a a inner join #b b on a.fclass = b.fclass
要求输出格式为
Fname fqun fclass name
------------------------------ -------------------- ----------- ------------------------------
a 20.00 1 A
2 B
3 C
B 10.00 1 A
2 B
create table #b ( fclass int,name varchar(30))
insert into #a
select 'a',20,1
union all
select 'a',20,2
union all
select 'a',20,3
union all
select 'B',10,1
union all
select 'B',10,2insert into #b
select 1,'A'
union
select 2,'B'
union
select 3,'C'select a.*,b.name from #a a inner join #b b on a.fclass = b.fclass
要求输出格式为
Fname fqun fclass name
------------------------------ -------------------- ----------- ------------------------------
a 20.00 1 A
2 B
3 C
B 10.00 1 A
2 B
解决方案 »
- 存储过程继续求教
- ManagementStudio中如何能让指针移动到条件行
- 循环里可以回滚事务吗?
- sql如何使标识列按某一列递增
- 字段循环增加内容
- 用xp_cmdshell怎样才能把本机文件拷到别的机子上
- 对象-关系数据库之间的映射,哪位能给我一个详细的例子讲讲吗?
- 请教关于 命令textcopy 中 ERROR: Argument '/' not recognized 的报错问题
- 比較菜的問題,在存儲過程中,怎樣檢測文本文件是否存在並進行讀寫,或追加內容?
- 问一个问题:在PB中用adptive anywhere server 6.0作的程序能否再次转为SQL SERVERR的程序
- 安装SQLserver2005开发版以后的疑问
- 数据库连接问题?网页调用
表的两个字段F1,F2,其值如下
F1 F2
01 a
01 aa
02 b
02 bb
02 bbb
现在想用SQL语句让上面的记录显示为
01 a
aa
02 b
bb
bbb
declare @t table(F1 varchar(8),F2 varchar(8))
insert into @t values('01','a ')
insert into @t values('01','aa ')
insert into @t values('02','b ')
insert into @t values('02','bb ')
insert into @t values('02','bbb')select (case when exists(select 1 from @t where F1=t.F1 and F2<t.F2) then '' else F1 end) as NewF1,F2
from @t t
order by F1,F2
create table #b ( fclass int,name varchar(30))
insert into #a
select 'a',20,1
union all
select 'a',20,2
union all
select 'a',20,3
union all
select 'B',10,1
union all
select 'B',10,2 insert into #b
select 1,'A'
union
select 2,'B'
union
select 3,'C' select (case when exists(select 1 from #a where Fname=t.Fname and fclass<t.fclass) then '' else Fname end) as Fname
,(case when exists(select 1 from #a where fqun=t.fqun and fclass<t.fclass) then '' else ltrim(fqun) end) as fqun
,t.fclass, x.name
from #a t
left join #b x on t.fclass=x.fclass
order by t.Fname,t.fclassdrop table #b
drop table #a/*Fname fqun fclass name
------------------------------ ----------------------------------------- ----------- ------------------------------
a 20.00 1 A
2 B
3 C
B 10.00 1 A
2 B(5 行受影响)*/
case when a.fclass=1 then a.fname else '' end as fname,
case when a.fclass=1 then ltrim(a.fqun) else '' end as fqun,
a.fclass,
b.name
from #a a inner join #b b on a.fclass = b.fclassfname fqun fclass name
a 20.00 1 A
2 B
3 C
B 10.00 1 A
2 B
Fname=case when not exists(select 1 from #a where Fname=a.Fname and fclass<a.fclass) then Fname else '' end,
fqun=case when not exists(select 1 from #a where fqun=a.fqun and fclass<a.fclass) then ltrim(fqun) else '' end,
a.fclass,b.name
from #a a inner join #b b on a.fclass = b.fclass
/*
Fname fqun fclass name
------------------------------ ----------------------------------------- ----------- ------------------------------
a 20.00 1 A
2 B
3 C
B 10.00 1 A
*/ 2 B
create table #b ( fclass int,name varchar(30))
insert into #a
select 'a',20,1
union all
select 'a',20,2
union all
select 'a',20,3
union all
select 'B',10,1
union all
select 'B',10,2 insert into #b
select 1,'A'
union
select 2,'B'
union
select 3,'C' select a.*,b.name from #a a inner join #b b on a.fclass = b.fclass select Fnane=(case when exists (select 1 from #a where a.fname = fname and fclass<a.fclass) then '' else a.fname end)
,fqun=(case when exists (select 1 from #a where a.fname = fname and fclass<a.fclass) then '' else ltrim(a.fqun) end)
,a.fclass
,b.name
from #a a
inner join #b b
on a.fclass =b.fclass
order by a.Fname,a.fclassdrop table #a,#b