|
|
我正在寻找类似的输出：6 R' Z6 C( C* m& h. \# N m2 |0 ]
[最近]日期：评论，[第二最近]日期：评论，…2 U+ |3 y" V+ G
例子：
/ n/ @ c# c; J1 \7 y5 J+ dBook CommentsBookA comment1,21/03/13: comment2,21/03/13: uhuuuu [and so on]BookB comment2a,18/03/13: xxx commentsSQL小提琴6 Q$ U0 J. N4 G( w
MS SQL Server 2012架构设置 ：
+ @% x: z- |$ y, L N6 d0 k4 CCREATE 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 ：5 r5 I6 |" |$ h9 g" S
SELECT book, CONVERT(VARCHAR,datewww,3) comments FROM books结果 ：- N- x5 u3 `3 T( E; n
| BOOK | COLUMN_1 ||-------|--------------------------|| BookA | comment1, || BookA | comment2, || BookA | comentx, || BookA | Text test, || BookA | uhuuuu, || BookB | comment2a, || BookB | 18/03/13: xxx comments, | / H) \$ o+ c- K. F0 y, k$ }+ C
解决方案: ! }; ~7 P% a' f# f9 I0 H3 t
SQL小提琴
6 L4 O& l8 @, y5 V$ ?. PSELECT 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) [comments] FROM books t1 GROUP BY book结果 ：3 M: c% W3 e; W+ Q4 z [( w6 |7 X: j
| 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 | |
|
技术问答
178 人阅读
|
0 人回复
|
2023-09-12
