SQL中的循环行

一尘不染

SQL中的循环行

sql

我正在寻找类似的输出：

[最近]日期：评论，[第二最近]日期：评论，…

例子：

Book    Comments
BookA   27/03/13: comment1, 21/03/13: comment2, 21/03/13: uhuuuu [and so on]
BookB   21/03/13: comment2a, 18/03/13: xxx comments

SQL小提琴

MS SQL Server 2012架构设置 ：

CREATE TABLE books 
    (
     book varchar(10), 
     comments varchar(20),
     datewww datetime

    );

INSERT INTO books
(book, comments, datewww)
VALUES
('BookA', 'comment1', '2013-03-27 10:30:00.000'),
('BookA', 'comment2', '2013-03-21 09:31:00.000'),
('BookA', 'comentx', '2013-03-10 08:31:00.000'),
('BookA', 'Text test', '2013-02-15 07:41:00.000'),
('BookA', 'uhuuuu', '2013-03-21 07:31:00.000'),
('BookB', 'comment2a', '2013-03-21 09:31:00.000'),
('BookB', 'xxx comments', '2013-03-18 09:31:00.000');

查询1 ：

SELECT
  book,
  CONVERT(VARCHAR, datewww, 3) + ': ' + comments + ', '
FROM books

结果：

|  BOOK |                 COLUMN_1 |
|-------|--------------------------|
| BookA |     27/03/13: comment1,  |
| BookA |     21/03/13: comment2,  |
| BookA |      10/03/13: comentx,  |
| BookA |    15/02/13: Text test,  |
| BookA |       21/03/13: uhuuuu,  |
| BookB |    21/03/13: comment2a,  |
| BookB | 18/03/13: xxx comments,  |

阅读 135

2021-05-30

共1个答案

一尘不染

SQL小提琴

SELECT book,
       Stuff((SELECT ', ' + ltrim(rtrim(CONVERT(VARCHAR, datewww, 3) + ': ' + comments))
              FROM   books t2 
              WHERE  t2.book = t1.book
              order by datewww desc
               FOR XML PATH(''), TYPE
                   ).value('.','varchar(max)')
                   ,1,2, '') [comments] 
FROM   books t1 
GROUP  BY book

结果：

|  BOOK |                                                                                          comments |
|-------|--------------------------------------------------------------------------------------------------|
| BookA | 27/03/13: comment1, 21/03/13: comment2, 21/03/13: uhuuuu, 10/03/13: comentx, 15/02/13: Text test |
| BookB | 21/03/13: comment2a, 18/03/13: xxx comments |

2021-05-30