个人收藏的SQL语句【T-SQL】

----查询a表中有b表中没有的数据

select distinct a.科目编码,a.科目名称,'',1 from [2007] a left outer join account b
on a.科目编码=b.科目编码
where b.科目编码 is null

--凭证中有科目代码，科目表没有的
select distinct a.科目代码,a.科目名称,left(a.科目代码,1)as 科目性质,case left(a.科目代码,1) when 1 then 1
when 2 then -1
when 3 then -1
when 4 then 1
when 5 then -1
else 1
end as 借贷方向,0 as 往来核算  from voucher a
left outer join account b
on a.科目代码=b.科目代码
and b.科目代码 is null

 

--查询重复数据
select * from aaa where col001 in(select col001 from aaa group by col001 having count(*)>1 )