表A:
列名:id name parentid 1 area 0
2 category 0
3 north 1
4 south 1
5 Shanghai 4
6 Beijing 3
7 pudong 5
8 xuhui 5
9 chaoyang 6
10 desk 2
11 chair 2
12 bed 2表B: 存放区域信息 ,很多字段用的是表A 的id
列名:id area city district address users
1 4 5 pudong CN,SH 33
2 4 5 xuhui CN,SH 44
3 4 6 chaoyang CN,BJ 67表C:存放设备信息,很多字段用到表A
列名:id category area city district cost
1 10 4 5 7
2 10 4 5 7
3 11 4 5 8
4 11 3 6 9
5 10 3 6 9现在要求统计出B表里每个区域(district)的信息,包括address,users 以及每种设备(category)的库存量。完成后结果如下:
Area city district address 桌子数量 椅子数量 床数量
North beijing chaoyang CN,BJ ? ? ?
South shanghai pudong CN,SH ? ? ?
South shanghai xuhui CN,SH ? ? ?请各位指点一下该怎么写啊,高分奉送!
1 10 4 5 7
2 10 4 5 7
3 11 4 5 8
4 11 3 6 9
5 10 3 6 9
列怎么对应
是啊,是给我写过,但后来发现不对啊,不是针对B表写的。
他是这么写的
select a0.name area,
a1.name city,
a2.name district,
'' address,
MAX(case a3.name when 'desk' then 数量 else 0 end) 桌子数量,
MAX(case a3.name when 'chair' then 数量 else 0 end) 椅子数量,
MAX(case a3.name when 'bed' then 数量 else 0 end) 床数量
from (
select category,area,city,[district],COUNT(1) 数量
from c
group by category,area,city,[district]
) c
join a a0 on a0.id=c.area
join a a1 on a1.id=c.[city]
join a a2 on a2.id=c.district
join a a3 on a3.id=c.category
left join b on b.area=c.area and c.city=b.city
group by a0.name,a1.name,a2.name
-- Author : htl258(Tony)
-- Date : 2010-04-26 20:42:12
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
--------------------------------------------------------------------------
--> 生成测试数据表:aIF NOT OBJECT_ID('[a]') IS NULL
DROP TABLE [a]
GO
CREATE TABLE [a]([id] INT,[name] NVARCHAR(10),[parentid] INT)
INSERT [a]
SELECT 1,'area',0 UNION ALL
SELECT 2,'category',0 UNION ALL
SELECT 3,'north',1 UNION ALL
SELECT 4,'south',1 UNION ALL
SELECT 5,'Shanghai',4 UNION ALL
SELECT 6,'Beijing',3 UNION ALL
SELECT 7,'pudong',5 UNION ALL
SELECT 8,'xuhui',5 UNION ALL
SELECT 9,'chaoyang',6 UNION ALL
SELECT 10,'desk',2 UNION ALL
SELECT 11,'chair',2 UNION ALL
SELECT 12,'bed',2
GO
--SELECT * FROM [a]--> 生成测试数据表:bIF NOT OBJECT_ID('[b]') IS NULL
DROP TABLE [b]
GO
CREATE TABLE [b]([id] INT,[area] INT,[city] INT,[district] NVARCHAR(10),[address] NVARCHAR(10),[users] INT)
INSERT [b]
SELECT 1,4,5,'pudong',N'CN,SH',33 UNION ALL
SELECT 2,4,5,'xuhui',N'CN,SH',44 UNION ALL
SELECT 3,3,6,'chaoyang',N'CN,BJ',67
GO
--SELECT * FROM [b]--> 生成测试数据表:cIF NOT OBJECT_ID('[c]') IS NULL
DROP TABLE [c]
GO
CREATE TABLE [c]([id] INT,[category] INT,[area] INT,[city] INT,[district] INT)
INSERT [c]
SELECT 1,10,4,5,7 UNION ALL
SELECT 2,10,4,5,7 UNION ALL
SELECT 3,11,4,5,8 UNION ALL
SELECT 4,11,3,6,9 UNION ALL
SELECT 5,10,3,6,9
GO
--SELECT * FROM [c]-->SQL查询如下:
select a0.name area,
a1.name city,
a2.name district,
address=STUFF((select distinct ','+address from b where b.area=c.area and c.city=b.city for xml path('')),1,1,''),
users=STUFF((select distinct ','+ltrim(users) from b where b.area=c.area and c.city=b.city for xml path('')),1,1,''),
桌子数量,
椅子数量,
床数量
from (
select area,city,[district],
SUM(case a.name when 'desk' then 1 else 0 end) 桌子数量,
SUM(case a.name when 'chair' then 1 else 0 end) 椅子数量,
SUM(case a.name when 'bed' then 1 else 0 end) 床数量
from c,a
where c.category=a.id
group by area,city,[district]
) c
join a a0 on a0.id=c.area
join a a1 on a1.id=c.[city]
join a a2 on a2.id=c.district
order by area,city
/*
area city district address users 桌子数量 椅子数量 床数量
north Beijing chaoyang CN,BJ 67 1 1 0
south Shanghai pudong CN,SH 33,44 2 0 0
south Shanghai xuhui CN,SH 33,44 0 1 0
*/这样不知道是不是你要的结果
SELECT 3,4,6,'chaoyang',N'CN,BJ',67这里面的4改为3了。你再改回去试试
另外,我知道B表里有几个字段是没有用的,但要求这么写的,不过ADDRESS和USERS还是有用的。而且统计的时候,USERS不应该出现"33,44"这样的结果的。
表A:
列名:id name parentid 1 area 0
2 category 0
3 north 1
4 south 1
5 Shanghai 4
6 Beijing 3
7 pudong 5
8 xuhui 5
9 chaoyang 6
10 desk 2
11 chair 2
12 bed 2
13 haidian 6表B: 存放区域信息 ,很多字段用的是表A 的id
列名:id area city district address users
1 4 5 pudong CN,SH 33
2 4 5 xuhui CN,SH 44
3 4 6 chaoyang CN,BJ 67
4 4 6 haidian CN,BJ 25
表C:存放设备信息,很多字段用到表A
列名:id category area city district cost
1 10 4 5 7
2 10 4 5 7
3 11 4 5 8
4 11 3 6 9
5 10 3 6 9现在要求统计出B表里每个区域(district)的信息,包括address,users 以及每种设备(category)的库存量。完成后结果如下:
Area city district address 桌子数量 椅子数量 床数量
North beijing chaoyang CN,BJ 1 1 0
South shanghai pudong CN,SH 2 0 0
South shanghai xuhui CN,SH 0 1 0
North beijing haidian cn,BJ 0 0 0
-- Author : htl258(Tony)
-- Date : 2010-04-26 20:42:12
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
--------------------------------------------------------------------------
--> 生成测试数据表:aIF NOT OBJECT_ID('[a]') IS NULL
DROP TABLE [a]
GO
CREATE TABLE [a]([id] INT,[name] NVARCHAR(10),[parentid] INT)
INSERT [a]
SELECT 1,'area',0 UNION ALL
SELECT 2,'category',0 UNION ALL
SELECT 3,'north',1 UNION ALL
SELECT 4,'south',1 UNION ALL
SELECT 5,'Shanghai',4 UNION ALL
SELECT 6,'Beijing',3 UNION ALL
SELECT 7,'pudong',5 UNION ALL
SELECT 8,'xuhui',5 UNION ALL
SELECT 9,'chaoyang',6 UNION ALL
SELECT 10,'desk',2 UNION ALL
SELECT 11,'chair',2 UNION ALL
SELECT 12,'bed',2 UNION ALL
SELECT 13,'haidian',6
GO
--SELECT * FROM [a]--> 生成测试数据表:bIF NOT OBJECT_ID('[b]') IS NULL
DROP TABLE [b]
GO
CREATE TABLE [b]([id] INT,[area] INT,[city] INT,[district] NVARCHAR(10),[address] NVARCHAR(10),[users] INT)
INSERT [b]
SELECT 1,4,5,'pudong',N'CN,SH',33 UNION ALL
SELECT 2,4,5,'xuhui',N'CN,SH',44 UNION ALL
SELECT 3,3,6,'chaoyang',N'CN,BJ',67 UNION ALL
SELECT 4,4,6,'haidian','CN,BJ',25GO
--SELECT * FROM [b]--> 生成测试数据表:cIF NOT OBJECT_ID('[c]') IS NULL
DROP TABLE [c]
GO
CREATE TABLE [c]([id] INT,[category] INT,[area] INT,[city] INT,[district] INT)
INSERT [c]
SELECT 1,10,4,5,7 UNION ALL
SELECT 2,10,4,5,7 UNION ALL
SELECT 3,11,4,5,8 UNION ALL
SELECT 4,11,3,6,9 UNION ALL
SELECT 5,10,3,6,9
GO
--SELECT * FROM [c]-->SQL查询如下:
select a0.name area,
a1.name city,
b.district,
address,
users,
isnull(桌子数量,0) 桌子数量,
isnull(椅子数量,0) 椅子数量,
isnull(床数量,0) 床数量
from b
join a on a.name=b.district
join a a0 on a0.id=b.area
join a a1 on a1.id=b.[city]
left join(
select area,city,[district],
SUM(case a.name when 'desk' then 1 else 0 end) 桌子数量,
SUM(case a.name when 'chair' then 1 else 0 end) 椅子数量,
SUM(case a.name when 'bed' then 1 else 0 end) 床数量
from c,a
where c.category=a.id
group by area,city,[district]
) c
on b.area=c.area and b.city=c.city and c.district=a.id
order by area,city
/*
area city district address users 桌子数量 椅子数量 床数量
---------- ---------- ---------- ---------- ----------- ----------- ----------- -----------
north Beijing chaoyang CN,BJ 67 1 1 0
south Beijing haidian CN,BJ 25 0 0 0
south Shanghai pudong CN,SH 33 2 0 0
south Shanghai xuhui CN,SH 44 0 1 0(4 行受影响)
*/