我有两个表(字段各不相同),字段分别为table1 :sum1,year1 第二个表table2 :sum2,year2
假设表里的数据有
table1 table2
6100.3 2006 6000.5 2006
7188.5 2007 7188.5 2007
8800.5 2008 8800.9 2008
我想通过一条sql语句,对两表里的量按年份进行一次性比较,比较出的结果显示为
2006 不相等
2007 相等
2008 不相等请问我应该怎么写sql语句呢,我现没什么概念,大家帮帮忙吧
假设表里的数据有
table1 table2
6100.3 2006 6000.5 2006
7188.5 2007 7188.5 2007
8800.5 2008 8800.9 2008
我想通过一条sql语句,对两表里的量按年份进行一次性比较,比较出的结果显示为
2006 不相等
2007 相等
2008 不相等请问我应该怎么写sql语句呢,我现没什么概念,大家帮帮忙吧
Select tb1.year1,
Case When tb1.sum1=tb2.sum2 THEN '相等' ELSE '不相等' END AS Status
From table1 tb1
Left OUTER JOIN table2 tb2
on tb1.year1=tb2.year2
insert into #table1
select 6100.3 ,2006 union all
select 7188.5 ,2007 union all
select 8800.5 ,2008 create table #table2 (sum2 decimal(18,2),year2 int)
insert into #table2
select 6000.5 ,2006 union all
select 7188.5 ,2007 union all
select 8800.9 ,2008
select year1 ,case when sum1= sum2 then '相等'else '不相等' end result
from #table1 left join #table2 on year1=year2year1 result
----------- ------
2006 不相等
2007 相等
2008 不相等(3 行受影响)
zd,year2
22.0 2006
223.5 2006
210.5 2007
15.8 2007
13.83 2008
也就是我对table2表里原始的zd字经过一次求和(select sum(zd) AS sum2,year from table2 group by year2)后得到的记录,table2,sum2,year2请问这一语句怎么和你的 select year1 ,case when sum1= sum2 then '相等'else '不相等' end result from #table1 left join #table2 on year1=year2 关连起来呢,也就是你这句的sum2是经过一sql语句得来的
insert into #table1
select 6100.3 ,2006 union all
select 7188.5 ,2007 union all
select 8800.5 ,2008 create table #table2 (sum2 decimal(18,2),year2 int)
insert into #table2
select 22.0 ,2006 union all
select 223.5 , 2006 union all
select 210.5 , 2007 union all
select 15.8 ,2007 union all
select 13.83 , 2008
select year1 ,case when sum1= sum2 then '相等'else '不相等' end result
from #table1 left join
(
select year2,sum(sum2) sum2 from #table2
group by year2) table2
on year1=year2year1 result
----------- ------
2006 不相等
2007 不相等
2008 不相等(3 行受影响)