select S.studentid,S.name,S.sex ,
piaoid1=(Select top 1 ID from FEE where piaoid=f.piaoid),
piaoid2=(Select ID from FEE where piaoid>=f.piaoid)
from student s,FEE F
WHERE S.PIAOID=F.PIAOID
piaoid1=(Select top 1 ID from FEE where piaoid=f.piaoid),
piaoid2=(Select ID from FEE where piaoid>=f.piaoid)
from student s,FEE F
WHERE S.PIAOID=F.PIAOID
piaoid1=(Select top 1 ID from FEE where piaoid=f.piaoid),
piaoid2=(Select ID from FEE where piaoid>=f.piaoid)
from student s,FEE F
WHERE S.STUDENTID=F.PIAOID
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('STUDENT') IS NOT NULL
DROP TABLE STUDENT
GO
CREATE TABLE STUDENT(STUDENTID VARCHAR(18),NAME VARCHAR(10),SEX varchar(2))
go
insert into STUDENT
select '2009062410001','房雅梅','女' union all
select '2009062410002','蔡新禹','男' union all
select '2009062410003','顾玉珠','男' union all
select '2009062410004','顾伟康','男' union all
select '2009062410005','陆勇海','男' union all
select '2009062410006','陆汉凌','男'
go
IF OBJECT_ID('FEE') IS NOT NULL
DROP TABLE FEE
GO
CREATE TABLE FEE(PIAOID VARCHAR(10),STUDENTID VARCHAR(18),NAME VARCHAR(10),id int)
go
insert into FEE
select '0120450','2009062410001','房雅梅',1 union all
select '0120451','2009062410001','房雅梅',2 union all
select '0120452','2009062410003','顾玉珠',3 union all
select '0120453','2009062410004','顾伟康',4 union all
select '0120454','2009062410005','陆勇海',5 union all
select '0120455','2009062410006','陆汉凌',6
go
select DISTINCT S.*,
piaoid1=(Select top 1 ID from FEE where F.STUDENTID=STUDENTID),
piaoid2=(Select MAX(ID) from FEE where F.STUDENTID=STUDENTID and ID not in(select top 1 ID from FEE where F.STUDENTID=STUDENTID ) )
from student s,FEE F
WHERE S.STUDENTID=F.STUDENTID/*------------
2009062410001 房雅梅 女 1 2
2009062410003 顾玉珠 男 3 NULL
2009062410004 顾伟康 男 4 NULL
2009062410005 陆勇海 男 5 NULL
2009062410006 陆汉凌 男 6 NULL
-------*/
piaoid=(select top 1 piaoid from fee where f.studentid=studentid order by piaoid),
piaoid2=(select max(piaoid) from fee where f.studentid=studentid and piaoid
not in(select top 1 piaoid from fee where f.studentid=studentid ) )
from student s left join fee f on s.studentid=f.studentid
studentid name sex piaoid1 piaoid2
------------------ ---------- ---- ---------- ----------
2009062410001 房雅梅 女 0120450 0120451
2009062410002 蔡新禹 男 NULL NULL
2009062410003 顾玉珠 男 0120452 NULL
2009062410004 顾伟康 男 0120453 NULL
2009062410005 陆勇海 男 0120454 NULL
2009062410006 陆汉凌 男 0120455 NULL(6 行受影响)
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S+',','')+piaoid from fee where studentid=@Col1
return @S
end
goselect distinct s.*,piaoid=dbo.F_Str(s.studentid)
from student s left join fee f
on s.studentid=f.studentidstudentid name sex piaoid
------------------ ---------- ---- ---------------------
2009062410001 房雅梅 女 0120450,0120451
2009062410002 蔡新禹 男 NULL
2009062410003 顾玉珠 男 0120452
2009062410004 顾伟康 男 0120453
2009062410005 陆勇海 男 0120454
2009062410006 陆汉凌 男 0120455(6 行受影响)