高分(100,目前能给的最多分了)挑战一下本版块的高手,select 多表复合查询并进行数据统计。

Question

问题描述
有一个产品大类的表(cat),分类表(class和other),这3个表中都有对应该产品的唯一标识号prd_id.一个数据表(prd_values),数据表中有针
对每个产品的销售数据。根据大类的划分规则不同，class表和other表中对应的产品销售数据以不同的方式累加到cat中来.
cat表
cat_id       cat_name      prd_id      type_id
1           cat_name_1    prd_id_1       1
2           cat_name_2    prd_id_2       2
3           cat_name_3    prd_id_3       3
4           cat_name_4    prd_id_4       4
5           cat_name_5    prd_id_5       5class表
class_id      class_name     prd_id          cat_prd_id  
1            class_name_1   prd_id_6          prd_id_2
2            class_name_2   prd_id_7          prd_id_3
3            class_name_3   prd_id_8          prd_id_4other表
other_id       other_name    prd_id            cat_prd_id
1            other_name_1   prd_id_9            prd_id_1
2            other_name_2   prd_id_10           prd_id_4
3            other_name_3   prd_id_11           prd_id_5prd_values表
values_id       prd_id        prd_values
1              prd_id_1         1
2              prd_id_2         2
3              prd_id_3         3
4              prd_id_4         4
5              prd_id_5         5
6              prd_id_6         6
7              prd_id_7         7
8              prd_id_8         8
9              prd_id_9         9
10             prd_id_10        10
11             prd_id_11        11
表结构说明：cat为大类名称表，通过字段cat_prd_id与class,other表关联。type_id为标识的大类数据统计类型。prd_id为每个产品的唯一标识号
需要得到的是一个做数据统计的结果。cat 表中的为大类，大类中有可能有分类在class表或者other表中
大类中有5对应关系。
type 1  在other中有分类
type 2  在class中有分类
type 3  在class中有分类
type 4  在other中有分类，在class中有分类
type 5  在other中有分类需要得到的结果为：  
以cat表为基准,如果此大类在class表中有分类，则增加一栏为-xxx数据统计的标准为
如果是cat在other表中有分类，则该分类的数据累加到cat_name中
如果是cat在class表中有分类，则该分类的数据累加到cat_name_xxx中具体结果及说明如下:prd_name           prd_values
cat_name_1            10              [other中有分类'other_id为1的other_name_1,prd_id=prd_id_9,
                                       prd_values表中values=9',则统计结果为=1 (prd_id_1)+9 (prd_id_9)]cat_name_2            2               [class表中有分类，新增栏位-xxx.统计结果为2(prd_id_2))
cat_name_2_xxx        6               [class表中有分类'class_id为1的class_name_1,prd_id=prd_id_6,
                                       prd_values表中values=6',则统计结果为=6(prd_id_6)] 
          
cat_name_3            3               [class表中有分类，新增栏位-xxx.统计结果为3(prd_id_3))
cat_name_3_xxx        7               [class表中有分类'class_id为2的class_name_2,prd_id=prd_id_7,
                                       prd_values表中values=7',则统计结果为=7(prd_id_7)]cat_name_4           14               [other中有分类'other_id为2的other_name_2,prd_id=prd_id_10,
                                       prd_values表中values=10',则统计结果为=4(prd_id_4)+10(prd_id_10)
                                       class表中有分类，新增栏位-xxx]
cat_name_4_xxx        8               [class表中有分类'class_id为3的class_name_3,prd_id=prd_id_8,
                                       prd_values表中values=8',则统计结果为=8(prd_id_8)]cat_name_5           16               [other中有分类'other_id为3的other_name_3,prd_id=prd_id_11,
                                       prd_values表中values=11',统计结果为=5(prd_id_5)+11(prd_id_11)]为免大家建表的辛苦，在这里我把建表的sql语句写好了.--建cat表
declare #cat table(cat_id int,cat_name varchar(15),prd_id varchar(15),type_id smallint) 
insert into #cat select 1,'cat_name_1','prd_id_1' ,1
union all select 2,'cat_name_2','prd_id_2' ,2 
union all select 3,'cat_name_3','prd_id_3' ,3
union all select 4,'cat_name_4','prd_id_4' ,4
union all select 5,'cat_name_5','prd_id_5' ,5--建class表
declare #class table(class_id int,class_name varchar(15),prd_id varchar(15),cat_prd_id varchar(15)) 
insert into #class select 1,'class_name_1','prd_id_6','prd_id_2'
union all select 2,'class_name_2','prd_id_7' , 'prd_id_3'
union all select 3,'class_name_3','prd_id_8' ,'prd_id_4'--建other表
declare #other table(other_id int,other_name varchar(15),prd_id varchar(15),cat_prd_id varchar(15)) 
insert into #other select 1,'other_name_1','prd_id_9','prd_id_1'
union all select 2,'other_name_2','prd_id_10' , 'prd_id_4'
union all select 3,'other_name_3','prd_id_11' ,'prd_id_5'--建prd_values表
prd_values表
values_id       prd_id        prd_values
1              prd_id_1         1
2              prd_id_2         2
3              prd_id_3         3
4              prd_id_4         4
5              prd_id_5         5
6              prd_id_6         6
7              prd_id_7         7
8              prd_id_8         8
9              prd_id_9         9
10             prd_id_10        10
11             prd_id_11        11declare #prd_values table(values_id int,prd_id varchar(15),prd_values int)
insert into #prd_values select 1,'prd_id_1',1
union all select 2,'prd_id_2',2
union all select 3,'prd_id_3',3
union all select 4,'prd_id_4',4
union all select 5,'prd_id_5',5
union all select 6,'prd_id_6',6
union all select 7,'prd_id_7',7
union all select 8,'prd_id_8',8
union all select 9,'prd_id_9',9
union all select 10,'prd_id_10',10
union all select 11,'prd_id_11',11我已经尽我的能力把问题描述清楚了，希望各位能看懂，谢谢！！

Answer 1

不好意思，建prd_values表的时候会报错，忘记把上面的表删掉了。
把--建prd_values表 下面的prd_values表 
values_id       prd_id        prd_values 
1              prd_id_1         1 
2              prd_id_2         2 
3              prd_id_3         3 
4              prd_id_4         4 
5              prd_id_5         5 
6              prd_id_6         6 
7              prd_id_7         7 
8              prd_id_8         8 
9              prd_id_9         9 
10             prd_id_10        10 
11             prd_id_11        11 这段删掉就好了

Answer 2

后面有结果啊，还有这个结果怎么得来的。
这段具体结果及说明如下: prd_name           prd_values 
cat_name_1            10              [other中有分类'other_id为1的other_name_1,prd_id=prd_id_9, 
                                       prd_values表中values=9',则统计结果为=1 (prd_id_1)+9 (prd_id_9)] cat_name_2            2               [class表中有分类，新增栏位-xxx.统计结果为2(prd_id_2)) 
cat_name_2_xxx        6               [class表中有分类'class_id为1的class_name_1,prd_id=prd_id_6, 
                                       prd_values表中values=6',则统计结果为=6(prd_id_6)]  
           
cat_name_3            3               [class表中有分类，新增栏位-xxx.统计结果为3(prd_id_3)) 
cat_name_3_xxx        7               [class表中有分类'class_id为2的class_name_2,prd_id=prd_id_7, 
                                       prd_values表中values=7',则统计结果为=7(prd_id_7)] cat_name_4           14               [other中有分类'other_id为2的other_name_2,prd_id=prd_id_10, 
                                       prd_values表中values=10',则统计结果为=4(prd_id_4)+10(prd_id_10) 
                                       class表中有分类，新增栏位-xxx] 
cat_name_4_xxx        8               [class表中有分类'class_id为3的class_name_3,prd_id=prd_id_8, 
                                       prd_values表中values=8',则统计结果为=8(prd_id_8)] cat_name_5           16               [other中有分类'other_id为3的other_name_3,prd_id=prd_id_11, 
                                       prd_values表中values=11',统计结果为=5(prd_id_5)+11(prd_id_11)] 

Answer 3

--我将你的临时表改为实际表.
--建cat表 
create table cat(cat_id int,cat_name varchar(15),prd_id varchar(15),type_id smallint)  
insert into cat select 1,'cat_name_1','prd_id_1' ,1 
union all select 2,'cat_name_2','prd_id_2' ,2  
union all select 3,'cat_name_3','prd_id_3' ,3 
union all select 4,'cat_name_4','prd_id_4' ,4 
union all select 5,'cat_name_5','prd_id_5' ,5 --建class表 
create table class(class_id int,class_name varchar(15),prd_id varchar(15),cat_prd_id varchar(15))  
insert into class select 1,'class_name_1','prd_id_6','prd_id_2' 
union all select 2,'class_name_2','prd_id_7' , 'prd_id_3' 
union all select 3,'class_name_3','prd_id_8' ,'prd_id_4' --建other表 
create table other(other_id int,other_name varchar(15),prd_id varchar(15),cat_prd_id varchar(15))  
insert into other select 1,'other_name_1','prd_id_9','prd_id_1' 
union all select 2,'other_name_2','prd_id_10' , 'prd_id_4' 
union all select 3,'other_name_3','prd_id_11' ,'prd_id_5' --建prd_values表 
create table prd_values (values_id int,prd_id varchar(15),prd_values int) 
insert into prd_values select 1,'prd_id_1',1 
union all select 2,'prd_id_2',2 
union all select 3,'prd_id_3',3 
union all select 4,'prd_id_4',4 
union all select 5,'prd_id_5',5 
union all select 6,'prd_id_6',6 
union all select 7,'prd_id_7',7 
union all select 8,'prd_id_8',8 
union all select 9,'prd_id_9',9 
union all select 10,'prd_id_10',10 
union all select 11,'prd_id_11',11 --1、查找cat中prd_id不在class中的cat_prd_id的prd_id，为1,5。
select m1.cat_name , prd_values = (select prd_values from prd_values where prd_id = m2.prd_id) + (select prd_values from prd_values where prd_id = m2.cat_prd_id) from (select a.cat_name , a.prd_id from cat a where prd_id not in (select cat_prd_id from class)) m1,other m2 where m1.prd_id = m2.cat_prd_id
union all
--2、查找class中cat_prd_id不在other中cat_prd_id的cat_prd_id,为2,3，先算不知道怎么解释. 
select m1.cat_name , prd_values from cat m1 , (select cat_prd_id from class where cat_prd_id not in (select cat_prd_id from other)) m2,prd_values m3 where m1.prd_id = m2.cat_prd_id and m2.cat_prd_id = m3.prd_id 
union all
--3、查找class中cat_prd_id不在other中cat_prd_id的cat_prd_id,为2,3，后算不知道怎么解释. 
select cat_name = m1.cat_name + '_xxx' , prd_values from cat m1 , (select prd_id , cat_prd_id from class where cat_prd_id not in (select cat_prd_id from other)) m2,prd_values m3 where m1.prd_id = m2.cat_prd_id and m2.prd_id = m3.prd_id 
union all
--4、查找class中cat_prd_id在other中cat_prd_id的cat_prd_id,为4，先算不知道怎么解释. 
select m1.cat_name , prd_values = (select prd_values from prd_values where prd_id = m2.prd_id) + (select prd_values from prd_values where prd_id = m2.cat_prd_id) from cat m1 , (select prd_id , cat_prd_id from other where cat_prd_id in (select cat_prd_id from class)) m2 where m1.prd_id = m2.cat_prd_id 
union all
--5、查找class中cat_prd_id在other中cat_prd_id的cat_prd_id,为4，后算不知道怎么解释. 
select cat_name = m1.cat_name + '_xxx' , prd_values from cat m1 , (select prd_id,cat_prd_id from class where cat_prd_id in (select cat_prd_id from other)) m2,prd_values m3 where m1.prd_id = m2.cat_prd_id and m2.prd_id = m3.prd_id 
order by cat_namedrop table cat,class,other,prd_values/*
cat_name            prd_values  
------------------- ----------- 
cat_name_1          10
cat_name_2          2
cat_name_2_xxx      6
cat_name_3          3
cat_name_3_xxx      7
cat_name_4          14
cat_name_4_xxx      8
cat_name_5          16（所影响的行数为 8 行）
*/

Answer 4

---这3个表中都有对应该产品的唯一标识号prd_id.不满足第三范式，如果硬要这样，那么中间的两个表都不要建了，只要在第一个表中设置一个列说明它是哪种小类就行了。---cat为大类名称表，通过字段cat_prd_id与class,other表关联。type_id为标识的大类数据统计类型。prd_id为每个产品的唯一标识号不满足连接关系  prd_id 不应出现在大类表中。---数据表中的prd_id为  prd_id_6 _7 _8...的类，在哪个表中可以找到呢？提供的数据不对。
从表的设计上看，你的表应设计成这样：大类c
id  name
1    a
2    b
3    c小类d
id  c_id  name
1    1    a1
2    1    a2
3    2    b2
4    2    b5
5    3    c3
关系：c.id,d.id 主键，d.c_id为c.id的外键，一对多其他o
id  c_id  name
1    1    a3
2    2    b2(与小类中同的那种）
3    2    b5
4    3    c2
关系类似于 d产品
id  d_id o_id name p_values
1    1    N    a    25
2    N    3    b    15
3    1    N    c    84
4    2    N    d    125
5    3    N    e    42
6    N    2    f    77
7    N    3    g    652
8    4    N    h    14
9    N    1    i    2
10   5    N    j    47
注：N 是指NULL 因为你一定要把d o 分开，只得这样。要是做成这样，求统计可能才好说一些。

Answer 5

感谢各位在凌晨2点半还在研究这个问题dawugui  潇洒老乌龟(爱新觉罗.毓华)的结果在正式库中测试回复：因为我们是在原来别人的一个库上做扩展功能，原本库上的一些结构无法改变，而我们得在他原有的分类方式上进行二次分类，所以才导致了设计成现在这3种表的结果。也知道这样划分表不大合理，这也是不得已而为之的办法。数据表中的prd_id为  prd_id_6 _7 _8...的类，可以在表class和other表中找到，他对应的销售数据可以在prd_values表中找到.

Answer 6

--建cat表 
create table cat(cat_id int,cat_name varchar(15),prd_id varchar(15),type_id smallint)  
insert into cat select 1,'cat_name_1','prd_id_1' ,1 
union all select 2,'cat_name_2','prd_id_2' ,2  
union all select 3,'cat_name_3','prd_id_3' ,3 
union all select 4,'cat_name_4','prd_id_4' ,4 
union all select 5,'cat_name_5','prd_id_5' ,5 --建class表 
create table class(class_id int,class_name varchar(15),prd_id varchar(15),cat_prd_id varchar(15))  
insert into class select 1,'class_name_1','prd_id_6','prd_id_2' 
union all select 2,'class_name_2','prd_id_7' , 'prd_id_3' 
union all select 3,'class_name_3','prd_id_8' ,'prd_id_4' --建other表 
create table other(other_id int,other_name varchar(15),prd_id varchar(15),cat_prd_id varchar(15))  
insert into other select 1,'other_name_1','prd_id_9','prd_id_1' 
union all select 2,'other_name_2','prd_id_10' , 'prd_id_4' 
union all select 3,'other_name_3','prd_id_11' ,'prd_id_5' --建prd_values表 
create table prd_values (values_id int,prd_id varchar(15),prd_values int) 
insert into prd_values select 1,'prd_id_1',1 
union all select 2,'prd_id_2',2 
union all select 3,'prd_id_3',3 
union all select 4,'prd_id_4',4 
union all select 5,'prd_id_5',5 
union all select 6,'prd_id_6',6 
union all select 7,'prd_id_7',7 
union all select 8,'prd_id_8',8 
union all select 9,'prd_id_9',9 
union all select 10,'prd_id_10',10 
union all select 11,'prd_id_11',11
go
--1,5 取r+l o
--2,3 取r c 取 l c
--4   取r+l o 取 l c
select a.cat_name,sum(prd_values) v  from (select cat_name,b.prd_id,b.cat_prd_id from cat a,other b where a.type_id in(1,4,5) and a.prd_id = b.cat_prd_id) a
       ,prd_values b where a.prd_id=b.prd_id or a.cat_prd_id = b.prd_id group by a.cat_name -- 1(o)、4(o)、5(o) r+l
union
select a.cat_name,sum(prd_values) v  from (select cat_name,b.cat_prd_id from cat a,class b where a.type_id in(2,3) and a.prd_id = b.cat_prd_id) a
       ,prd_values b where a.cat_prd_id=b.prd_id group by a.cat_name -- 2(c)、3(c) r
union 
select a.cat_name+'xxx',sum(prd_values) v  from (select cat_name,b.prd_id from cat a,class b where a.type_id in(2,3,4) and a.prd_id = b.cat_prd_id) a
       ,prd_values b where  a.prd_id = b.prd_id group by a.cat_name+'xxx' -- 2(c)、3(c) l
/*
cat_name           v           
------------------ ----------- 
cat_name_1         10
cat_name_2         2
cat_name_2xxx      6
cat_name_3         3
cat_name_3xxx      7
cat_name_4         14
cat_name_4xxx      8
cat_name_5         16
*/
go
drop table  cat, class,other,prd_values

Answer 7

服了各位了，这个帖子先结了，现在答案是得到了。考虑qianjin036a 提到的确实可以把class表和other表合并，这样结构更合理，当时设计要把这2个表分开是基于另外一种考虑，现在没有这种必要了。谢谢各位辛苦地看完题还要去分析表结构。该给的分都给了。