sql語句面試題（城市人口統計）

問題描述:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

 

城市表
【城市】
北京
上海
天津

人口表：
【性別】【城市】 【人口】
男 浙江 100
女 北京 500
男 上海 120
女 深圳 50
女 浙江 52
男 北京 45
女 上海 180
男 深圳 254
未知 上海 20

希望查詢結果：
【性別】【北京】 【上海】 【天津】 【其他】
全部 545 320 0 456
男 45 120 0 354
女 500 180 0 102
未知 0 20 0 0

說明：其他包括北京、上海、天津以外的所有城市

 

 

--> liangCK小樑 於2008-10-06

--> 生成測試資料: #T

IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T

CREATE TABLE #T (城市 NVARCHAR(4))

INSERT INTO #T

SELECT '北京' UNION ALL

SELECT '上海' UNION ALL

SELECT '天津'

--> liangCK小樑 於2008-10-06

--> 生成測試資料: #人口表:

IF OBJECT_ID('tempdb.dbo.#人口表') IS NOT NULL DROP TABLE #人口表

CREATE TABLE #人口表 (性別 NVARCHAR(4),城市 NVARCHAR(4),人口 INT)

INSERT INTO #人口表

SELECT '男','浙江','100' UNION ALL

SELECT '女','北京','500' UNION ALL

SELECT '男','上海','120' UNION ALL

SELECT '女','深圳','50' UNION ALL

SELECT '女','浙江','52' UNION ALL

SELECT '男','北京','45' UNION ALL

SELECT '女','上海','180' UNION ALL

SELECT '男','深圳','254' UNION ALL

SELECT '未知','上海','20'

 

 

 

--SQL查詢如下:

 

DECLARE @SQL VARCHAR(8000),@city VARCHAR(1000)

SET @SQL=''

SET @city=''

 

SELECT @SQL=@SQL+',SUM(CASE WHEN a.城市='''+城市+''' then b.人口 else 0 end) ['+城市+']',

       @city=@city+','''+城市+''''

FROM #T

 

SET @city=STUFF(@city,1,1,'')

 

EXEC ('SELECT ISNULL(c.性別,''全部'') 性別'+@SQL+',SUM(CASE WHEN b.城市 NOT IN('+@city+') THEN b.人口 else 0 end) [其它]

       FROM #T a RIGHT JOIN #人口表 b on a.城市=b.城市

            RIGHT JOIN

             (

                SELECT 性別=''男''

                UNION ALL

                SELECT 性別=''女''

                UNION ALL

                SELECT 性別=''未知''

             ) AS c

          ON b.性別=c.性別

       GROUP BY c.性別

       WITH ROLLUP

       ORDER BY CASE WHEN c.性別=''男'' THEN 0

                     WHEN c.性別=''女'' THEN 1

                     ELSE 2 END ')

 

/*

性別   北京          上海          天津          其它

---- ----------- ----------- ----------- -----------

男    45          120         0           354

女    500         180         0           102

未知   0           20          0           0

全部   545         320         0           456

 

(4 行受影響)

 

*/

 

問題網址:http://topic.csdn.net/u/20081006/16/cedf3352-844e-45c8-b6f3-39508ba08e72.html