Question

SQL建立多表连接的视图

如下是3个表
create table HIC_base(
sn char(30) primary key, --SN编号（唯一、字符串);1
uid char(40) unique not null, --用户关键码（唯一、字符串);2
shno varchar(30) not null--发货商代码号;12
);

create table HIC_shippers(
shno varchar(30) primary key not null, --发货商代码号;12
shname varchar(40) not null, --发货商名称13
foreign key(shno) references HIC_base(shno)
);

create table HIC_device(
sn char(30) primary key, --SN编号（唯一、字符串);1
model char(30) not null, --HIC产品型号;3
foreign key(sn) references HIC_base(sn)
);

如上
我试图建立个视图，取用以上3个表的信息，显示，每个shno所拥有的每个model的sn的数量。。。不知我的表述是否能让人理解。。

create view HIC_shMdNumDet(shno,shname,mdNum,model,snNum)
如上，snNum指的是，每个同样的model对应的sn数量。。。。

各种感激帮助者
mdNum指的是，每个shipper.shno拥有的model 个数
snNum指的是，每个商家，每个model拥有的sn个数
视图什么其实没什么。。
求一个Select，主要是要求得到 mdNum 和 snNum的统计值

Answer 1

先写了个查询语句，你看看执行没问题吧？没问题的话再变成视图。
因为你要的结果中，mdNum和model是1对多的关系，所以用了右外连接，不知道结果是不是你想要的结果。
select shno,shname,mdNum,model,snNum from
(select a.shno,a.shname,count(a.sn) mdNum,c.model from HIC_shippers a join HIC_base b on a.shno=b.shno
join HIC_device c on a.sn=c.sn group by a.shno,a.shname,c.model) a
right join (select model,count(c.sn) snNum from HIC_base b
join HIC_device c on a.sn=c.sn group by b.shno,c.model) b
on a.model=b.model

追问

执行有问题。。。
a 和 b什么重名了吧？
“Server: Msg 107, Level 16, State 2, Line 1
列前缀 'a' 与查询中所用的表名或别名不匹配。
”

追答

哦对》。。。
select a.shno,a.shname,a.mdNum,b.model,b.snNum from
(select a.shno,a.shname,count(a.sn) mdNum,c.model from HIC_shippers a join HIC_base b on a.shno=b.shno
join HIC_device c on a.sn=c.sn group by a.shno,a.shname,c.model) a
right join (select model,count(c.sn) snNum from HIC_base b
join HIC_device c on a.sn=c.sn group by b.shno,c.model) b
on a.model=b.model

追问

。。。。。
Server: Msg 107, Level 16, State 2, Line 1
列前缀 'a' 与查询中所用的表名或别名不匹配。

追答

不好意思...下面这个我测试过了，OK了。
select a.shno,a.shname,a.mdNum,b.model,b.snNum from
(select a.shno,a.shname,count(b.sn) mdNum,c.model from HIC_shippers a join HIC_base b on a.shno=b.shno
join HIC_device c on b.sn=c.sn group by a.shno,a.shname,c.model) a
right join (select model,count(c.sn) snNum from HIC_base b
join HIC_device c on b.sn=c.sn group by b.shno,c.model) b
on a.model=b.model

追问

统计结果似乎不如人意啊。。见图

追答

看到你的测试数据，我明白你的意思了。。下面的语句就OK了：
select a.shno,a.shname,a.mdNum,b.model,b.snNum from
(select a.shno,a.shname,count(b.sn) mdNum,c.model from HIC_shippers a join HIC_base b on a.shno=b.shno
join HIC_device c on b.sn=c.sn group by a.shno,a.shname,c.model) a
join (select b.shno,model,count(c.sn) snNum from HIC_base b
join HIC_device c on b.sn=c.sn group by b.shno,c.model) b
on a.model=b.model and a.shno=b.shno

Answer 2

建视图，最好表有索引，这样多表联合，检索的效率会好一些。

Answer 3

不是很明白你的意思，
不过你要建视图，最好表有索引，这样多表联合，检索的效率会好一些。

Answer 4

完全可以的，不过我质疑你这个视图用后效率到底能提高多少。。

追问

我知道可以的，刚刚做了个2表连接的统计，现在做3表的实在受不了。。
。。。
视图一定要追求效率么？拥有统计，看起来清爽不少也可以认为是目的

追答

mdNum是什么？ 一般作视图都是为了效率或者起到权限控制作用的.

SELECT shp.shno, shp.shname, base.model, SUM(base.sn)
FROM HIC_shippers shp ,HIC_device dev, HIC_base base
WHERE shp.shno = base.shno
AND base.sn = dev.sn
Group by shp.shno, shp.shname, base.model

追问

每个shipper 有[0,正无穷]个HIC_base
mdNum指的是，每个shipper.shno拥有的model 个数
snNum指的是，每个商家，每个model拥有的sn个数。。。。。。。

用的似乎应当是count

追答

周一再看，下班先