求一报表查询sql语句的写法.急呀.

[Juchiyufei]

有三个表如下所示:

Table: A

ProudctNO StockPrice
-------------------- ---------------
MIAA0000002 9.5555
MIAA0000001 8.1234
MIAA0000001 9.5555
MIAA0000003 8.1234
MIAA0000002 1.4133
MIAA0000001 9.5555
MIAA0000001 1.2000
MIAA0000001 1.1000
MIAA0000002 2.0000
MIAA0000002 2.0000
MIAA0000001 9.6563
MIAA0000001 968.6530

table B:

ProudctNO SalePrice
-------------------- ---------------
MIAA0000002 8.1234
MIAA0000001 9.5555
MIAA0000002 8.1234
MIAA0000001 9.5555
MIAA0000002 8.1234
MIAA0000001 1.1000
MIAA0000001 1.2000
MIAA0000003 .0000
MIAA0000002 .0000
MIAA0000001 .0000
MIAA0000008 2.0000
MIAA0000007 1.0000

table C:

ProudctNO Name
-------------------- ------------------------
MIAA0000001 a
MIAA0000002 a
MIAA0000003 a
MIAA0000004 x24c45
MIAA0000005 x24c45
MIAA0000006 22222222243
MIAA0000007 22222
MIAA0000008 23434
MIAA0000009 dd
MIAA0000010 UC3842BN

报表需要显示以下四个字段:
ProudctNO ProudctName, sum_StockPrice, sum(SalePrice)

好像有点简单，我是用两个查询语句来实现的,但总觉得效率方面不高，有没有什么方法用一个查询实现（视图,存储过程，sql语句）之类的，并且又能查到所有的数据.


谢谢.

[Juchiyufei]

dawugui(潇洒老乌龟)
===========
恭喜你，老乌龟，回答完全正确，现在正在测效率.

[Juchiyufei]

现在又有问题了，要同时能支持两种数据库，MSSQL与ACCESS，如果不行的话，就只有分开写了。

[dawugui]

access?
不知道行不行.我基本不用OFFICE这个数据库.

[Juchiyufei]

你用了isnull关键字，肯定不能在access里用了。

因为我这个系统要分版本，就只有试用版本的才会用access的.

[dawugui]

那你找找access是否有对应的语句?

[Juchiyufei]

select isnull(t1.ProductNO,t2.ProductNO) as ProductNO , isnull(t1.name,t2.name) as name,t1.sum_StockPrice,t2.sum_SalePrice from
(
select c.ProductNO,c.Name,m.sum_StockPrice from c,
(select ProductNO,sum(StockPrice) as sum_StockPrice from A group by ProductNO) m
where c.ProductNO=m.ProductNO
) t1 full join
(
select c.ProductNO,c.Name,n.sum_SalePrice from c,
(select productno,sum(SalePrice) as sum_SalePrice from B group by ProductNO) n
where c.ProductNO=n.ProductNO
) t2
on t1.ProductNO = t2.ProductNO

我会把里面的name去掉，只保留NO,两个price,写成视图。因为显示的时候还需要显示其它的产品信息的，如果放在里去做连接分组求和的话，代价有点大，最后再用这个视图去和产品表做NO等值连接.

你挺厉害的。:)

[fcuandy]

那么直接将我写在3楼的语句用inner join即可.

[fcuandy]

看错了.

select x.productno,y.name,sst,ssa
from
(
select distinct productno from ta
union
select distinct productno from tb
) x
left join
(select proudctNo,sum(stockprice) ssp from ta group by proudctNo) b
on x.proudctNO=b.proudctNo
left join
(select proudctNo,sum(saleprice) ssa from tb group by proudctNo) c
on x.proudctNo=c.proudctNo
left join tc y
on x.productno=y.productno

[Juchiyufei]

select a.proudctNo,a.Name,sst, ssa from tc a
inner join
(select proudctNo,sum(stockprice) ssp from ta group by proudctNo) b
on a.proudctNO=b.proudctNo
inner join
(select proudctNo,sum(saleprice) ssa from tb group by proudctNo) c
on a.proudctNo=c.proudctNo

楼上的，你好强呀。

另外,楼主把product拼错了,害我改了好多次语句.
========
不好意思呀。

[fcuandy]

那个语句是有bug的,只会取a,b表的交集. 而你表中的数据恰好a,b中都是一样存在的productno,所以看不出来问题.

要用我第二次写的那样,才会取并集.

[Juchiyufei]

ModelInfoNO StockPrice table A
-------------------- ---------------
MIAA0000001 5.0000
MIAA0000001 5.0000
MIAA0000002 4.0000
MIAA0000002 4.0000
MIAA0000003 3.0000
MIAA0000003 3.0000
MIAA0000004 2.0000
MIAA0000004 2.0000
MIAA0000005 1.0000
MIAA0000005 1.0000

(10 row(s) affected)

ModelInfoNO SalePrice table B
-------------------- ---------------
MIAA0000001 15.0000
MIAA0000001 15.0000
MIAA0000002 14.0000
MIAA0000002 14.0000
MIAA0000003 13.0000
MIAA0000003 13.0000
MIAA0000004 12.0000
MIAA0000004 12.0000
MIAA0000005 11.0000
MIAA0000005 11.0000

(10 row(s) affected)

ModelInfoNO ModelNO Sum_StockPrice Sum_SalePrice
-------------------- -------------------------------------------------- ---------------------------------------- ----------------------------------------
MIAA0000001 a 10.0000 30.0000
MIAA0000002 a 8.0000 28.0000
MIAA0000003 a 6.0000 26.0000
MIAA0000004 x24c45 4.0000 24.0000
MIAA0000005 x24c45 2.0000 22.0000

(5 row(s) affected)

[allright_flash]

marco08(天道酬勤)
是不是刘兵？

[Juchiyufei]

哪里有资料讲select语句的，很全，很深，很细的那种？

[qinhl99]

select a.proudctNo,a.Name,sst, ssa from tc a
left join
(select proudctNo,sum(stockprice) ssp from ta group by proudctNo) b
on a.proudctNO=b.proudctNo
left join
(select proudctNo,sum(saleprice) ssa from tb group by proudctNo) c
on a.proudctNo=c.proudctNo

是正确的！

[linnansong]

学习了……

[hansyswin]

mark

[Radar2006]

up，这个版不错

[tangqijun199]

select A.ProudctNO,Max(Name) as Name,Sum(Isnull(StockPrice,0)) as sum_StockPrice,
...

你这里用到Max(Name)是什么意思? 笔误?
=====================================
没有group by的列都要聚合的。这个地方不管用max,还是min结果都是一样了。没有错列，呵呵。

[tcsc1981]

up

[Juchiyufei]

高手就是高手呀.