用sql求两表的差异

wwfxgm 2020-06-20 08:12:47

题目要求如下：

表的结构和示例数据如下：

drop table if exists dbo.REPORT_CARD_A;

go

create table dbo.REPORT_CARD_A(

cid char(1) not null,

sid int not null,

score int not null

)







drop table if exists dbo.REPORT_CARD_B;

go

create table dbo.REPORT_CARD_B(

cid char(1) not null,

sid int not null,

score int not null

)



insert into dbo.REPORT_CARD_A

values('A',210,88),('A',211,89),('A',212,90),('A',213,91),('B',210,78),('B',211,79),('B',212,80),('B',213,81),('C',210,80),('C',211,78),('C',212,68)





insert into dbo.REPORT_CARD_B

values('A',210,88),('A',211,69),('A',212,90),('A',214,91),('B',210,78),('B',211,79),('B',214,91),('C',210,80),('C',211,78),('C',212,68),('C',214,45)

我写了好久，感觉功力还是差，没有办法，请大神帮忙。

...全文

368 11 打赏收藏转发到动态举报

写回复

用AI写文章

11 条回复

切换为时间正序

请发表友善的回复…

发表回复

唐诗三百首 2020-06-26

打赏
举报


create function dbo.getdiff(@sid int,@cid char(1))
returns int
as
begin
 declare @diff int

 select @diff=abs(isnull((select a.SCORE
	                      from dbo.REPORT_CARD_A a
	                      where a.SID=@sid and a.CID=@cid),0)
			      -
			      isnull((select b.SCORE
	                      from dbo.REPORT_CARD_B b
	                      where b.SID=@sid and b.CID=@cid),0))

 return @diff
end


select '学生号'=s.SID,
       '是否有差异'=iif(dbo.getdiff(s.SID,'A')
	                  +dbo.getdiff(s.SID,'B')
					  +dbo.getdiff(s.SID,'C')>0,'是','否'),
       'A课程差异'=dbo.getdiff(s.SID,'A'),
	   'B课程差异'=dbo.getdiff(s.SID,'B'),
	   'C课程差异'=dbo.getdiff(s.SID,'C')
 from (select distinct SID from dbo.REPORT_CARD_A
       union 
	   select distinct SID from dbo.REPORT_CARD_B) s

/*
学生号       是否有差异 A课程差异       B课程差异       C课程差异
----------- -----    ----------- ----------- -----------
210         否        0           0           0
211         是        20          0           0
212         是        0           80          0
213         是        91          81          0
214         是        91          91          45

(5 行受影响)
*/

wwfxgm 2020-06-26

打赏
举报

大致能写出一个结果就行，工作中，经常遇到找数据的差异。

wwfxgm 2020-06-26

打赏
举报

引用 7 楼唐诗三百首的回复:

5楼其实是2个问题喔. 这题的本身应该有问题, 不应让人猜测[成绩单A数据]和[成绩单B数据]应显示什么?具体以什么格式显示?

三百首版主，我如果不要成绩单A 、B数据，就这样的话，可以写出语句吗？

北冥小渔 2020-06-26

打赏
举报

看了版主的脚本很简洁，学习了，赞一个我自己也尝试写了一下还是，献丑了



--检索差异数据和差异值
SELECT 
	t.[sid] , 
	CASE WHEN t.A = 0 AND t.B = 0 AND t.C = 0 THEN '无差异' ELSE '有差异' END ifDiff ,
	t.A ,t.B ,t.C ,
	SPACE(20) scoreA ,
	SPACE(20) scoreB
	INTO #T1
FROM (
	SELECT 
		MAX(isnull(rca.sid,rcb.[sid])) [sid],
		MAX(CASE isnull(rca.cid,rcb.cid) WHEN 'A'  THEN ABS(isnull(rca.score,0)- isnull(rcb.score,0)) ELSE 0 END) A,
		MAX(CASE isnull(rca.cid,rcb.cid) WHEN 'B'  THEN ABS(isnull(rca.score,0)- isnull(rcb.score,0)) ELSE 0 END) B,
		MAX(CASE isnull(rca.cid,rcb.cid) WHEN 'C'  THEN ABS(isnull(rca.score,0)- isnull(rcb.score,0)) ELSE 0 END) C
	FROM REPORT_CARD_A AS rca 
	FULL JOIN REPORT_CARD_B AS rcb ON rca.cid = rcb.cid AND rca.[sid] = rcb.[sid]
	GROUP BY rca.[sid]
) t
ORDER BY t.[sid]

--将成绩A数据合并成行
SELECT #T1.[sid], 
STUFF((SELECT ','+CAST(score AS VARCHAR) FROM REPORT_CARD_A a1 WHERE a1.sid = a.sid
	   FOR XML PATH('')) ,1,1,'') scorea
INTO #T2	   
FROM  REPORT_CARD_A a
FULL JOIN #T1 ON a.[sid] = #T1.[sid]
GROUP BY a.sid , #T1.[sid]

--将成绩B数据合并成行
SELECT #T1.[sid], 
STUFF(( SELECT ','+CAST(score AS VARCHAR) FROM REPORT_CARD_B b1 WHERE b.sid = b1.sid
	   FOR XML PATH('')) ,1,1,'') scoreb
	INTO #T3	   
FROM  REPORT_CARD_B b 
FULL JOIN #T1 ON b.[sid] = #T1.[sid]
GROUP BY b.sid , #T1.[sid]

--更新最后scorea，scoreb数据
UPDATE #T1
SET    #T1.scoreA = CASE WHEN isnull(#T2.scorea,'')= '' THEN '' ELSE  #T2.scorea END , 
       #T1.scoreB = CASE WHEN isnull(#T3.scoreb,'')= '' THEN '' ELSE  #T3.scoreb END  
FROM   #T1, #T2, #T3
WHERE  #T1.[sid] = #T2.[sid] AND #T1.[sid] = #T3.[sid]

SELECT * FROM #T1

DROP TABLE #T1
DROP TABLE #T2
DROP TABLE #T3