跪求,查询的一条SQL语句!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
现有四个表,如下:
销售记录头表:(ID,会员ID,.)
产品销售表(销售记录头表ID,ID,产品编号.数量,售价,)
疗程卡销售表(销售记录头表ID,ID,编号.数量,售价,)
服务销售表(销售记录头表ID,ID,服务价)
问题:输入会员ID,
查询些会员 产品销售多少 疗程卡销售多少 服务销售多少
[解决办法]
是用ID關聯嗎？
Select
A.会员ID,
IsNull(B.产品销售, 0) As 产品销售,
IsNull(C.疗程卡销售, 0) As 疗程卡销售,
IsNull(D.服务销售, 0) As 服务销售
From
销售记录头表 A
Left Join
(Select ID, Count(ID) As 产品销售 From 产品销售表 Group By ID) B
On A.ID = B.ID
Left Join
(Select ID, Count(ID) As 疗程卡销售 From 疗程卡销售表 Group By ID) C
On A.ID = C.ID
Left Join
(Select ID, Count(ID) As 服务销售 From 服务销售表 Group By ID) D
On A.ID = D.ID
[解决办法]
select t1.会员ID, sum(isnull(t2.数量,0) * t2.售价), sum(isnull(t3.数量,0) * t3.售价), sum(isnull(t3.数量,0) * t3.售价)
from 销售记录头表 t1
inner join 产品销售表 t2 on t1.id=t2.销售记录头表ID
inner join 疗程卡销售表 t3 on t1.id=t3.销售记录头表ID
inner join 服务销售表 t4 on t1.id=t4.销售记录头表ID
where t1.会员ID=@会员ID
group by t1.会员ID
[解决办法]
select t1.会员ID, sum(isnull(t2.数量,0) )as 产品销售, sum(isnull(t3.数量,0) )as 疗程卡销售, sum(isnull(t3.数量,0) ) as 服务销售
from 销售记录头表 t1
inner join 产品销售表 t2 on t1.id=t2.销售记录头表ID
inner join 疗程卡销售表 t3 on t1.id=t3.销售记录头表ID
inner join 服务销售表 t4 on t1.id=t4.销售记录头表ID
where t1.会员ID=@会员ID
group by t1.会员ID
[解决办法]
nolast02(小明) ( ) 信誉：100 2007-08-18 11:01:01 得分: 0
select t1.会员ID, sum(isnull(t2.数量,0) * t2.售价), sum(isnull(t3.数量,0) * t3.售价), sum(isnull(t3.数量,0) * t3.售价)
from 销售记录头表 t1
inner join 产品销售表 t2 on t1.id=t2.销售记录头表ID
inner join 疗程卡销售表 t3 on t1.id=t3.销售记录头表ID
inner join 服务销售表 t4 on t1.id=t4.销售记录头表ID
where t1.会员ID=@会员ID
group by t1.会员ID
------------
這種寫法，貌似可以，但統計出的數量會遠遠比實際數量多。
這樣關聯，會產生重復數據。
另外，都使用了inner join，還使用isnull有什麼作用。
