只能做到这一步,剩下的是前台的来做 select id,date=(select date from tb1 where id=a.id),content from tb2 a
create table tb1(id int,date int) create table tb2(id int,fid int,content char(3)) insert tb1 select 1,2004 insert tb1 select 2,2003 insert tb2 select 1,1,'111' insert tb2 select 2,1,'222' insert tb2 select 3,2,'333' insert tb2 select 4,2,'444'select a.id,a.date,b.content into #t from tb1 a,tb2 b where a.id=b.fid declare @m int,@n int set @m=0 set @n=0 update #t set @m=id=case when @m=id then null else id end, @n=date=case when @n=date then null else date end select case when id is null then '' else cast(id as varchar(10)) end as id, case when date is null then '' else cast(date as varchar(10)) end as date, content from #t drop table #t --查询结果 /* id date content ---------- ---------- ------- 1 2004 111 222 2 2003 333 444 */
select (case when id=(select min(id) from tb2 where fid=b.id) then id else '' end) as id ,(case when id=(select min(id) from tb2 where fid=b.id) then b.date else '' end) as id ,a.content from tb2 a,tb1 b where b.fid=a.id
select (case when id=(select min(id) from tb2 where fid=b.id) then id else '' end) as id ,(case when id=(select min(id) from tb2 where fid=b.id) then b.date else '' end) as date --这里改了一下 ,a.content from tb2 a,tb1 b where b.fid=a.id
--简化一点 create table tb1(id int,date int) create table tb2(id int,fid int,content char(3)) insert tb1 select 1,2004 insert tb1 select 2,2003 insert tb2 select 1,1,'111' insert tb2 select 2,1,'222' insert tb2 select 3,2,'333' insert tb2 select 4,2,'444'select a.id,a.date,b.content into #t from tb1 a,tb2 b where a.id=b.fid declare @m int set @m=0 update #t set @m=id=case when @m=id then null else id end select case when id is null then '' else cast(id as varchar(10)) end as id, case when id is null then '' else cast(date as varchar(10)) end as date, content from #t drop table #t drop table tb1 drop table tb2 --查询结果 /* id date content ---------- ---------- ------- 1 2004 111 222 2 2003 333 444(所影响的行数为 4 行) */
try this sql on sql server 2000:Select isnull(tb1.id,''),isnull(tb1.date,''),tb2.content from tb2 LEFT OUTER JOIN tb1 ON tb1.id=tb2.fid
TO:wl_weiliang(麦克老狼) --把基本的忘了,不好意思,你的方法好是好,不过少了类型转换,会显示出0来的Select isnull(cast(tb1.id as varchar(10),''),isnull(cast(tb1.date as varchar(10),''),tb2.content from tb2 LEFT OUTER JOIN tb1 ON tb1.id=tb2.fid
TO:lxd99423(苹果) --呵呵
create table tb1(id int,date int) create table tb2(id int,fid int,content char(3)) insert tb1 select 1,2004 insert tb1 select 2,2003 insert tb2 select 1,1,'111' insert tb2 select 2,1,'222' insert tb2 select 3,2,'333' insert tb2 select 4,2,'444'Select isnull(cast(tb1.id as varchar(10)),'') as id,isnull(cast(tb1.date as varchar(10)),'') as date,tb2.content from tb2 LEFT OUTER JOIN tb1 ON tb1.id=tb2.fid 结果 id date content ---------- ---------- ------- 1 2004 111 1 2004 222 2 2003 333 2 2003 444
select id,date=(select date from tb1 where id=a.id),content from tb2 a
create table tb2(id int,fid int,content char(3))
insert tb1 select 1,2004
insert tb1 select 2,2003
insert tb2 select 1,1,'111'
insert tb2 select 2,1,'222'
insert tb2 select 3,2,'333'
insert tb2 select 4,2,'444'select a.id,a.date,b.content into #t from tb1 a,tb2 b where a.id=b.fid
declare @m int,@n int
set @m=0
set @n=0
update #t set @m=id=case when @m=id then null else id end,
@n=date=case when @n=date then null else date end
select case when id is null then '' else cast(id as varchar(10)) end as id,
case when date is null then '' else cast(date as varchar(10)) end as date,
content from #t
drop table #t
--查询结果
/*
id date content
---------- ---------- -------
1 2004 111
222
2 2003 333
444
*/
then id
else ''
end) as id
,(case when id=(select min(id) from tb2 where fid=b.id)
then b.date
else ''
end) as id
,a.content
from tb2 a,tb1 b where b.fid=a.id
then id
else ''
end) as id
,(case when id=(select min(id) from tb2 where fid=b.id)
then b.date
else ''
end) as date --这里改了一下
,a.content
from tb2 a,tb1 b where b.fid=a.id
create table tb1(id int,date int)
create table tb2(id int,fid int,content char(3))
insert tb1 select 1,2004
insert tb1 select 2,2003
insert tb2 select 1,1,'111'
insert tb2 select 2,1,'222'
insert tb2 select 3,2,'333'
insert tb2 select 4,2,'444'select a.id,a.date,b.content into #t from tb1 a,tb2 b where a.id=b.fid
declare @m int
set @m=0
update #t set @m=id=case when @m=id then null else id end
select case when id is null then '' else cast(id as varchar(10)) end as id,
case when id is null then '' else cast(date as varchar(10)) end as date,
content from #t
drop table #t
drop table tb1
drop table tb2
--查询结果
/*
id date content
---------- ---------- -------
1 2004 111
222
2 2003 333
444(所影响的行数为 4 行)
*/
from tb2 LEFT OUTER JOIN
tb1 ON tb1.id=tb2.fid
--把基本的忘了,不好意思,你的方法好是好,不过少了类型转换,会显示出0来的Select isnull(cast(tb1.id as varchar(10),''),isnull(cast(tb1.date as varchar(10),''),tb2.content
from tb2 LEFT OUTER JOIN
tb1 ON tb1.id=tb2.fid
--呵呵
create table tb2(id int,fid int,content char(3))
insert tb1 select 1,2004
insert tb1 select 2,2003
insert tb2 select 1,1,'111'
insert tb2 select 2,1,'222'
insert tb2 select 3,2,'333'
insert tb2 select 4,2,'444'Select isnull(cast(tb1.id as varchar(10)),'') as id,isnull(cast(tb1.date as varchar(10)),'') as date,tb2.content
from tb2 LEFT OUTER JOIN
tb1 ON tb1.id=tb2.fid
结果
id date content
---------- ---------- -------
1 2004 111
1 2004 222
2 2003 333
2 2003 444
你的sql好像通不过啊
我的sql是2000的但显示的和 taiguang(银狐) 贴出来是一样的