0

Please login or register to vote for this query.

(click on this box to dismiss)

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

SELECT Count(ID) kount,
        CASE WHEN GROUPING(CloseCount) = 1 THEN 'ALL'
        ELSE  CAST(CloseCount as varchar(10))
        END [Open Count]
FROM (
  SELECT 
         ph.ID,
         COUNT(t.VALUE) CloseCount
         
         
    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) data
GROUP BY
      CloseCount WITH ROLLUP
ORDER BY 
CASE WHEN GROUPING(CloseCount) = 1 then 1 else 0 END ,
  CloseCount

fork query

edited mar 5 12

created mar 5 12 conradfrix