表1:CARDINFO
字段 +测试数据:
_id CardId CardType CardOwner CardOwnerPhone 等字段
1 111111111 1 xxx 13888888888
2 222222222 2 AAA 13555555555
3 333333333 1 ccc 13555555544
表二 SIGN
字段+测试数据
_id CardId SignTime
1 111111111 2014-01-10 20:33:00
2 111111111 2014-01-25 20:33:00
3 111111111 2014-02-11 10:15:00
4 111111111 2014-02-12 22:35:00
5 111111111 2014-02-13 12:03:00
6 111111111 2014-02-14 07:32:00
7 222222222 2014-01-10 20:33:00
8 222222222 2014-01-25 20:33:00
9 222222222 2014-02-11 10:15:00
10 222222222 2014-02-12 22:35:00
11 222222222 2014-02-13 12:03:00
我要统计2014年1月到2月的总计数据
结果如下:(按测试数据)
CardId CardType CardOwner CardOwnerPhone Count
111111111 1 XXX 13888888888 4
222222222 2 AAA 13555555555 3
333333333 1 ccc 13555555544 0
我写了一句SQL,查询统计:
SELECT CARDINFO.CardID,CARDINFO.CardType,CARDINFO.CardOwner,CARDINFO.CardOwnerPhone,count() FROM CARDINFO inner join SIGN ON SIGN.CardID=CARDINFO.CardID WHERE SIGN.SignTime BETWEEN '2011-01-01' AND '2011-02-30' GROUP BY SIGN.CardID
然后查出来的数据是
CardId CardType CardOwner CardOwnerPhone Count
111111111 1 XXX 13888888888 4
222222222 2 AAA 13555555555 3
少统计了签到数据为0哪一项
333333333 1 ccc 13555555544 0
请我我怎么才能一起统计上去?还是我上面本身写的sql语句就有问题,请指教
------解决方案--------------------
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