不看会后悔的Windows XP之经验谈	简单快捷DIY实用家庭影院

请问这种视图能够构件出来吗？？拜托各位高手，如果不能解决，告诉我原因

楼主cxlhero（曲终人不散）2006-06-04 12:16:10 在 MS-SQL Server / 应用实例提问

就两个表，结构如下
1〉库存表
TZBH(编号)，产品编号，工厂编号，批次号，剩余数量，包装形式，仓库名称，库存地点，生产日期，桶数，净重

然后是表2 是每个库存对应的指标，是个细表通过工厂编号和批次号来对应
2> 指标表
AUTOID 工厂编号，批次号，指标名称，指标值
数据如下 1 01 20060602 酸度 5
2 01 20060602 糖度 20
3 01 20060602 钙离子 5
4 01 20060603 酸度 6
5 01 20060603 糖度 22
6 01 20060603 钙离子 8

如何构件为这样的视图

TZBH(编号)，产品编号，工厂编号，批次号，剩余数量，包装形式，仓库名称，库存地点，生产日期，桶数，净重，酸度，糖度钙离子
///// 指标表中的数据成为视图中的字段
问题点数：100、回复次数：13Top

1 楼itblog（Just for wife!）回复于 2006-06-04 12:25:58 得分 0

select a.*,
酸度=case b.指标名称 when '酸度' then 指标值 end,
糖度=case b.指标名称 when '糖度' then 指标值 end,
钙离子=case b.指标名称 when '钙离子' then 指标值 end
from 库存表 a
inner join 指标表 b on a.工厂编号=b.工厂编号 and a.批次号=b.批次号Top

2 楼itblog（Just for wife!）回复于 2006-06-04 12:27:47 得分 0

你也可以根据需要，使用left join,full join 或是right joinTop

3 楼xeqtr1982（Visual C# .NET）回复于 2006-06-04 12:38:46 得分 0

create table tb(
TZBH int,
产品编号 int,
工厂编号 varchar(10),
批次号 int,
剩余数量 int,
包装形式 varchar(10),
仓库名称 varchar(10),
库存地点 varchar(10),
生产日期 varchar(10),
桶数 int,
净重 int)

insert into tb select 1,1,'01',20060602,1000,'瓶','A','china','2005-10-10',1000,100
insert into tb select 2,1,'01',20060603,2000,'瓶','B','china','2006-06-11',2000,200
go

create table tt(AUTOID int,工厂编号 varchar(10),批次号 int,指标名称 varchar(10),指标值 int)
insert into tt select 1,'01',20060602, '酸度', 5
union all select 2 , '01' ,20060602 , '糖度' ,20
union all select 3 , '01' , 20060602 , '钙离子' , 5
union all select 4 , '01' , 20060603 , '酸度' , 6
union all select 5 , '01' , 20060603 , '糖度' , 22
union all select 6 , '01' , 20060603 , '钙离子' , 8
go

create view aaa
as

select [TZBH(编号)]=a.TZBH,
a.产品编号,
a.工厂编号,
a.批次号,
a.剩余数量,
a.包装形式,
a.仓库名称,
a.库存地点,
a.生产日期,
a.桶数,
a.净重,
酸度=max(case b.指标名称 when '酸度' then b.指标值 else 0 end),
糖度=max(case b.指标名称 when '糖度' then b.指标值 else 0 end),
钙离子=max(case b.指标名称 when '钙离子' then b.指标值 else 0 end)
from
tb a,tt b
where
a.工厂编号=b.工厂编号 and
a.批次号=b.批次号
group by
a.TZBH,
a.产品编号,
a.工厂编号,
a.批次号,
a.剩余数量,
a.包装形式,
a.仓库名称,
a.库存地点,
a.生产日期,
a.桶数,
a.净重
go

select * from aaa

drop view aaa
drop table tb,tt

--这样？Top

4 楼cxlhero（曲终人不散）回复于 2006-06-04 13:13:18 得分 0

非常感谢大家的回答，能看看我当时做的东西吗？
这是我当时生成的
SELECT SCGC, CPBH, PCH AS 批次号, SYSL AS 剩余数量, BZXS AS 包装形式,
CKMC AS 仓库名称, KCDD AS 库存地点, SCRQ AS 生产日期, TS AS 桶数, JZ AS 净重,
(SELECT ruleValue
FROM kc_rule_detail
WHERE Tz.SCGC = SCGC AND Tz.PCH = PCH AND rulename = '糖度') AS 糖度,
(SELECT ruleValue
FROM kc_rule_detail
WHERE Tz.SCGC = SCGC AND Tz.PCH = PCH AND rulename = '酸度') AS 酸度,
(SELECT ruleValue
FROM kc_rule_detail
WHERE Tz.SCGC = SCGC AND Tz.PCH = PCH AND rulename = '浊度') AS 浊度,
(SELECT ruleValue
FROM kc_rule_detail
WHERE Tz.SCGC = SCGC AND Tz.PCH = PCH AND rulename = '透光率') AS 透光率,
(SELECT ruleValue
FROM kc_rule_detail
WHERE Tz.SCGC = SCGC AND Tz.PCH = PCH AND rulename = '纳离子') AS 纳离子,
(SELECT ruleValue
FROM kc_rule_detail
WHERE Tz.SCGC = SCGC AND Tz.PCH = PCH AND rulename = '钙离子') AS 钙离子,
(SELECT ruleValue
FROM kc_rule_detail
WHERE Tz.SCGC = SCGC AND Tz.PCH = PCH AND rulename = 'qq') AS qq,
(SELECT ruleValue
FROM kc_rule_detail
WHERE Tz.SCGC = SCGC AND Tz.PCH = PCH AND rulename = 'TT') AS TT
FROM dbo.Kc_KCTZ Tz
但是里面的子查询不具有唯一性，所以视图有问题，大家帮我解决下把，解决后另有加分Top

5 楼cxlhero（曲终人不散）回复于 2006-06-04 13:15:16 得分 0

这个视图是在程序中动态产生的，执行后如上因为每个产品的东西的指标不一样，Top

6 楼itblog（Just for wife!）回复于 2006-06-04 13:20:39 得分 0

SELECT
SCGC, CPBH, PCH AS 批次号, SYSL AS 剩余数量, BZXS AS 包装形式,
CKMC AS 仓库名称, KCDD AS 库存地点, SCRQ AS 生产日期, TS AS 桶数, JZ AS 净重,
糖度=case b.rulename when '糖度' then ruleValue end,
酸度=case b.rulename when '酸度' then ruleValue end,
浊度=case b.rulename when '浊度' then ruleValue end,
透光率=case b.rulename when '透光率' then ruleValue end,
纳离子=case b.rulename when '纳离子' then ruleValue end,
钙离子=case b.rulename when '钙离子' then ruleValue end ,
qq=case b.rulename when 'qq' then ruleValue end,
TT=case b.rulename when 'TT' then ruleValue end
FROM dbo.Kc_KCTZ Tz
left join Tz.SCGC b on Tz.SCGC =b.SCGC AND Tz.PCH =b.PCHTop

7 楼itblog（Just for wife!）回复于 2006-06-04 13:31:42 得分 0

8 楼cxlhero（曲终人不散）回复于 2006-06-04 13:42:33 得分 0

itblog(^ω^) 的，还有问题，我改下，放心，搞定一定给分Top

9 楼cxlhero（曲终人不散）回复于 2006-06-04 13:44:50 得分 0

我的要求是根据Tz.SCGC =b.SCGC AND Tz.PCH =b.PCH
在试图中显示为一条记录，而不是每个对应指标都成为一条记录了Top

10 楼xeqtr1982（Visual C# .NET）回复于 2006-06-04 13:53:04 得分 50

--借用itblog代码

SELECT
SCGC, CPBH, PCH AS 批次号, SYSL AS 剩余数量, BZXS AS 包装形式,
CKMC AS 仓库名称, KCDD AS 库存地点, SCRQ AS 生产日期, TS AS 桶数, JZ AS 净重,
糖度=max(case b.rulename when '糖度' then ruleValue end),
酸度=max(case b.rulename when '酸度' then ruleValue end),
浊度=max(case b.rulename when '浊度' then ruleValue end),
透光率=max(case b.rulename when '透光率' then ruleValue end),
纳离子=max(case b.rulename when '纳离子' then ruleValue end),
钙离子=max(case b.rulename when '钙离子' then ruleValue end),
qq=max(case b.rulename when 'qq' then ruleValue end),
TT=max(case b.rulename when 'TT' then ruleValue end)
FROM dbo.Kc_KCTZ Tz
left join kc_rule_detai b on Tz.SCGC =b.SCGC AND Tz.PCH =b.PCH
group by SCGC, CPBH, PCH, SYSL, BZXS,
CKMC, KCDD, SCRQ, TS, JZ

这样试试Top

11 楼itblog（Just for wife!）回复于 2006-06-04 14:01:27 得分 50

如果是按最大值取，可以用楼上的，如果是求和，可以改成这样的：

SELECT
SCGC, CPBH, PCH AS 批次号, SYSL AS 剩余数量, BZXS AS 包装形式,
CKMC AS 仓库名称, KCDD AS 库存地点, SCRQ AS 生产日期, TS AS 桶数, JZ AS 净重,
糖度=sum(case b.rulename when '糖度' then ruleValue end),
酸度=sum(case b.rulename when '酸度' then ruleValue end),
浊度=sum(case b.rulename when '浊度' then ruleValue end),
透光率=sum(case b.rulename when '透光率' then ruleValue end),
纳离子=sum(case b.rulename when '纳离子' then ruleValue end),
钙离子=sum(case b.rulename when '钙离子' then ruleValue end) ,
qq=sum(case b.rulename when 'qq' then ruleValue end),
TT=sum(case b.rulename when 'TT' then ruleValue end)
FROM dbo.Kc_KCTZ Tz
left join kc_rule_detai b on Tz.SCGC =b.SCGC AND Tz.PCH =b.PCH
group by SCGC,CPBH,PCH,SYSL, BZXS,CKMC,KCDD,SCRQ,TS,JZTop

12 楼xeqtr1982（Visual C# .NET）回复于 2006-06-04 14:03:50 得分 0

:)Top

13 楼cxlhero（曲终人不散）回复于 2006-06-04 14:10:18 得分 0

感谢大家的帮助，虽然还有点问题，但是非常感谢你们，开始散分了
另外，我的QQ15931853 希望以后和你们多交流Top