1.函数法.
create table t (names nvarchar(10),books nvarchar(10),dates nvarchar(10))insert into t
select '张三', 'A', '2009-1-8'
union all select'张三', 'B', '2009-3-2'
union all select'张三', 'C', '2009-3-26'
union all select'张三', 'D', '2009-4-5'
union all select'李四', 'F', '2009-1-3'
union all select'李四', 'H', '2009-1-8'
union all select'赵大', 'R', '2008-12-26'
union all select'赵大', 'D', '2009-1-15' --select names,max(dates) from t group by names
drop function booklist
go
create function booklist(@names nvarchar(100))
returns nvarchar(20)
as
begin
declare @book nvarchar(20)
set @book = ''
select @book = @book + ' | '+ books from t where names=@names order by dates desc
return substring(@book,3,len(@book)-1)
endselect names,dbo.booklist(names) as books,max(dates) as dates from t group by names
/*
names books dates
---------- -------------------- ----------
张三 D | C | B | A 2009-4-5
李四 H | F 2009-1-8
赵大 D | R 2009-1-15
(3 row(s) affected)
*/
create table t (names nvarchar(10),books nvarchar(10),dates nvarchar(10))insert into t
select '张三', 'A', '2009-1-8'
union all select'张三', 'B', '2009-3-2'
union all select'张三', 'C', '2009-3-26'
union all select'张三', 'D', '2009-4-5'
union all select'李四', 'F', '2009-1-3'
union all select'李四', 'H', '2009-1-8'
union all select'赵大', 'R', '2008-12-26'
union all select'赵大', 'D', '2009-1-15' --select names,max(dates) from t group by names
drop function booklist
go
create function booklist(@names nvarchar(100))
returns nvarchar(20)
as
begin
declare @book nvarchar(20)
set @book = ''
select @book = @book + ' | '+ books from t where names=@names order by dates desc
return substring(@book,3,len(@book)-1)
endselect names,dbo.booklist(names) as books,max(dates) as dates from t group by names
/*
names books dates
---------- -------------------- ----------
张三 D | C | B | A 2009-4-5
李四 H | F 2009-1-8
赵大 D | R 2009-1-15
(3 row(s) affected)
*/
解决方案 »
- 请问这个SQL语句该怎么写?
- 这个是什么原因
- 急急急 求教解决问题 [OLE DB 访问接口 "MSDAORA" 无法启动分布式事务。]
- 救命的sql2000问题,本机备份的数据库在别的电脑上不能恢复
- 100分求一个论坛源码
- 用了union就不能用order by了?
- 请教高手这个SQL语句该怎么写啊?
- 请教一个SQL 版里没有的问题,可能是太笨了,我问了好多朋友都不行
- 继续提问:如何在命令行的方式下备份SQL2000的数据库???
- 用SQL语句能不能根据“变量的值做为表名”来创建表?
- SQL2000存储过程问题,请高人来回答一下,急!!!在线等
- 这是什么错误啊??连接数据库sqlserver时
drop table t
create table t (names nvarchar(10),books nvarchar(10),dates datetime)insert into t
select '张三', 'A', '2009-1-8'
union all select'张三', 'B', '2009-3-2'
union all select'张三', 'C', '2009-3-26'
union all select'张三', 'D', '2009-4-5'
union all select'李四', 'F', '2009-1-3'
union all select'李四', 'H', '2009-1-8'
union all select'赵大', 'R', '2008-12-26'
union all select'赵大', 'D', '2009-1-15' --select names,max(dates) from t group by names
drop function booklist
go
create function booklist(@names nvarchar(100))
returns nvarchar(20)
as
begin
declare @book nvarchar(20)
set @book = ''
select @book = @book + ' | '+ books from t where names=@names order by dates desc
return substring(@book,3,len(@book)-1)
endselect names,dbo.booklist(names) as books,convert(nvarchar(10),max(dates),120) as dates from t
group by names
order by max(dates) desc
/*
names books dates
---------- -------------------- ----------
张三 D | C | B | A 2009-04-05
赵大 D | R 2009-01-15
李四 H | F 2009-01-08(3 row(s) affected)*/
DROP TABLE TESTCREATE TABLE TEST(NAMES VARCHAR(20),BOOKS VARCHAR(20),DATES VARCHAR(20))
INSERT INTO TEST SELECT '张三', 'A', '2009-1-8'
UNION ALL SELECT'张三', 'B', '2009-3-2'
UNION ALL SELECT'张三', 'C', '2009-3-26'
UNION ALL SELECT'张三', 'D', '2009-4-5'
UNION ALL SELECT'李四', 'F', '2009-1-3'
UNION ALL SELECT'李四', 'H', '2009-1-8'
UNION ALL SELECT'赵大', 'R', '2008-12-26'
UNION ALL SELECT'赵大', 'D', '2009-1-15'
GOSELECT * FROM TESTDROP FUNCTION UNIONSTR
CREATE FUNCTION UNIONSTR(@STR VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @BOOK VARCHAR(100)
SET @BOOK = '';
SELECT @BOOK = @BOOK+'| '+BOOKS FROM TEST WHERE NAMES=@STR ORDER BY DATES DESC
RETURN SUBSTRING(@BOOK,3,LEN(@BOOK)-1)
ENDSELECT NAMES,DBO.UNIONSTR(NAMES)[BOOKS],CONVERT(NVARCHAR(10),MAX(DATES),120) AS DATES FROM TEST
GROUP BY NAMES
ORDER by MAX(DATES) DESC
NAMES BOOKS DATES
-------------------- -------------------- --------------------
张三 A 2009-1-8
张三 B 2009-3-2
张三 C 2009-3-26
张三 D 2009-4-5
李四 F 2009-1-3
李四 H 2009-1-8
赵大 R 2008-12-26
赵大 D 2009-1-15(所影响的行数为 8 行)
names books dates
-------------------- ---------------------------------------------------------------------------------------------------- ----------
张三 D| C| B| A 2009-4-5
李四 H| F 2009-1-8
赵大 D| R 2009-1-15(所影响的行数为 3 行)
if object_id('t') is not null drop table t --判断表T是否存在,若存在则无删除表T
create table t (names nvarchar(10),books nvarchar(10),dates datetime)insert into t
select '张三', 'A', '2009-1-8'
union all select'张三', 'B', '2009-3-2'
union all select'张三', 'C', '2009-3-26'
union all select'张三', 'D', '2009-4-5'
union all select'李四', 'F', '2009-1-3'
union all select'李四', 'H', '2009-1-8'
union all select'赵大', 'R', '2008-12-26'
union all select'赵大', 'D', '2009-1-15'
if object_id('booklist') is not null drop function booklist --意义同第二行
go
create function booklist(@names nvarchar(100))
returns nvarchar(20)
as
begin
declare @book nvarchar(20)
set @book = ''
select @book = @book + ' | '+ books from t where names=@names order by dates desc
return stuff(@book,1,3,'') --STUFF作用,删除指定长度的字符串并在指定的起始点插入另一组字符
endselect names,dbo.booklist(names) as books,convert(nvarchar(10),max(dates),120) as dates from t
group by names
order by max(dates) desc