select a.卡名,b.总数,b.已出售,b.未出售,a.isauto,a.issale
from(
select cardid,卡名=cardname,isauto=sum(isauto),issale=sum(issale)
from a
group by cardid,cardname
)a join(
select cardid,总数=count(*)
,已出售=sum(case when orderid is null then 0 else 1 end)
,未出售=sum(case when orderid is null then 1 else 0 end)
from b
group by cardid
)b on a.cardid=b.cardid
from(
select cardid,卡名=cardname,isauto=sum(isauto),issale=sum(issale)
from a
group by cardid,cardname
)a join(
select cardid,总数=count(*)
,已出售=sum(case when orderid is null then 0 else 1 end)
,未出售=sum(case when orderid is null then 1 else 0 end)
from b
group by cardid
)b on a.cardid=b.cardid
已出售=sum(case when orderid is null then 0 else 1 end)
,未出售=sum(case when orderid is null then 1 else 0 end)为什么只判断is null 然后then 0 else 1就可以出来了呢?!
谢谢!
写存储过程吧.