select a.cardname,a.Price,(case when a.price<>0 then case when b.mannum<>0 then (b.cash/b.mannum) else 0 end )/a.price else 0 end )as '折扣率',sum(b.mannum),sum(b.cash)
From card_type a,card_publish b
Where a.cardtype=b.cardtype and convert(char,b.usedatetime,112)='20030301' and a.price<>0
group by a.cardname,a.Price,(case when a.price<>0 then case when b.mannum<>0 then (b.cash/b.mannum) else 0 end )/a.price else 0 end )
From card_type a,card_publish b
Where a.cardtype=b.cardtype and convert(char,b.usedatetime,112)='20030301' and a.price<>0
group by a.cardname,a.Price,(case when a.price<>0 then case when b.mannum<>0 then (b.cash/b.mannum) else 0 end )/a.price else 0 end )
From card_type a,card_publish b
Where a.cardtype=b.cardtype and convert(char,b.usedatetime,112)='20030301' and a.price<>0
group by a.cardname,a.Price,(b.cash/b.mannum)/(a.price=Case when (cardtype=6)or(cardtype=9) 1 else a.Price)
From card_type a,card_publish b
Where a.cardtype=b.cardtype and convert(char,b.usedatetime,112)='20030301' and a.price<>0
group by a.cardname,a.Price,(b.cash/b.mannum)/(case when cardtype='6' then 1 when cardtype='9' then 100 else a.price end)
select a.cardname,a.Price,(case when a.price<>0 then (case when b.mannum<>0 then (b.cash/b.mannum) else 0 end )/a.price else 0 end )as '折扣率',sum(b.mannum),sum(b.cash)
From card_type a,card_publish b
Where a.cardtype=b.cardtype and convert(char,b.usedatetime,112)='20030301'
group by a.cardname,a.Price,(case when a.price<>0 then (case when b.mannum<>0 then (b.cash/b.mannum) else 0 end )/a.price else 0 end )我用上面的语句调试通过了,但还有一个问题,由于在表中有两个PRICE为零的票种,而统计出来的结果只有一个为零的记录,我不知道哪出了错?
还有不知是我的机器慢还是我的语句的问题,执行起来速度很慢(表中大概有一百万条记录)。
你的返回结果和crazyfor一样,耗时12秒,
crazyfor 的耗时13秒。
From card_type a,card_publish b
Where a.cardtype=b.cardtype and convert(char,b.usedatetime,112)='20030301'
--and a.price<>0
group by a.cardname,a.Price,(b.cash/b.mannum)/(case when cardtype in (6,9) then 1 else a.price end)