语句如下:
1)select quid,cardtypeid,count(id) as idnum,(select count(id) from kaku where used=0 and regid='10451') as nosell,(select count(id) from kaku where used=1 and regid='10451') as selled from kaku where regid='10451' group by quid,cardtypeid2)select * from kaku where regid='10451'我实际1)想要达到的效果 是 nosell 和 selled 只显示对应cardtypeid里未售和已售的。但现在没有进一步筛选出来。具体的约束语句不知道改怎么写了。请高手赐教!!!
1)select quid,cardtypeid,count(id) as idnum,(select count(id) from kaku where used=0 and regid='10451') as nosell,(select count(id) from kaku where used=1 and regid='10451') as selled from kaku where regid='10451' group by quid,cardtypeid2)select * from kaku where regid='10451'我实际1)想要达到的效果 是 nosell 和 selled 只显示对应cardtypeid里未售和已售的。但现在没有进一步筛选出来。具体的约束语句不知道改怎么写了。请高手赐教!!!
select quid,cardtypeid,count(id) as idnum,
sum( case when used=0 then 1 else 0 end) as nosell,
sum( case when used=1 then 1 else 0 end) as selled
from kaku
where regid='10451'
group by quid,cardtypeid
select * from
(
select quid,cardtypeid,count(id) as idnum,regid,
sum( case when used=0 then 1 else 0 end) as nosell,
sum( case when used=1 then 1 else 0 end) as selled
from kaku
group by quid,cardtypeid,regid
)c
where regid='10451'
(
select quid,cardtypeid,count(id) as idnum,regid,
sum( case when used=0 then 1 else 0 end) as nosell,
sum( case when used=1 then 1 else 0 end) as selled
from kaku
group by quid,cardtypeid,regid
)c
where regid='10451'
select quid,cardtypeid,count(id) as idnum,
count(case when used=0 then 1 end) nosell,
count(case when used=1 then 1 end) nosell
from kaku
where regid='10451'
group by quid,cardtypeid