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

zbasic 2008-03-21 12:56:14
问题描述
有一个产品大类的表(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 5

class表
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_4

other表
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_5

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
表结构说明：

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 11

declare #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

我已经尽我的能力把问题描述清楚了，希望各位能看懂，谢谢！！
...全文
153 15 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
zbasic 2008-03-21
  • 打赏
  • 举报
 回复
不好意思，建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

这段删掉就好了
zbasic 2008-03-21
  • 打赏
  • 举报
 回复
服了各位了，这个帖子先结了，现在答案是得到了。考虑qianjin036a 提到的确实可以把class表和other表合并，这样结构更合理，当时设计要把这2个表分开是基于另外一种考虑，现在没有这种必要了。

谢谢各位辛苦地看完题还要去分析表结构。该给的分都给了。
ylsn1982 2008-03-21
  • 打赏
  • 举报
 回复
ding
JiangHongTao 2008-03-21
  • 打赏
  • 举报
 回复 
--建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
zbasic 2008-03-21
  • 打赏
  • 举报
 回复
感谢各位在凌晨2点半还在研究这个问题

dawugui 潇洒老乌龟(爱新觉罗.毓华)的结果在正式库中测试

回复：
[Quote=引用 11 楼 qianjin036a 的回复:]
---这3个表中都有对应该产品的唯一标识号prd_id.

不满足第三范式，如果硬要这样，那么中间的两个表都不要建了，只要在第一个表中设置一个列说明它是哪种小类就行了。

---cat为大类名称表，通过字段cat_prd_id与class,other表关联。type_id为标识的大类数据统计类型。prd_id为每个产品的唯一标识号

不满足连接关系 prd_id 不应出现在大类表中。

---数据表中的prd_id为 prd_id_6 _7 _8...的类，在哪个表中可以找…
[/Quote]

因为我们是在原来别人的一个库上做扩展功能，原本库上的一些结构无法改变，而我们得在他原有的分类方式上进行二次分类，所以才导致了设计成现在这3种表的结果。也知道这样划分表不大合理，这也是不得已而为之的办法。

数据表中的prd_id为 prd_id_6 _7 _8...的类，可以在表class和other表中找到，他对应的销售数据可以在prd_values表中找到.
-晴天 2008-03-21
  • 打赏
  • 举报
 回复
---这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 分开，只得这样。

要是做成这样，求统计可能才好说一些。
dawugui 2008-03-21
  • 打赏
  • 举报
 回复 
--我将你的临时表改为实际表.

--建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_name



drop 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 行）

*/
-狙击手- 2008-03-21
  • 打赏
  • 举报
 回复
恩，明天再说吧。

我和依依聊天 呢
dawugui 2008-03-21
  • 打赏
  • 举报
 回复
晕了.明天再说了.
zbasic 2008-03-21
  • 打赏
  • 举报
 回复
好的，谢谢了，看起来比较累人，关系有点复杂。
因为真实数据不能给出，只能用这些东西代替了。
dawugui 2008-03-21
  • 打赏
  • 举报
 回复
哦,那我看看.
zbasic 2008-03-21
  • 打赏
  • 举报
 回复
后面有结果啊，还有这个结果怎么得来的。
这段

具体结果及说明如下:

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)]
dawugui 2008-03-21
  • 打赏
  • 举报
 回复
你最好把结果写出来.

zbasic 2008-03-21
  • 打赏
  • 举报
 回复
[Quote=引用 2 楼 dawugui 的回复:]
够长.
[/Quote]

哥们，花了半个多小时才把这个问题整理好，将近一个小时才全部弄完了贴出来。
dawugui 2008-03-21
  • 打赏
  • 举报
 回复
够长.
envi5.3处理高分二号影像数据详细过程记录
全文根据此篇文章进行数据处理： tm影像辐射定标_高分二号影像数据预处理及裁剪过程 其他参考： 学习笔记---遥感影像辐射定标与大气校正 利用ENVI自带全球DEM数据计算区域平均高程 高分二号数据处理流程（有高分二号的数据信息） GF2有多光谱和全色两个数据，MSS是多光谱的，PAN是全色的，多光谱的分辨率低，全色的高，需要把这两个数据融合，融合之前需要做定标等处理，获取经纬度。 一、多光谱影像处理 1. 辐射定标 以这个为例： 其他路径\2022.4.2...
高分一号（GF-1）影像数据下载方法
高分一号影像数据的下载方法： 方法一：遥感集市下载，网址：http://www.rscloudmart.com/ 点击网址打开遥感集市网页，点击数据中心，显示如下图影像查询和下载界面，在下载之前，首先应注册账户，点击注册按钮免费注册网站账户，登录账户之后即可下载查询的影像数据。目前高分一号（GF-1）影像数据仅能免费下载16米分辨率的WFV传感器影像数据，8米分辨率影像数据需要付费下载。所查询的影像数据加入到购物车之后需要等待一段时间才能下载，时间一般在15天内。 遥感集市界面图 ...
高分一号数据处理（一）：数据下载
首先需要在中国资源卫星应用中心-数据服务平台下载相关高分数据，需要提前进行注册与登陆。 下载步骤：产品与服务→产品查询（如果此页面打开不出图，可以在资料下载界面下载安装flash插件，建议直接更换浏览器，比如IE浏览器或qq浏览器，多尝试）。 根据左侧设置需要的影像的基本参数→查询 勾选需要的影像后，点击立即订购 在购物车中选择需要的影像→生成订单 填写订单信息→提交 进入订单管理界面，等待审核。审核完成后根据提供的链接进行数据下载，本人目前数据下载成功率没有特别高，如...
高分卫星数据介绍
高分专项目前已经发射了7颗遥感卫星。高分一号卫星，高分辨率相机全色分辨率2米、多光谱分辨率8米，并携带16米分辨率幅宽800千米的宽幅相机，是典型的高时间分辨率（宽覆盖）遥感卫星；高分二号卫星，携带了全色分辨率优于1米、多光谱分辨率优于4米的高分辨率相机，实现了较高的空间分辨率；高分三号卫星，携带C波段多极化合成孔径雷达的，是中国第一颗民用合成孔径雷达卫星，空间分辨率最高可达1米，主要用于对海洋目标的监测；高分四号卫星，是静止轨道光学遥感卫星，携带全色分辨率50米的凝视相机，在静止轨道上对7000千米X70
高分一号PMS数据预处理
GF-1 PMS数据完整预处理流程包括：辐射定标、大气校正、正射校正、图像融合、裁剪和影像配准。 注：全色图像没有方法进行大气校正，所以一般在定量遥感中不使用全色图像。本文档的操作流程是为了说明所有处理的步骤，如果只需要GF-1数据做底图使用，可以不做辐射定标和大气校正。 数据：GF1_PMS1_E82.3_N34.2_20151103_L1A0001149149 图1 预处理流程 多光谱辐射定标与大气校正 在ENVI 5.3.1版本中，直接支持高分一号PMS数据的辐射定标和大气校正。 其..
MS-SQL Server

34,836

社区成员

254,632

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章