---

http://www.dotblogs.com.tw/phoenix7765/archive/2008/11/14/5987.aspx


SELECT
                           asset_category AS 資產類別, status as 狀態,  
                           COUNT(CASE WHEN check_status='待盤點' THEN 1 ELSE NULL END) AS 待盤點,
                           COUNT(CASE WHEN check_status='已盤點' THEN 1 ELSE NULL END) AS 已盤點,
                           COUNT(CASE WHEN check_status='盤盈' THEN 1 ELSE NULL END) AS 盤盈,
                           COUNT(CASE WHEN check_status='盤差' THEN 1 ELSE NULL END) AS 盤差,
                           COUNT(*) AS 總計
                     FROM
                           vw_check
                     WHERE check_no = @check_no

     group by status,asset_category


Union all

SELECT
                           '總  計' AS 資產類別, '' as 狀態,  
                           COUNT(CASE WHEN check_status='待盤點' THEN 1 ELSE NULL END) AS 待盤點,
                           COUNT(CASE WHEN check_status='已盤點' THEN 1 ELSE NULL END) AS 已盤點,
                           COUNT(CASE WHEN check_status='盤盈' THEN 1 ELSE NULL END) AS 盤盈,
                           COUNT(CASE WHEN check_status='盤差' THEN 1 ELSE NULL END) AS 盤差,
                           COUNT(*) AS 總計
                     FROM
                           vw_check
                     WHERE check_no = @check_no

     order by asset_category

--
※ 作者: book11 時間: 2012-01-30 13:27:56