create table t_corporation(name_ch varchar(100),account varchar(100),password varchar(100)) insert into t_corporation select '企业1','账号1','密码1' union all select '企业2','账号2','密码2' create table t_data(name_ch varchar(100),year_i int,month_i int) insert into t_data select '企业1',2008,1 union all select '企业1',2008,5 union all select '企业2',2008,2 union all select '企业2',2008,6 declare @year int,@month int select @year=2008,@month=5 select a.name_ch,b.year_i from t_corporation a left join t_data b on a.name_ch=b.name_ch and b.year_i=@year and b.month_i=@month where b.year_i is null
declare @year int,@month int select @year=2008,@month=5 select a.name_ch,@year,@month from t_corporation a left join t_data b on a.name_ch=b.name_ch and b.year_i=@year and b.month_i=@month where b.year_i is null -- 替换一下
declare @year int,@month int select @year=2008,@month=5 select b.name_ch,@year,@month from t_corporation b where not exists ( select name_ch from t_data where year_i=@year and month_i=@month and name_ch=b.name_ch )-- 这种效率更高一些
公司名 companyname
报表日期 years
报表月份 month
各个数据字段 。公司信息表的字段
公司名 comname
其他信息 。谢谢了!
例如有100家企业,而只有其中的50家上报了今年9月份的报表,其他50家没有上报今年9月份的报表,查询出哪50家没有上的企业名称和未上报的年月。
有好的答案我+分!
insert into t_corporation
select '企业1','账号1','密码1' union all
select '企业2','账号2','密码2'
create table t_data(name_ch varchar(100),year_i int,month_i int)
insert into t_data
select '企业1',2008,1 union all
select '企业1',2008,5 union all
select '企业2',2008,2 union all
select '企业2',2008,6 declare @year int,@month int
select @year=2008,@month=5
select a.name_ch,b.year_i from t_corporation a
left join t_data b
on a.name_ch=b.name_ch and b.year_i=@year and b.month_i=@month
where b.year_i is null
select @year=2008,@month=5
select a.name_ch,@year,@month from t_corporation a
left join t_data b
on a.name_ch=b.name_ch and b.year_i=@year and b.month_i=@month
where b.year_i is null
--
替换一下
select @year=2008,@month=5
select b.name_ch,@year,@month from t_corporation b where not exists
(
select name_ch from t_data
where year_i=@year and month_i=@month and name_ch=b.name_ch
)--
这种效率更高一些