日期:2014-05-16 浏览次数:20639 次
_id CardId CardType CardOwner CardOwnerPhone 等字段
1 111111111 1 xxx 13888888888
2 222222222 2 AAA 13555555555
3 333333333 1 ccc 13555555544
_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
CardId CardType CardOwner CardOwnerPhone Count
111111111 1 XXX 13888888888 4
222222222 2 AAA 13555555555 3
333333333 1 ccc 13555555544 0
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
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