select memNo,isnull((select count(x_memNo) from t_xf where x_memNo=t_member.x_memNo),0) as 消費次數 from t_member
declare @t table(memNo varchar(10),memName varchar(10)) insert into @t select '0001','亚昂他' union all select '0002','赤字'declare @a table(x_memNo varchar(10),x_money money) insert into @a select '0001',100.0000select a.memNo,count(b.x_memNo) as [count] from @t a,@a b where a.memNo*=b.x_memNo group by a.memNoselect a.memNo,count(b.x_memNo) as [count] from @t a left join @a b on a.memNo=b.x_memNo group by a.memNo
use pubs ---测试用临时数据--------------------------------------------- declare @Ta table(memNO varchar(10),memName varchar(10)) insert @Ta select '0001','亚昂他' union all select '0002','赤字'Declare @Tb table(x_memNo varchar(10),x_money smallmoney) insert @Tb select '0001',100.0000select * from @Ta select * from @Tb -------------------------------------------------- --完成如上所示查询的Select语句 select A.*,count(B.x_money) from @Ta A left join @Tb B on A.memno=B.x_memNo group by A.memno,A.memname order by A.memno
insert into @t select '0001','亚昂他'
union all select '0002','赤字'declare @a table(x_memNo varchar(10),x_money money)
insert into @a select '0001',100.0000select a.memNo,count(b.x_memNo) as [count] from @t a,@a b where a.memNo*=b.x_memNo group by a.memNoselect a.memNo,count(b.x_memNo) as [count] from @t a left join @a b on a.memNo=b.x_memNo group by a.memNo
---测试用临时数据---------------------------------------------
declare @Ta table(memNO varchar(10),memName varchar(10))
insert @Ta select '0001','亚昂他'
union all select '0002','赤字'Declare @Tb table(x_memNo varchar(10),x_money smallmoney)
insert @Tb select '0001',100.0000select * from @Ta
select * from @Tb
--------------------------------------------------
--完成如上所示查询的Select语句
select A.*,count(B.x_money)
from @Ta A left join @Tb B
on A.memno=B.x_memNo
group by A.memno,A.memname
order by A.memno