表结构T
tID int,
tDate datetime,
tTitle nvarchar,
tContent nvarchar,
tRef int
==============================
数据
1 2008-9-8 标题1 内容1 0
2 2009-9-7 标题2 内容2 1
3 2009-9-7 标题3 内容4 1如何
select tid,tDate,tTitle,tContent (根据tRef引用ID获取 tTitle + tContent) as refContent
???
tID int,
tDate datetime,
tTitle nvarchar,
tContent nvarchar,
tRef int
==============================
数据
1 2008-9-8 标题1 内容1 0
2 2009-9-7 标题2 内容2 1
3 2009-9-7 标题3 内容4 1如何
select tid,tDate,tTitle,tContent (根据tRef引用ID获取 tTitle + tContent) as refContent
???
tDate datetime,
tTitle nvarchar(20),
tContent nvarchar(20),
tRef int)
insert into tb values(1 ,'2008-9-8' ,'标题1', '内容1', 0)
insert into tb values(2 ,'2009-9-7' ,'标题2', '内容2', 1)
insert into tb values(3 ,'2009-9-7' ,'标题3', '内容4', 1)
go
select t.* , (select tTitle + tContent from tb where tid = t.tRef) from tb tdrop table tb /*
tID tDate tTitle tContent tRef
----------- ------------------------------------------------------ -------------------- -------------------- ----------- ----------------------------------------
1 2008-09-08 00:00:00.000 标题1 内容1 0 NULL
2 2009-09-07 00:00:00.000 标题2 内容2 1 标题1内容1
3 2009-09-07 00:00:00.000 标题3 内容4 1 标题1内容1(所影响的行数为 3 行)
*/
tDate datetime,
tTitle nvarchar(20),
tContent nvarchar(20),
tRef int)
insert into tb values(1 ,'2008-9-8' ,'标题1', '内容1', 0)
insert into tb values(2 ,'2009-9-7' ,'标题2', '内容2', 1)
insert into tb values(3 ,'2009-9-7' ,'标题3', '内容4', 1)
go
select t.* , refContent = (select tTitle + tContent from tb where tid = t.tRef) from tb tdrop table tb /*
tID tDate tTitle tContent tRef refContent
----------- ------------------------------------------------------ -------------------- -------------------- ----------- ----------------------------------------
1 2008-09-08 00:00:00.000 标题1 内容1 0 NULL
2 2009-09-07 00:00:00.000 标题2 内容2 1 标题1内容1
3 2009-09-07 00:00:00.000 标题3 内容4 1 标题1内容1(所影响的行数为 3 行)
*/
tid,
tDate,
tTitle,
tContent,
(selct tTitle + tContent from T where tid=a.tRef) as refContent
from T a
go
create table tb(
tID int,
tDate datetime,
tTitle nvarchar(15),
tContent nvarchar(15),
tRef int)
--==============================
--数据
insert tb select 1 ,'2008-9-8', N'标题1' ,N'内容1', 0
insert tb select 2 ,'2009-9-7' ,N'标题2' ,N'内容2' ,1
insert tb select 3, '2009-9-7' ,N'标题3', N'内容4' ,1
select t.* , (select tTitle + tContent from tb where tid = t.tRef)AS refContent from tb t
/*tID tDate tTitle tContent tRef refContent
----------- ----------------------- --------------- --------------- ----------- ------------------------------
1 2008-09-08 00:00:00.000 标题1 内容1 0 NULL
2 2009-09-07 00:00:00.000 标题2 内容2 1 标题1内容1
3 2009-09-07 00:00:00.000 标题3 内容4 1 标题1内容1(3 行受影响)*/
如
select t.*,(select tTitle as rTitle,tContent as rContent
???
go
create table tb(
tID int,
tDate datetime,
tTitle nvarchar(15),
tContent nvarchar(15),
tRef int)
--==============================
--数据
insert tb select 1 ,'2008-9-8', N'标题1' ,N'内容1', 0
insert tb select 2 ,'2009-9-7' ,N'标题2' ,N'内容2' ,1
insert tb select 3, '2009-9-7' ,N'标题3', N'内容4' ,1
select t.* , (select tTitle from tb where tid = t.tRef)AS tTitle,
(select tContent from tb where tid = t.tRef)AS tContent
from tb t
/*(1 行受影响)
tID tDate tTitle tContent tRef tTitle tContent
----------- ----------------------- --------------- --------------- ----------- --------------- ---------------
1 2008-09-08 00:00:00.000 标题1 内容1 0 NULL NULL
2 2009-09-07 00:00:00.000 标题2 内容2 1 标题1 内容1
3 2009-09-07 00:00:00.000 标题3 内容4 1 标题1 内容1(3 行受影响)*/
create table tb(tID int,
tDate datetime,
tTitle nvarchar(20),
tContent nvarchar(20),
tRef int)
insert into tb values(1 ,'2008-9-8' ,'标题1', '内容1', 0)
insert into tb values(2 ,'2009-9-7' ,'标题2', '内容2', 1)
insert into tb values(3 ,'2009-9-7' ,'标题3', '内容4', 1)
go
select t.* ,
refTitle = (select tTitle from tb where tid = t.tRef) ,
refContent = (select tContent from tb where tid = t.tRef)
from tb tdrop table tb /*
tID tDate tTitle tContent tRef refTitle refContent
----------- ------------------------------------------------------ -------------------- -------------------- ----------- -------------------- --------------------
1 2008-09-08 00:00:00.000 标题1 内容1 0 NULL NULL
2 2009-09-07 00:00:00.000 标题2 内容2 1 标题1 内容1
3 2009-09-07 00:00:00.000 标题3 内容4 1 标题1 内容1(所影响的行数为 3 行)*/