一个非常复杂的SQL,请高手进入,帮我解决!!

handyworkroom 2003-10-19 09:52:08
有三个表
card
card_id,name
001 wang
002 zhang
003 zhao
004 li

store
store_id,card_id,storetype,money,storecent
000001 002 XC 200 20
000002 001 ZC 100 10
000003 002 HE 365 30
000004 003 XC 252 20
000005 003 SB 350 30
000006 001 HE 110 10
000007 002 XC 460 40
usecent
use_id,card_id,usecent
000001 001 26
000002 003 31
000003 001 13

现在想查询出的表如下
card_id name XC ZC HE SB usecent TOTALcent TOTALmoney
001 wang 10 30 -39 1 210
002 zhang 60 10 70 1025
003 zhao 20 30 -31 19 602
004 li
这里要用到了交叉表,可是后面要加上,usecent和TOTALcent(剩余积分),TOTALmoney(消费总额)
请大侠指点!!谢谢
...全文
29 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
txlicenhe 2003-10-20
  • 打赏
  • 举报
回复
看到有贴子说: 子查询比join的效率还要高。
wzh1215 2003-10-19
  • 打赏
  • 举报
回复

select a.card_id,a.name,isnull(convert(varchar(20),(select sum(b.storecent) from store b where a.card_id=b.card_id and b.storetype='XC')),'') as XC,
isnull(convert(varchar(20),(select sum(h.storecent) from store h where a.card_id=h.card_id and h.storetype='ZC')),'') as ZC,
isnull(convert(varchar(20),(select sum(f.storecent) from store f where a.card_id=f.card_id and f.storetype='HE')),'') as HE,
isnull(convert(varchar(20),(select sum(g.storecent) from store g where a.card_id=g.card_id and g.storetype='SB')),'') as SB,
isnull(convert(varchar(20),(select -sum(e.usecent) from usecent e where a.card_id=e.card_id)),'') as usecent,
(isnull(convert(decimal(18,2),(select sum(j.storecent) from store j where j.card_id=a.card_id)),0)-
isnull(convert(decimal(18,2),(select sum(e.usecent) from usecent e where a.card_id=e.card_id)),0)) as TOTALcent,
isnull(convert(varchar(20),(select sum(i.money) from store i where i.card_id=a.card_id)),'') as TOTALmoney
from card a

结果是你上面的TOTALcent有点错误!
SuperGam 2003-10-19
  • 打赏
  • 举报
回复
select a.card_id,a.Card_name,m1.xc,m2.zc,m3.he,m4.sb,b.usecent,
c1.storecent-c2.usecent as totalcent,d.totalmoney
from
(select card_id,card_name from card ) as a,
(select card_id,sum(storecent) as xc from store where storetype='xc' group by card_id ) as m1,
(select card_id,sum(StoreCent) as zc from store where storetype='zc' group by card_id ) as m2),
(select card_id,sum(storecent) as he from store where storetype='he' group by card_id ) as m3,
(select card_id,sum(storecent) as sb from store where storetype='sb' group by card_id ) as m4,
(Select Card_id,0-sum(usecent) as usecent from usecent groupy by Card_id) as b,
(Select care_id,sum(i.storecent) as storecent from store grouy by card_id) as c1,
(Select care_id,sum(i.usecent) as usecent from store group by card_id) as c2,
(Select card_id,sum(money) as totalmoney from store group by cardid) as d
where
a.card_id*=m1.card_id and a.card_id*=m2.card_id and a.card_id*=m3.card_id
and a.card_id*=m4.card_id and a.card_id*=b.card_id and a.card_id*=d.card_id
and a.card_id*=c1.card_id and a.card_id*=c2.card_id;

wzh1215 2003-10-19
  • 打赏
  • 举报
回复
帮你UP一下
wzh1215 2003-10-19
  • 打赏
  • 举报
回复
跟据你要求只能这样了!其实也并不是很复杂!
handyworkroom 2003-10-19
  • 打赏
  • 举报
回复
子查询太多,是不是不太好呀?
各位大侠,有没有好一点方法了,少一点的子查询
zuozl 2003-10-19
  • 打赏
  • 举报
回复
学习
playyuer 2003-10-19
  • 打赏
  • 举报
回复
001 wang 10 30 -39 1 210
002 zhang 60 10 70 1025


查询结果 30 和10 似乎位置颠倒了!
playyuer 2003-10-19
  • 打赏
  • 举报
回复
select *
,(select sum(storecent) from store where storetype = 'XC' and card_id = card.card_id ) as XC
,(select sum(storecent) from store where storetype = 'ZC' and card_id = card.card_id ) as ZC
,(select sum(storecent) from store where storetype = 'HE' and card_id = card.card_id ) as HE
,(select sum(storecent) from store where storetype = 'SB' and card_id = card.card_id ) as SB
,-(select sum(usecent) from usecent where card_id = card.card_id) as usecent
,(select sum(storecent) from store where card_id = card.card_id )
-(select sum(usecent) from usecent where card_id = card.card_id) as TOTALcent
,(select sum([money]) from store where card_id = card.card_id ) as TOTALmoney
from card

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧