njmnjx
- 浏览: 232148 次
- 性别:
- 来自: 北京
-
文章分类
最新评论
-
njmnjx：
humors221 写道这篇文章还不错，不过有一个小问题，就是 ...
html判断页面加载完毕 -
donkey526485268：
httpModules应用 -
humors221：
这篇文章还不错，不过有一个小问题，就是setInterval( ...
html判断页面加载完毕 -
小昭WBCA：
[/ code]
jquery判断子元素是否存在
以下测试使用到的sql语句
http://dl.iteye.com/topics/download/21402853-8309-34bc-897f-ed201e75ad4b
一、首先创建两个表：省份表、城市表
城市表中包含所属省份的ID
省份表中包含所属大区的ID
二、查询
查询结果
进一步想要这样的结果？
这样即可
再进一步控制将列名转变为变量
再进一步将NULL值显示为0
结果如下：
以上结果的一个关键是使用pivot函数
语法：http://technet.microsoft.com/zh-cn/library/ms177410.aspx
注意事项：
一、以下为预期查询数据
二、*号位置使用，可以用<非透视列>，<非透视列>代替，但<非透视列>并不是…位置查询存在的列名而是…位置列中存在的列值。
sum(t.c)为最后被用作列值的统计;
t.ProvinceName被用作列名;
in ()被用作列名的…位置列值
http://dl.iteye.com/topics/download/21402853-8309-34bc-897f-ed201e75ad4b
一、首先创建两个表：省份表、城市表
城市表中包含所属省份的ID
省份表中包含所属大区的ID
/****** Object: Table [dbo].[City] Script Date: 06/25/2011 07:02:38 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[City]( [CityID] [int] IDENTITY(1,1) NOT NULL, [ProvinceID] [int] NOT NULL, [CityName] [nvarchar](50) NULL, CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED ( [CityID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET IDENTITY_INSERT [dbo].[City] ON INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (1, 1, N'北京') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (2, 2, N'天津') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (3, 3, N'石家庄') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (4, 3, N'唐山') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (5, 3, N'秦皇岛') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (6, 3, N'邯郸') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (7, 3, N'邢台') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (8, 3, N'保定') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (9, 3, N'张家口') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (10, 3, N'承德') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (11, 3, N'沧州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (12, 3, N'廊坊') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (13, 3, N'衡水') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (14, 4, N'太原') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (15, 4, N'大同') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (16, 4, N'阳泉') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (17, 4, N'长治') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (18, 4, N'晋城') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (19, 4, N'朔州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (20, 4, N'晋中') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (21, 4, N'临汾') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (22, 4, N'忻州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (23, 4, N'运城') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (24, 5, N'呼和浩特') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (25, 5, N'包头') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (26, 5, N'乌海') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (27, 5, N'赤峰') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (28, 5, N'通辽') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (29, 6, N'沈阳') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (30, 6, N'大连') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (31, 6, N'鞍山') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (32, 6, N'抚顺') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (33, 6, N'本溪') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (34, 6, N'丹东') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (35, 6, N'锦州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (36, 6, N'营口') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (37, 6, N'阜新') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (38, 6, N'辽阳') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (39, 6, N'盘锦') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (40, 6, N'铁岭') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (41, 6, N'朝阳') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (42, 6, N'葫芦岛') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (43, 7, N'长春') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (44, 7, N'吉林') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (45, 7, N'四平') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (46, 7, N'辽源') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (47, 7, N'通化') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (48, 7, N'白山') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (49, 7, N'白城') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (50, 7, N'松原') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (51, 8, N'哈尔滨') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (52, 8, N'齐齐哈尔') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (53, 8, N'鸡西') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (54, 8, N'鹤岗') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (55, 8, N'双鸭山') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (56, 8, N'大庆') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (57, 8, N'伊春') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (58, 8, N'佳木斯') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (59, 8, N'七台河') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (60, 8, N'牡丹江') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (61, 8, N'黑河') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (62, 8, N'绥化') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (63, 9, N'上海') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (64, 10, N'南京') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (65, 10, N'无锡') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (66, 10, N'徐州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (67, 10, N'常州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (68, 10, N'苏州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (69, 10, N'南通') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (70, 10, N'连云港') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (71, 10, N'淮阴') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (72, 10, N'盐城') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (73, 10, N'扬州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (74, 10, N'镇江') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (75, 10, N'泰州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (76, 10, N'宿迁') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (77, 11, N'杭州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (78, 11, N'宁波') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (79, 11, N'温州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (80, 11, N'嘉兴') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (81, 11, N'湖州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (82, 11, N'绍兴') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (83, 11, N'金华') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (84, 11, N'衢州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (85, 11, N'舟山') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (86, 11, N'台州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (87, 11, N'丽水') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (88, 12, N'合肥') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (89, 12, N'芜湖') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (90, 12, N'蚌埠') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (91, 12, N'淮南') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (92, 12, N'马鞍山') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (93, 12, N'淮北') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (94, 12, N'铜陵') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (95, 12, N'安庆') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (96, 12, N'黄山') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (97, 12, N'阜阳') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (98, 12, N'毫州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (99, 12, N'宿州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (100, 12, N'滁州') GO print 'Processed 100 total records' INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (101, 12, N'巢湖') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (102, 12, N'六安') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (103, 12, N'池州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (104, 12, N'宣城') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (105, 13, N'福州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (106, 13, N'厦门') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (107, 13, N'三明') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (108, 13, N'莆田') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (109, 13, N'泉州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (110, 13, N'漳州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (111, 13, N'南平') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (112, 13, N'龙岩') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (113, 13, N'宁德') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (114, 14, N'南昌') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (115, 14, N'景德镇') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (116, 14, N'萍乡') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (117, 14, N'九江') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (118, 14, N'新余') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (119, 14, N'鹰潭') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (120, 14, N'上饶') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (121, 14, N'赣州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (122, 14, N'吉安') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (123, 14, N'宜春') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (124, 15, N'济南') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (125, 15, N'青岛') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (126, 15, N'淄博') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (127, 15, N'枣庄') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (128, 15, N'东营') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (129, 15, N'潍方') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (130, 15, N'烟台') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (131, 15, N'威海') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (132, 15, N'济宁') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (133, 15, N'泰安') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (134, 15, N'日照') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (135, 15, N'莱芜') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (136, 15, N'德州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (137, 15, N'滨州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (138, 15, N'临沂') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (139, 15, N'荷泽') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (140, 15, N'聊城') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (141, 16, N'郑州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (142, 16, N'开封') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (143, 16, N'洛阳') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (144, 16, N'平顶山') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (145, 16, N'焦作') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (146, 16, N'鹤壁') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (147, 16, N'新乡') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (148, 16, N'安阳') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (149, 16, N'濮阳') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (150, 16, N'许昌') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (151, 16, N'漯河') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (152, 16, N'三门峡') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (153, 16, N'商丘') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (154, 16, N'周口') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (155, 16, N'驻马店') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (156, 16, N'信阳') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (157, 16, N'南阳') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (158, 17, N'武汉') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (159, 17, N'黄石') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (160, 17, N'襄樊') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (161, 17, N'十堰') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (162, 17, N'宜昌') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (163, 17, N'荆州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (164, 17, N'鄂州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (165, 17, N'孝感') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (166, 17, N'黄冈') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (167, 17, N'咸宁') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (168, 17, N'荆门') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (169, 17, N'随州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (170, 18, N'长沙') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (171, 18, N'株洲') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (172, 18, N'湘潭') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (173, 18, N'衡阳') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (174, 18, N'邵阳') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (175, 18, N'岳阳') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (176, 18, N'常德') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (177, 18, N'张家界') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (178, 18, N'娄底') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (179, 18, N'郴州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (180, 18, N'永州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (181, 18, N'怀化') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (182, 18, N'益阳') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (183, 19, N'广州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (184, 19, N'深圳') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (185, 19, N'珠海') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (186, 19, N'汕头') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (187, 19, N'韶关') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (188, 19, N'河源') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (189, 19, N'梅州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (190, 19, N'惠州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (191, 19, N'汕尾') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (192, 19, N'东莞') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (193, 19, N'中山') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (194, 19, N'江门') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (195, 19, N'佛山') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (196, 19, N'阳江') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (197, 19, N'湛江') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (198, 19, N'茂名') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (199, 19, N'肇庆') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (200, 19, N'清远') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (201, 19, N'潮州') GO print 'Processed 200 total records' INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (202, 19, N'揭阳') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (203, 19, N'云浮') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (204, 20, N'南宁') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (205, 20, N'桂林') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (206, 20, N'梧州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (207, 20, N'北海') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (208, 20, N'玉林') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (209, 20, N'柳州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (210, 20, N'防城港') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (211, 20, N'钦州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (212, 20, N'贵港') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (213, 21, N'海口') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (214, 21, N'三亚') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (215, 22, N'重庆') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (216, 23, N'成都') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (217, 23, N'自贡') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (218, 23, N'广安') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (219, 23, N'攀枝花') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (220, 23, N'泸州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (221, 23, N'德阳') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (222, 23, N'绵阳') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (223, 23, N'广元') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (224, 23, N'遂宁') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (225, 23, N'内江') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (226, 23, N'乐山') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (227, 23, N'宜宾') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (228, 23, N'南充') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (229, 23, N'资阳') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (230, 23, N'雅安') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (231, 23, N'巴中') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (232, 23, N'达州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (233, 24, N'贵阳') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (234, 24, N'六盘水') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (235, 24, N'遵义') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (236, 24, N'安顺') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (237, 25, N'昆明') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (238, 25, N'东川') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (239, 25, N'曲靖') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (240, 25, N'玉溪') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (241, 25, N'保山') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (242, 26, N'拉萨') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (243, 26, N'日喀则') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (244, 27, N'西安') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (245, 27, N'铜川') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (246, 27, N'宝鸡') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (247, 27, N'咸阳') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (248, 27, N'延安') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (249, 27, N'渭南') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (250, 27, N'安康') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (251, 27, N'榆林') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (252, 27, N'汉中') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (253, 28, N'兰州') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (254, 28, N'金昌') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (255, 28, N'白银') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (256, 28, N'天水') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (257, 28, N'嘉峪关') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (258, 29, N'西宁') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (259, 30, N'银川') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (260, 30, N'石嘴山') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (261, 30, N'吴忠') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (262, 31, N'乌鲁木齐') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (263, 31, N'克拉玛依') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (264, 32, N'台北') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (265, 33, N'香港') INSERT [dbo].[City] ([CityID], [ProvinceID], [CityName]) VALUES (266, 34, N'澳门') SET IDENTITY_INSERT [dbo].[City] OFF /****** Object: Table [dbo].[Province] Script Date: 06/25/2011 07:02:38 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Province]( [ProvinceID] [int] IDENTITY(1,1) NOT NULL, [ProvinceName] [nvarchar](50) NULL, [RegionID] [int] NULL, CONSTRAINT [PK_Province] PRIMARY KEY CLUSTERED ( [ProvinceID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'该省属于哪个区' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Province', @level2type=N'COLUMN',@level2name=N'RegionID' GO SET IDENTITY_INSERT [dbo].[Province] ON INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (1, N'北京', 1) INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (2, N'天津', 1) INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (3, N'河北', 1) INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (4, N'山西', 1) INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (5, N'内蒙古', 1) INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (6, N'辽宁', 2) INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (7, N'吉林', 2) INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (8, N'黑龙江', 2) INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (9, N'上海', 3) INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (10, N'江苏', 3) INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (11, N'浙江', 3) INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (12, N'安徽', 3) INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (13, N'福建', 3) INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (14, N'江西', 3) INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (15, N'山东', 3) INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (16, N'河南', 4) INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (17, N'湖北', 4) INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (18, N'湖南', 4) INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (19, N'广东', 4) INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (20, N'广西', 4) INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (21, N'海南', 4) INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (22, N'重庆', 5) INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (23, N'四川', 5) INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (24, N'贵州', 5) INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (25, N'云南', 5) INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (26, N'西藏', 5) INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (27, N'陕西', 6) INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (28, N'甘肃', 6) INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (29, N'青海', 6) INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (30, N'宁夏', 6) INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (31, N'新疆', 6) INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (32, N'台湾', 7) INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (33, N'香港', 7) INSERT [dbo].[Province] ([ProvinceID], [ProvinceName], [RegionID]) VALUES (34, N'澳门', 7) SET IDENTITY_INSERT [dbo].[Province] OFF
二、查询
select RegionID,p.ProvinceID,ProvinceName,COUNT(p.ProvinceID) c from City as c join Province as p on c.ProvinceID = p.ProvinceID group by p.ProvinceID,ProvinceName,RegionID
查询结果
进一步想要这样的结果？
这样即可
select * from ( select RegionID,p.ProvinceID,ProvinceName,COUNT(p.ProvinceID) c from City as c join Province as p on c.ProvinceID = p.ProvinceID group by p.ProvinceID,ProvinceName,RegionID ) as t pivot (sum(t.c) for t.ProvinceName in ([北京],[天津],[河北],[山西],[内蒙古],[辽宁],[吉林],[黑龙江],[上海],[江苏],[浙江],[安徽],[福建],[江西],[山东] ,[河南],[湖北],[湖南],[广东],[广西],[海南],[重庆],[四川],[贵州],[云南],[西藏],[陕西],[甘肃],[青海],[宁夏],[新疆],[台湾],[香港],[澳门]) ) as p
再进一步控制将列名转变为变量
declare @provinceStr nvarchar(1000)
set @provinceStr = '[北京],[天津],[河北],[山西],[内蒙古],[辽宁],[吉林],[黑龙江],[上海],[江苏],[浙江],[安徽],[福建],[江西],[山东]
,[河南],[湖北],[湖南],[广东],[广西],[海南],[重庆],[四川],[贵州],[云南],[西藏],[陕西],[甘肃],[青海],[宁夏],[新疆],[台湾],[香港],[澳门]'
declare @sql nvarchar(max)
set @sql = '
select * from
(
select RegionID,p.ProvinceID,ProvinceName,COUNT(p.ProvinceID) c from City as c
join Province as p on c.ProvinceID = p.ProvinceID group by p.ProvinceID,ProvinceName,RegionID
) as t
pivot (sum(t.c) for t.ProvinceName in
({0})
) as p'
SET @sql= REPLACE(@sql,'{0}',@provinceStr)
exec sp_executesql @sql
再进一步将NULL值显示为0
declare @provinceStr nvarchar(1000)
set @provinceStr = '[北京],[天津],[河北],[山西],[内蒙古],[辽宁],[吉林],[黑龙江],[上海],[江苏],[浙江],[安徽],[福建],[江西],[山东]
,[河南],[湖北],[湖南],[广东],[广西],[海南],[重庆],[四川],[贵州],[云南],[西藏],[陕西],[甘肃],[青海],[宁夏],[新疆],[台湾],[香港],[澳门]';
declare @byname nvarchar(2000)
set @byname = 'coalesce(北京,0) as 北京,coalesce(天津,0) as 天津,coalesce(河北,0) as 河北,coalesce(山西,0) as 山西,coalesce(内蒙古,0) as 内蒙古,coalesce(辽宁,0) as 辽宁'
declare @sql nvarchar(max)
set @sql = '
select RegionID,ProvinceID,{1} from
(
select RegionID,p.ProvinceID,ProvinceName,COUNT(p.ProvinceID) c from City as c
join Province as p on c.ProvinceID = p.ProvinceID group by p.ProvinceID,ProvinceName,RegionID
) as t
pivot (sum(t.c) for t.ProvinceName in
({0})
) as p'
SET @sql= REPLACE(@sql,'{0}',@provinceStr)
SET @sql= REPLACE(@sql,'{1}',@byname)
exec sp_executesql @sql
结果如下：
以上结果的一个关键是使用pivot函数
语法：http://technet.microsoft.com/zh-cn/library/ms177410.aspx
注意事项：
一、以下为预期查询数据
select RegionID,p.ProvinceID,ProvinceName,COUNT(p.ProvinceID) c from City as c join Province as p on c.ProvinceID = p.ProvinceID group by p.ProvinceID,ProvinceName,RegionID
二、*号位置使用，可以用<非透视列>，<非透视列>代替，但<非透视列>并不是…位置查询存在的列名而是…位置列中存在的列值。
sum(t.c)为最后被用作列值的统计;
t.ProvinceName被用作列名;
in ()被用作列名的…位置列值
select * from
(
…
) as t
pivot (sum(t.c) for t.ProvinceName in ({0}) ) as p
分享到：
发表评论
-
链接服务器-分布式事务图解
2011-06-26 19:24 4611一、环境说明： 挑选两台不同系统的电脑测试（XP、Win7、S ... -
Sql sa无法登陆
2010-10-27 20:31 1535在SQL SERVER 安装的时候,如果选择的是WINDOWS ... -
使用自连接、for xml path('')和stuff合并显示多行数据到一行中
2010-10-27 20:22 8818--使用 自连接、for xml path('')和stu ... -
自动执行存储过程
2010-09-22 20:51 1029利用sql作业自动执行存储过程步骤如下（sql2005为例）： ... -
sql server 导出数据 表自增列 问题处理
2010-06-19 17:05 3616当数据库表中有自增列时，由一个数据库向另一个数据库导出数据时会 ... -
存储过程中使用游标
2010-04-28 17:49 1347以下例子完整的表述了一个存储过程中使用游标、变量及整个 ...
相关推荐
杰笛数据透视表格 Pivot Grid 杰笛数据透视表格利用Java/Swing实现了在Microsoft Excel里面非常著名的数据透视表（PivotTable）功能。这个功能也在很多数据分析和OLAP应用程序里出现过。数据透视表的特点就是帮助您...
vue-pivot-table数据透视表Live演示（jsfiddle）的vue组件安装npm install --save @ marketconnect / vue-pivot-table组件此项目vue-pivot-table数据透视表Live演示（jsfiddle）的vue组件npm install --save @ ...
PowerPivot 作为 Excel2013 商务智能标准插件，...读完本书您会了解， PowerPivot 已经在各方面完全超越了传统 Excel 数据透视表，它可以对数据源任意子集进行 各种比较和计算，让你的数据的分析变得更加灵活和方便。
微软excel2013用powerpivot建立数据模型-【配套内容示例文件】 Microsoft Excel 2013 Building Data Models with PowerPivot] 注意！只有示例文件，没有电子书！ 内含一个access数据库文件；1-16章Excel的.xlsx配套...
Pivot和Pivot_table函数都是对数据做透视表而使用的。其中的区别在于Pivot_table可以支持重复元素的聚合操作，而Pivot函数只能对不重复的元素进行聚合操作。 在一般的日常业务中，因为Pivot_table的功能更为强大,...
pivot_table：Matlab 的数据透视表介绍 此函数计算数据透视表（类似于在 Excel、R 或 Pandas (python) 中从 matlab 表中创建的数据透视表。换句话说，in 能够通过将大型数据集聚合到组中，然后应用函数来对其进行...
用类模块来封装Excel数据透视表生成的具体实现细节，提供一个用户界面收集报表的命名、存放位置等参数，设计一个自定义结构类型的变量来实现参数的传递，并将其封装成组件.因为项目引用了Office Excel COM组件，...
在数据分析中，常常需要用到数据透视表和交叉表，下面介绍pandas.DataFrame.pivot_table数据透视表和pandas.DataFrame.crosstab交叉表的用 法和区别。 4.9.1 数据透视表 典型的数据格式是扁平的，只包含行和列，不...
react-pivottable是基于React的数据透视表库，具有拖放功能。 它是同一作者基于jQuery的的React端口。 react-pivottable是Plotly的一部分，用于构建数据可视化Web应用程序和产品。 它有什么作用？演示在哪里？ ...
零基础到高级应用系统化讲解Power Pivot课程,涉及常规透视表与数据模型透视表的区别,导入数据等基础应用,以及更多DAX基础语法及拓展高级应用,设置度量值,理解查询上下文,筛选上下文,行上下文之间的关系。
也许大多数人都有在Excel中使用数据透视表的经历，其实Pandas也提供了一个类似的功能，名为pivot_table。虽然pivot_table非常有用，但是我发现为了格式化输出我所需要的内容，经常需要记住它的使用语法。所以，本文...
零基础到高级应用系统化讲解Power Pivot课程,涉及常规透视表与数据模型透视表的区别,导入数据等基础应用,以及更多DAX基础语法及拓展高级应用,设置度量值,理解查询上下文,筛选上下文,行上下文之间的关系。
数据透视资料。。数据透视图资料。数据组合方法。。
数据透视表数据透视表提供了一种简单的方法来汇总一组结构化 SQL 表数据。执照此数据透视表功能是在许可下的开源软件。
英文 |数据透视图 数据透视图是数据透视表的扩展数据可视化类型。 它允许用户观察不同图表类型的数据，而不仅限于表格和纯数字。 数据透视图还提供了基本的数据透视表组件供您构建 Web 应用程序，您可以将数据透视表...
本书内容丰富，创建与分析数据透视表、在商务领域中的应用、数据透视图、PowerPivot、输出与打印、快捷键等秘技尽在本书。本书查阅方便，属于便携式的辞典类工具书，按应用功能分类，方便读者随时随地学习和查阅。
react-virtualized-pivot是在和之上构建的React.js透视UI。 演示版 该演示使用提供的2007-2012年拒绝贷款数据中约24.7mb的未压缩.csv，269,372行乘9列（2,424,348个像元）。 入门 使用npm安装react-virtualized-...
1、excel中做数据透视表 ...2、pandas中使用pivot_table()做数据透视表 1）语法格式 pd.pivot_table(data,index=None,columns=None, values=None,aggfunc='mean', margins=False,margins_name='All',