新手，100分求sql语句，急！！！

[milu626]

我把整个产品流程按供需打散，分成A，B，C，D，E， 5个子表。另有产品信息表P。各表内容如下：
p：
product_code product_name product_weight
(varchar) (varchar) (int)

A00001 psw001 80
A00002 psw002 85
A00003 psw003 60
A00004 psw004 90

A,B,C,D,E五个工序表结构相同，只是名称有别(A_date和product_code共同作主键)：

A:
A_date A_product_code A_product_num
(varchar) (varchar) (int)

20041205 A00002 50
20041205 A00004 30

B:
B_date B_product_code B_product_num
(varchar) (varchar) (int)

20041205 A00002 20
20041205 A00003 40

C:
C_date C_product_code C_product_num
(varchar) (varchar) (int)

20041205 A00001 25
20041205 A00004 20

D:
D_date D_product_code D_product_num
(varchar) (varchar) (int)

20041205 A00001 50
20041205 A00003 30

E:
E_date E_product_code E_product_num
(varchar) (varchar) (int)

20041205 A00003 20

以上表中字段都不允许为空。
要查询结果表中字段为：

product_code
product_name
product_weight
A_product_num
A_COUNT
B_product_num
B_COUNT
C_product_num
C_COUNT
D_product_num
D_COUNT
E_product_num
E_COUNT

就是只要当天其中一道以上工序涉及到的产品都列到结果表中，没有数据的项用零补，或是保留为空。类似A_COUNT是月初到当前日期该产品本到工序加工的累计数量。最好能在结果表里在加入各道工序加工产品的期初结存和期末结存字段，问可不可以实现，如何实现，谢谢！！

[zjcxc]

to: mlxboy(晴空)

先各自汇总出入库表,再关联即可:


select coalesce(t1.MatID,t2.MatID,t3.MatID) as MatID,
ISNULL(t1.StartNum,0) as StartNum,
ISNULL(t2.InNum,0) as InNum,
ISNULL(t3.OutNum,0) as OutNum
from t1
full join(
select id,InNum=sum(InNum) from t2 group by MatID
)t2 on t1.MatID=t2.MatID
full join(
select id,OutNum=sum(OutNum) from t3 group by MatID
)t3 on t1.MatID=t3.MatID

[mlxboy]

To zjcxc(邹建)

如果有产品 在 T1 中未出现
在T2,T3 中都出现了就会出现 上面现贴子中的问题需要

再套一层group by 然后求和

谢谢 老大
谢谢
wyb0026(小小)

[milu626]

先谢谢大家的帮助啊！！！
代码看的时间长了点，不好意思。
这问题是麻烦，辛苦大家了。

再问一下zjcxc(邹建) 的代码，为什么查出来的只有
product_code，product_name， product_weight， 这三列阿。
想要

product_code
product_name
product_weight
期初_A
期初_C
期初_D
A_product_num
A_COUNT --月初到现在生产量（下同）
B_product_num
B_COUNT
C_product_num
C_COUNT
D_product_num
D_COUNT
E_product_num
E_COUNT
期初_A
期初_C
期初_D

这些字段都显示在一张表里边要怎么做阿。谢谢！！

还有wyb0026(小小) 代码里面的A_COUNT类似字段都提示找不到。

631799(杭州工人)的代码运行提示列前缀 'a' 与查询中所用的表名或别名不匹配。

怎么办啊？

[zjcxc]

--查询处理的存储过程
create proc p_qry
@date varchar(8) --要查询的天
as
--查询处理
SELECT p.product_code,p.product_name,p.product_weight,
A_product_期初,A_product_num,A_count,A_product_期末,
B_product_期初,B_product_num,B_count,B_product_期末,
C_product_期初,C_product_num,C_count,C_product_期末,
D_product_期初,D_product_num,D_count,D_product_期末,
E_product_期初,E_product_num,E_count,E_product_期末
from p,(
select product_code=isnull(A_product_code,
isnull(B_product_code,
isnull(C_product_code,
isnull(D_product_code,E_product_code)))),
A_product_期初=isnull(a.A_Total,0)-isnull(a.A_product_num,0),
A_product_num=isnull(a.A_product_num,0),
A_count=isnull(a.A_count,0),
A_product_期末=isnull(a.A_Total,0),

B_product_期初=isnull(b.B_Total,0)-isnull(b.B_product_num,0),
B_product_num=isnull(b.B_product_num,0),
B_count=isnull(b.B_count,0),
B_product_期末=isnull(b.B_Total,0),

C_product_期初=isnull(c.C_Total,0)-isnull(c.C_product_num,0),
C_product_num=isnull(c.C_product_num,0),
C_count=isnull(c.C_count,0),
C_product_期末=isnull(c.C_Total,0),

D_product_期初=isnull(d.D_Total,0)-isnull(d.D_product_num,0),
D_product_num=isnull(d.D_product_num,0),
D_count=isnull(d.D_count,0),
D_product_期末=isnull(d.D_Total,0),

E_product_期初=isnull(e.E_Total,0)-isnull(e.E_product_num,0),
E_product_num=isnull(e.E_product_num,0),
E_count=isnull(e.E_count,0),
E_product_期末=isnull(e.E_Total,0)
from(
select A_product_code,
A_product_num=sum(case when A_date=@date then A_product_num else 0 end),
A_count=sum(case when A_date between left(@date,6)+'01' and @date then A_product_num else 0 end),
A_Total=sum(A_product_num)
from A
where A_date<=@date
having sum(case when A_date=@date then A_product_num else 0 end)<>0
)a
full join(
select B_product_code,
B_product_num=sum(case when B_date=@date then B_product_num else 0 end),
B_count=sum(case when B_date between left(@date,6)+'01' and @date then B_product_num else 0 end),
B_Total=sum(B_product_num)
from A
where B_date<=@date
having sum(case when B_date=@date then B_product_num else 0 end)<>0
)b on a.A_product_code=b.B_product_code
full join(
select C_product_code,
C_product_num=sum(case when C_date=@date then C_product_num else 0 end),
C_count=sum(case when C_date between left(@date,6)+'01' and @date then C_product_num else 0 end),
C_Total=sum(C_product_num)
from A
where C_date<=@date
having sum(case when C_date=@date then C_product_num else 0 end)<>0
)c on a.A_product_code=c.C_product_code
full join(
select D_product_code,
D_product_num=sum(case when D_date=@date then D_product_num else 0 end),
D_count=sum(case when D_date between left(@date,6)+'01' and @date then D_product_num else 0 end),
D_Total=sum(D_product_num)
from A
where D_date<=@date
having sum(case when D_date=@date then D_product_num else 0 end)<>0
)d on a.A_product_code=d.D_product_code
full join(
select E_product_code,
E_product_num=sum(case when E_date=@date then E_product_num else 0 end),
E_count=sum(case when E_date between left(@date,6)+'01' and @date then E_product_num else 0 end),
E_Total=sum(E_product_num)
from A
where E_date<=@date
having sum(case when E_date=@date then E_product_num else 0 end)<>0
)e on a.A_product_code=e.E_product_code
)b where p.product_code=b.product_code

[zjcxc]

最外层查询忘记把字段列进去了,加上就可以了.

[milu626]

还有个问题问 zjcxc(邹建)兄弟啊，就是现在查出来的结果表里边，如果多个子表有同一种商品的记录，这种商品会可能多次显示，就是分成多行，怎么让一种商品只显示一行呢，让各个子表的信息显示在相应的列里面。
还有就是我把where A_date<=@date
having sum(case when A_date=@date then A_product_num else 0 end)<>0
之间加上了group by A_product_code，不然报语法错误。

[heguosheng]

mark

[zjcxc]

--查询处理的存储过程
create proc p_qry
@date varchar(8) --要查询的天
as
--查询处理
SELECT p.product_code,p.product_name,p.product_weight
from p,(
select product_code=isnull(A_product_code,
isnull(B_product_code,
isnull(C_product_code,
isnull(D_product_code,E_product_code)))),
A_product_期初=isnull(a.A_Total,0)-isnull(a.A_product_num,0),
A_product_num=isnull(a.A_product_num,0),
A_count=isnull(a.A_count,0),
A_product_期末=isnull(a.A_Total,0),

B_product_期初=isnull(b.B_Total,0)-isnull(b.B_product_num,0),
B_product_num=isnull(b.B_product_num,0),
B_count=isnull(b.B_count,0),
B_product_期末=isnull(b.B_Total,0),

C_product_期初=isnull(c.C_Total,0)-isnull(c.C_product_num,0),
C_product_num=isnull(c.C_product_num,0),
C_count=isnull(c.C_count,0),
C_product_期末=isnull(c.C_Total,0),

D_product_期初=isnull(d.D_Total,0)-isnull(d.D_product_num,0),
D_product_num=isnull(d.D_product_num,0),
D_count=isnull(d.D_count,0),
D_product_期末=isnull(d.D_Total,0),

E_product_期初=isnull(e.E_Total,0)-isnull(e.E_product_num,0),
E_product_num=isnull(e.E_product_num,0),
E_count=isnull(e.E_count,0),
E_product_期末=isnull(e.E_Total,0)
from(
select A_product_code,
A_product_num=sum(case when A_date=@date then A_product_num else 0 end),
A_count=sum(case when A_date between left(@date,6)+'01' and @date then A_product_num else 0 end),
A_Total=sum(A_product_num)
from A
where A_date<=@date
having sum(case when A_date=@date then A_product_num else 0 end)<>0
)a
full join(
select B_product_code,
B_product_num=sum(case when B_date=@date then B_product_num else 0 end),
B_count=sum(case when B_date between left(@date,6)+'01' and @date then B_product_num else 0 end),
B_Total=sum(B_product_num)
from A
where B_date<=@date
having sum(case when B_date=@date then B_product_num else 0 end)<>0
)b on a.A_product_code=b.B_product_code
full join(
select C_product_code,
C_product_num=sum(case when C_date=@date then C_product_num else 0 end),
C_count=sum(case when C_date between left(@date,6)+'01' and @date then C_product_num else 0 end),
C_Total=sum(C_product_num)
from A
where C_date<=@date
having sum(case when C_date=@date then C_product_num else 0 end)<>0
)c on a.A_product_code=c.C_product_code
full join(
select D_product_code,
D_product_num=sum(case when D_date=@date then D_product_num else 0 end),
D_count=sum(case when D_date between left(@date,6)+'01' and @date then D_product_num else 0 end),
D_Total=sum(D_product_num)
from A
where D_date<=@date
having sum(case when D_date=@date then D_product_num else 0 end)<>0
)d on a.A_product_code=d.D_product_code
full join(
select E_product_code,
E_product_num=sum(case when E_date=@date then E_product_num else 0 end),
E_count=sum(case when E_date between left(@date,6)+'01' and @date then E_product_num else 0 end),
E_Total=sum(E_product_num)
from A
where E_date<=@date
having sum(case when E_date=@date then E_product_num else 0 end)<>0
)e on a.A_product_code=e.E_product_code
)b where p.product_code=b.product_code

[zjcxc]

--调用
exec p_qry '20050110'

[tdtjjiao]

你这个好麻烦啊。
不是一下就能搞定的啊。

[631799]

select a.*,a_count=isnull((select sum(A_product_num) from a where A_product_code=p.product_code),0)
,b_count=isnull((select sum(B_product_num) from b where B_product_code=p.product_code),0)
,c_count=isnull((select sum(C_product_num) from c where C_product_code=p.product_code),0)
,d_count=isnull((select sum(D_product_num) from d where D_product_code=p.product_code),0)
,e_count=isnull((select sum(E_product_num) from e where E_product_code=p.product_code),0)
from p

[rpass]

俺看着晕
帮你UP

[winternet]

up

[mlxboy]

zjcxc(邹建)大哥;
你的方法多次全连接
我的方法： (1) 选择发生业务的产品编号
select A_PruductID As ProductID
uinon
select B_PruductID As ProductID
union
select C_PruductID As ProductID
uinon
select D_PruductID As ProductID
union
select E_PruductID As ProductID
into #tmpProductID
（2）左连接 #tempProductID
select X.ProductID,ISNULL(A.Product_Num,0) as A.Product_Num,
ISNULL(B.Product_Num,0) As B.ProductNum,....
from #tempProductID X left join
(
select A_product_code,
A_product_num=sum(case when A_date=@date then A_product_num else 0 end),
A_count=sum(case when A_date between left(@date,6)+'01' and @date then A_product_num else 0 end),
A_Total=sum(A_product_num)
from A
where A_date<=@date
having sum(case when A_date=@date then A_product_num else 0 end)<>0
)a on X,ProductID=a.A_Product_code

left join

(
select B_product_code,
B_product_num=sum(case when B_date=@date then B_product_num else 0 end),
B_count=sum(case when B_date between left(@date,6)+'01' and @date then B_product_num else 0 end),
b_Total=sum(B_product_num)
from B
where B_date<=@date
having sum(case when B_date=@date then B_product_num else 0 end)<>0
)b on X,ProductID=b.B_Product_code
.........


左连接，全连接那个效率更高？

[mlxboy]

zjcxc(邹建)大哥; 问一个类似的问题

仓库报表
ID,Name,StartNum,InNum,OutNum,EndNum

入库表;
ID,Date,InNum

出库表

ID,Date OutNum

库存表

ID,Date,StockQuantity

可以通过全连接得到仓库报表么?

[wyb0026]

select product_code,product_name,product_weight
,A_product_num,A_COUNT,B_product_num,B_COUNT,C_product_num
,C_COUNT,D_product_num,D_COUNT,E_product_num,E_COUNT
from
(select COALESCE (A_product_num,B_product_num,C_product_num,D_product_num,E_product_num ) as product,
A_product_num,A_COUNT,B_product_num,B_COUNT,C_product_num
,C_COUNT,D_product_num,D_COUNT,E_product_num,E_COUNT
from
a full join b on A_Date=B_Date and A_product_code=B_product_code
full join c on A_Date=C_Date and A_product_code=C_product_code
full join d on A_Date=D_Date and A_product_code=D_product_code
full join e on A_Date=D_Date and A_product_code=E_product_code) as t0
left join p on t0.product=p.product_code

[mlxboy]

多表全连接会有问题，因为都是于第一个表全连接；
例子：

select * from t1
------------------
1 100.000000
3 1000.000000
4 1000.000000

select * from t2
--------------------
2 200.000000
4 1000.000000
5 1000.000000

select * from t3
-----------------
3 300.000000
5 1000.000000

select coalesce(t1.MatID,t2.MatID,t3.MatID) as MatID,
ISNULL(t1.StartNum,0) as StartNum,
ISNULL(t2.InNum,0) as InNum,
ISNULL(t3.OutNum,0) as OutNum

from t1 full join t2 on t1.MatID=t2.MatID
full join t3 on t1.MatID=t3.MatID

order by MatID

------------------------------------------------------------
1 100.000000 .000000 .000000
2 .000000 200.000000 .000000
3 1000.000000 .000000 300.000000
4 1000.000000 1000.000000 .000000

5 .000000 1000.000000 .000000
5 .000000 .000000 1000.000000

注意最后两条记录

[wyb0026]

再套一层group by 然后求和不久行了吗

[zjcxc]

--查询处理的存储过程
create proc p_qry
@date varchar(8) --要查询的天
as
--查询处理
SELECT p.product_code,p.product_name,p.product_weight,
A_product_期初=sum(A_product_期初),
A_product_num=sum(A_product_num),
A_count=sum(A_count),
A_product_期末=sum(A_product_期末),

B_product_期初=sum(B_product_期初),
B_product_num=sum(B_product_num),
B_count=sum(B_count),
B_product_期末=sum(B_count),

C_product_期初=sum(C_product_期初),
C_product_num=sum(C_product_num),
C_count=sum(C_count),
C_product_期末=sum(C_product_期末),

D_product_期初=sum(D_product_期初),
D_product_num=sum(D_product_num),
D_count=sum(D_count),
D_product_期末=sum(D_product_期末),

E_product_期初=sum(E_product_期初),
E_product_num=sum(E_product_num),
E_count=sum(E_count),
E_product_期末=sum(E_product_期末)
from p,(
select product_code=isnull(A_product_code,
isnull(B_product_code,
isnull(C_product_code,
isnull(D_product_code,E_product_code)))),
A_product_期初=isnull(a.A_Total,0)-isnull(a.A_product_num,0),
A_product_num=isnull(a.A_product_num,0),
A_count=isnull(a.A_count,0),
A_product_期末=isnull(a.A_Total,0),

B_product_期初=isnull(b.B_Total,0)-isnull(b.B_product_num,0),
B_product_num=isnull(b.B_product_num,0),
B_count=isnull(b.B_count,0),
B_product_期末=isnull(b.B_Total,0),

C_product_期初=isnull(c.C_Total,0)-isnull(c.C_product_num,0),
C_product_num=isnull(c.C_product_num,0),
C_count=isnull(c.C_count,0),
C_product_期末=isnull(c.C_Total,0),

D_product_期初=isnull(d.D_Total,0)-isnull(d.D_product_num,0),
D_product_num=isnull(d.D_product_num,0),
D_count=isnull(d.D_count,0),
D_product_期末=isnull(d.D_Total,0),

E_product_期初=isnull(e.E_Total,0)-isnull(e.E_product_num,0),
E_product_num=isnull(e.E_product_num,0),
E_count=isnull(e.E_count,0),
E_product_期末=isnull(e.E_Total,0)
from(
select A_product_code,
A_product_num=sum(case when A_date=@date then A_product_num else 0 end),
A_count=sum(case when A_date between left(@date,6)+'01' and @date then A_product_num else 0 end),
A_Total=sum(A_product_num)
from A
where A_date<=@date
group by A_product_code
having sum(case when A_date=@date then A_product_num else 0 end)<>0
)a
full join(
select B_product_code,
B_product_num=sum(case when B_date=@date then B_product_num else 0 end),
B_count=sum(case when B_date between left(@date,6)+'01' and @date then B_product_num else 0 end),
B_Total=sum(B_product_num)
from A
where B_date<=@date
group by A_product_code
having sum(case when B_date=@date then B_product_num else 0 end)<>0
)b on a.A_product_code=b.B_product_code
full join(
select C_product_code,
C_product_num=sum(case when C_date=@date then C_product_num else 0 end),
C_count=sum(case when C_date between left(@date,6)+'01' and @date then C_product_num else 0 end),
C_Total=sum(C_product_num)
from A
where C_date<=@date
group by A_product_code
having sum(case when C_date=@date then C_product_num else 0 end)<>0
)c on a.A_product_code=c.C_product_code
full join(
select D_product_code,
D_product_num=sum(case when D_date=@date then D_product_num else 0 end),
D_count=sum(case when D_date between left(@date,6)+'01' and @date then D_product_num else 0 end),
D_Total=sum(D_product_num)
from A
where D_date<=@date
group by A_product_code
having sum(case when D_date=@date then D_product_num else 0 end)<>0
)d on a.A_product_code=d.D_product_code
full join(
select E_product_code,
E_product_num=sum(case when E_date=@date then E_product_num else 0 end),
E_count=sum(case when E_date between left(@date,6)+'01' and @date then E_product_num else 0 end),
E_Total=sum(E_product_num)
from A
where E_date<=@date
group by A_product_code
having sum(case when E_date=@date then E_product_num else 0 end)<>0
)e on a.A_product_code=e.E_product_code
)b where p.product_code=b.product_code
group by p.product_code,p.product_name,p.product_weight

[milu626]

感谢zjcxc(邹建) 和多位好心人的帮助，郁闷这些日子，终于给解决了。由于sql的查询结果跟我想得有些不同，我想要的是还要和其他几个表连接，所以没能用上以上代码，我就换了个思路做的，这个问题比较繁琐，也就没在麻烦大家。让大家久等了！谢谢！！！