0

Please login or register to vote for this query.

(click on this box to dismiss)

プログラマーとプログラミングに熱心な人々のための質問と回答

SELECT postid 
FROM   posthistory 
WHERE  posthistoryTypeID = 10 
       AND id IN (SELECT ph.id
                      FROM   posthistory ph 
                             OUTER APPLY (SELECT Substring(',' + TEXT + ',', 
                                                 NUMBER + 1 
                                                 , 
                                                 Charindex(',', 
                                                 ',' + TEXT + ',', NUMBER 
                                                         + 
                                                         1) - NUMBER - 1) 
                                                                        AS VALUE 
                                          FROM   MASTER..spt_values 
                                          WHERE  TYPE = 'P' 
                                                 AND NUMBER <= Len( 
                                                     ',' + TEXT + ',') 
                                                               - 1 
                                                 AND Substring(',' + TEXT + ',', 
                                                     NUMBER 
                                                     , 1) 
                                                     = ',' 
                                                 AND ph.posthistorytypeid = 10) 
                                         t 
                      WHERE  ph.posthistorytypeid = 10 
                             AND ( t.VALUE LIKE '"DisplayName":%' 
                                    OR t.VALUE IS NULL ) 
                      GROUP  BY ph.id 
                      HAVING COUNT(t.VALUE) = 5)

view current version

edited mar 5 12

created mar 5 12 conradfrix