sql相同表不同查询条件合并显示

关键字:FULL JOIN
只要其中某个表存在匹配,FULL JOIN 关键字就会返回行。
select a.createtime, ISNULL(lp, 0) lp , ISNULL(hp, 0) hp
 from (
	select  convert(nvarchar(10),cCreateTime,23) as createtime,count(iTotalAmount) as lp
	from vat_inv_standard 
	where iCharacter = 0 and iStatus = 0 and DateDiff(dd,cCreateTime,getdate())<=7
	group by convert(nvarchar(10),cCreateTime,23)
) A FULL JOIN (
	select convert(nvarchar(10),cCreateTime,23) as createtime,count(iTotalAmount) as hp 
	from vat_inv_standard 
	where iCharacter = 1 and iStatus = 0 and DateDiff(dd,cCreateTime,getdate())<=7
	group by convert(nvarchar(10),cCreateTime,23)
) B on  a.createtime =  b.createtime 

详细教程:http://www.w3school.com.cn/sql/sql_join_full.asp


展开阅读全文

没有更多推荐了,返回首页