高分求教一个三表联合查询语句。

Question

表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
        1     4       5      pudong        
        2     4       5      xuhui  
        3     4       6      chaoyang表C：存放设备信息，很多字段用到表A
列名： id     category    area    city   district   cost  weight
       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现在要求统计出每个区域每种设备的库存量。完成后结果如下：
North :
        city        district    address     桌子数量      椅子数量     床数量
        beijing      chaoyang                  ?            ?             ?
South:
        shanghai     pudong                    ?             ?            ?
        shanghai     xuhui                     ?             ?            ?请各位指点一下该怎么写啊，高分奉送！

Answer 1

表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
      1     4       5      pudong        
      2     4       5      xuhui  
      3     4       6      chaoyang表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现在要求统计出每个区域每种设备的库存量。完成后结果如下：
       city    district   address   桌子数量   椅子数量   床数量
  
North :
       beijing   chaoyang               ?        ?       ？
South:
      shanghai    pudong                ？        ?       ?
      shanghai     xuhui                ?         ?      ?请各位指点一下该怎么写啊，高分奉送！

Answer 2

108860028 NET开发的群,期待你们的加入!

Answer 3

--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-22 21:46:56
--  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)
--------------------------------------------------------------------------
--> 生成测试数据表: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--> 生成测试数据表:bIF NOT OBJECT_ID('[b]') IS NULL
DROP TABLE [b]
GO
CREATE TABLE [b]([id] INT IDENTITY,[area] INT,[city] INT,[district] NVARCHAR(10),[address] NVARCHAR(10))
INSERT [b]
SELECT 1,4,5,'pudong' UNION ALL
SELECT 2,4,5,'xuhui' UNION ALL
SELECT 3,4,6,'chaoyang'
GO--> 生成测试数据表:cIF NOT OBJECT_ID('[c]') IS NULL
DROP TABLE [c]
GO
CREATE TABLE [c]([id] INT IDENTITY,[category] INT,[area] INT,[city] INT,[district] INT,[cost] 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 [a]
--SELECT * FROM [b]
--SELECT * FROM [c]-->SQL查询如下:
select a1.name city,
a2.name district,
b.address,
SUM(case a3.name when 'desk' then 1 else 0 end) 桌子数量,
SUM(case a3.name when 'chair' then 1 else 0 end) 椅子数量,
SUM(case a3.name when 'bed' then 1 else 0 end) 床数量
from c 
join b on c.city=b.city 
left join a a1 on a1.id=b.city
left join a a2 on a2.id=b.district
left join a a3 on a3.id=c.area
group by a1.name,a2.name,b.address
/*
city       district   address    桌子数量        椅子数量        床数量
---------- ---------- ---------- ----------- ----------- -----------
south      Beijing    chaoyang   2           1           0
south      Shanghai   pudong     2           1           0
south      Shanghai   xuhui      2           1           0(3 行受影响)
*/
select a1.name city,
a2.name district,
b.address,
SUM(case a3.name when 'desk' then cost else 0 end) 桌子数量,
SUM(case a3.name when 'chair' then cost else 0 end) 椅子数量,
SUM(case a3.name when 'bed' then cost else 0 end) 床数量
from c 
join b on c.city=b.city 
left join a a1 on a1.id=b.city
left join a a2 on a2.id=b.district
left join a a3 on a3.id=c.area
group by a1.name,a2.name,b.address
/*
city       district   address    桌子数量        椅子数量        床数量
---------- ---------- ---------- ----------- ----------- -----------
south      Beijing    chaoyang   14          8           0
south      Shanghai   pudong     14          8           0
south      Shanghai   xuhui      14          8           0(3 行受影响)
*/两个结果不知有没有一个是你要的

Answer 4

A表是个地址的BOM，这样好象不行吧

Answer 5

谢谢你这么热心，不过好像表结构错了B表和C表多生成了一行ID，给了第二列了，

Answer 6

--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-22 21:46:56
--  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)
--------------------------------------------------------------------------
--> 生成测试数据表: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--> 生成测试数据表:bIF NOT OBJECT_ID('[b]') IS NULL
DROP TABLE [b]
GO
CREATE TABLE [b]([id] INT IDENTITY,[area] INT,[city] INT,[district] NVARCHAR(10),[address] NVARCHAR(10))
INSERT [b]
SELECT 1,4,5,'pudong' UNION ALL
SELECT 2,4,5,'xuhui' UNION ALL
SELECT 3,4,6,'chaoyang'
GO--> 生成测试数据表:cIF NOT OBJECT_ID('[c]') IS NULL
DROP TABLE [c]
GO
CREATE TABLE [c]([id] INT IDENTITY,[category] INT,[area] INT,[city] INT,[district] INT,[cost] 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 [a]
SELECT * FROM [b]
SELECT * FROM [c]-->SQL查询如下:
select a1.name city,
a2.name district,
b.address,
SUM(case a3.name when 'desk' then 1 else 0 end) 桌子数量,
SUM(case a3.name when 'chair' then 1 else 0 end) 椅子数量,
SUM(case a3.name when 'bed' then 1 else 0 end) 床数量
from c 
left join b on c.city=b.city and b.district=c.district
left join a a1 on a1.id=c.[city]
left join a a2 on a2.id=c.district
left join a a3 on a3.id=c.area
group by a1.name,a2.name,b.address
/*
city       district   address    桌子数量        椅子数量        床数量
---------- ---------- ---------- ----------- ----------- -----------
north      Beijing    NULL       1           1           0
south      Shanghai   pudong     2           1           0
south      Shanghai   xuhui      2           1           0(3 行受影响)
*/
select a1.name city,
a2.name district,
b.address,
SUM(case a3.name when 'desk' then cost else 0 end) 桌子数量,
SUM(case a3.name when 'chair' then cost else 0 end) 椅子数量,
SUM(case a3.name when 'bed' then cost else 0 end) 床数量
from c 
left join b on c.city=b.city and b.district=c.district
left join a a1 on a1.id=c.[city]
left join a a2 on a2.id=c.district
left join a a3 on a3.id=c.area
group by a1.name,a2.name,b.address
/*
city       district   address    桌子数量        椅子数量        床数量
---------- ---------- ---------- ----------- ----------- -----------
north      Beijing    NULL       9           9           0
south      Shanghai   pudong     14          8           0
south      Shanghai   xuhui      14          8           0(3 行受影响)
*/

Answer 7

--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-22 21:46:56
--  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)
--------------------------------------------------------------------------
--> 生成测试数据表: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--> 生成测试数据表:bIF NOT OBJECT_ID('[b]') IS NULL
DROP TABLE [b]
GO
CREATE TABLE [b]([id] INT IDENTITY,[area] INT,[city] INT,[district] NVARCHAR(10),[address] NVARCHAR(10))
INSERT [b]
SELECT 1,4,5,'pudong' UNION ALL
SELECT 2,4,5,'xuhui' UNION ALL
SELECT 3,4,6,'chaoyang'
GO--> 生成测试数据表:cIF NOT OBJECT_ID('[c]') IS NULL
DROP TABLE [c]
GO
CREATE TABLE [c]([id] INT IDENTITY,[category] INT,[area] INT,[city] INT,[district] INT,[cost] 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 [a]
--SELECT * FROM [b]
--SELECT * FROM [c]-->SQL查询如下:
select a1.name city,
a2.name district,
a4.name address,
SUM(case a3.name when 'desk' then 1 else 0 end) 桌子数量,
SUM(case a3.name when 'chair' then 1 else 0 end) 椅子数量,
SUM(case a3.name when 'bed' then 1 else 0 end) 床数量
from c 
left join b on c.city=b.city and b.district=c.district
left join a a1 on a1.id=c.[city]
left join a a2 on a2.id=c.district
left join a a3 on a3.id=c.area
left join a a4 on a4.id=c.cost
group by a1.name,a2.name,a4.name
/*
city       district   address    桌子数量        椅子数量        床数量
---------- ---------- ---------- ----------- ----------- -----------
north      Beijing    chaoyang   1           1           0
south      Shanghai   pudong     4           0           0
south      Shanghai   xuhui      0           2           0(3 行受影响)
*/最后猜一下

Answer 8

B表和C表的 id列，是自增长的。
B表的area列，对应A表的ID：3， 4
C表的category列，对应A表的ID为 10.11.12,以此类推。

Answer 9

不太对啊，city列对应的肯定是beijing ,shanghai ，district列对应的肯定是chaoyang,pudong 等，south和north代表这俩city的区域，也就是area,我再发一次表结构，结果里把south和north去掉。表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
      1     4       5      pudong        
      2     4       5      xuhui  
      3     4       6      chaoyang表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现在要求统计出每个区域每种设备的库存量。完成后结果如下：
       city    district   address   桌子数量   椅子数量   床数量 
      beijing   chaoyang               ?        ?       ？
      shanghai    pudong                ？      ?        ?
      shanghai     xuhui                ?       ?      ?

Answer 10

--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-22 21:46:56
--  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)
--------------------------------------------------------------------------
--> 生成测试数据表: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--> 生成测试数据表:bIF NOT OBJECT_ID('[b]') IS NULL
DROP TABLE [b]
GO
CREATE TABLE [b]([id] INT,[area] INT,[city] INT,[district] NVARCHAR(10))
INSERT [b]
SELECT 1,4,5,'pudong' UNION ALL
SELECT 2,4,5,'xuhui' UNION ALL
SELECT 3,4,6,'chaoyang'
GO
--> 生成测试数据表: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-->SQL查询如下:
--SELECT * FROM [a]
--SELECT * FROM [b]
--SELECT * FROM [c]-->SQL查询如下:
select a0.name area,
a1.name city,
    a2.name district,
    '' address,
    SUM(case a3.name when 'desk' then 1 else 0 end) 桌子数量,
    SUM(case a3.name when 'chair' then 1 else 0 end) 椅子数量,
    SUM(case a3.name when 'bed' then 1 else 0 end) 床数量
from 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
/*
area       city       district   address 桌子数量        椅子数量        床数量
---------- ---------- ---------- ------- ----------- ----------- -----------
north      Beijing    chaoyang           1           1           0
south      Shanghai   pudong             4           0           0
south      Shanghai   xuhui              0           2           0(3 行受影响)
*/

Answer 11

谢谢楼上热心帮忙。
按上面最后的统计，桌子数量一共5把，可是在C表里category为10的，也就是desk的数量是3啊

Answer 12

--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-22 21:46:56
--  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)
--------------------------------------------------------------------------
--> 生成测试数据表: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--> 生成测试数据表:bIF NOT OBJECT_ID('[b]') IS NULL
    DROP TABLE [b]
GO
CREATE TABLE [b]([id] INT,[area] INT,[city] INT,[district] NVARCHAR(10))
INSERT [b]
SELECT 1,4,5,'pudong' UNION ALL
SELECT 2,4,5,'xuhui' UNION ALL
SELECT 3,4,6,'chaoyang'
GO
--> 生成测试数据表: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-->SQL查询如下:
--SELECT * FROM [a]
--SELECT * FROM [b]
--SELECT * FROM [c]-->SQL查询如下:
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
/*
area       city       district   address 桌子数量        椅子数量        床数量
---------- ---------- ---------- ------- ----------- ----------- -----------
north      Beijing    chaoyang           1           1           0
south      Shanghai   pudong             2           0           0
south      Shanghai   xuhui              0           1           0(3 行受影响)
*/