SELECT
CARDINFO.CardID, CARDINFO.CardType, CARDINFO.CardOwner,
CARDINFO.CardOwnerPhone, ISNULL(COUNT(1),0)
FROM
CARDINFO
LEFT JOIN [SIGN] ON SIGN.CardID=CARDINFO.CardID
WHERE
SIGN.SignTime BETWEEN '2011-01-01'
AND '2011-02-30'
GROUP BY
SIGN.CardID试试。
先不提这些,至少要改一下几点:
1、inner join 改成 left join
2、count()里面要放入一个右表的一个非null字段。可以这么写 count(SIGN.CardID)
3、如果是MSSQL,select要显示啥Group by后面必须加上啥:
GROUOP BY SIGN.CardID,CARDINFO.CardType,CARDINFO.CardOwner,CARDINFO.CardOwnerPhone
SELECT CARDINFO.CardID,CARDINFO.CardType,CARDINFO.CardOwner,CARDINFO.CardOwnerPhone,
isnull(s.qty,0) 'Count'
FROM CARDINFO
left join
(select CardID,
count(1) 'qty'
from [SIGN]
where SignTime between '2011-01-01' and '2011-02-30'
group by CardID) s on s.CardID=CARDINFO.CardID
select a.*,
isnull((select count(1) from SIGN where CardId = a.CardId group by CardId),0) as Count
from CardInfo a