重复信息中case when exists 的应用<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
create table #a(Fname varchar(30),fqun decimal(18,2),fclass int )
create table #b ( fclass int,name varchar(30))
insert into #a
select 'a',20,1
union all
select 'a',20,2
union all
select 'a',20,3
union all
select 'B',10,1
union all
select 'B',10,2
insert into #b
select 1,'A'
union
select 2,'B'
union
select 3,'C'
select a.*,b.name from #a a inner join #b b on a.fclass = b.fclass
要求输出格式为
Fname fqun fclass name
------------------------------ -------------------- ----------- ------------------------------
a 20.00 1 A
2 B
3 C
B 10.00 1 A
2 B
-解决-
create table #a(Fname varchar(30),fqun decimal(18,2),fclass int )
create table #b ( fclass int,name varchar(30))
insert into #a
select 'a',20,1
union all
select 'a',20,2
union all
select 'a',20,3
union all
select 'B',10,1
union all
select 'B',10,2
insert into #b
select 1,'A'
union
select 2,'B'
union
select 3,'C'
---select a.*,b.name from #a a inner join #b b on a.fclass = b.fclass
select Fnane=(case when exists (select 1 from #a where a.fname = fname and fclass<a.fclass) then '' else a.fname end)
,fqun=(case when exists (select 1 from #a where a.fqun = fqun and fclass<a.fclass) then '' else ltrim(a.fqun) end)
,a.fclass
,b.name
from #a a
inner join #b b
on a.fclass =b.fclass
order by a.Fname,a.fclass
drop table #a,#b
--------------------------
a 20.00 1 A
2 B
3 C
B 10.00 1 A
2 B
这里需要注意的就是case when exists(select 1 from #a …..
这里的是一个嵌套查询,需要处理的字段里层与外层sql 相等,然后再加一条件,不处理的条件做大小比较。根据 大于 或 小于 可以控制为相同记录的第一条记录,或最后一条记录
如果外层 小于 里层,就是第一条,反之,就是最后一条。
例子:
declare @t table(F1 varchar(8),F2 varchar(8))
insert into @t values('01','a ')
insert into @t values('01','aa ')
insert into @t values('02','b ')
insert into @t values('02','bb ')
insert into @t values('02','bbb')
--select * from @t
select F1=(Case when exists (select 1 from @t where F1=a.F1 and F2<a.F2) then '' else F1 end)
,a.F2
from @t a
order by a..F1,a.F2
--Result--
------------
01 a
aa
02 b
bb
bbb
分享到:
相关推荐
NULL 博文链接:https://576017120.iteye.com/blog/1624774
/*SQL Server数据库字典(查询所有的表结构)*/ SELECT TOP 100 PERCENT --a.id, CASE WHEN a.colorder = 1 THEN d.name ELSE '' END AS 表名, CASE WHEN a.colorder = 1 THEN isnull(f....CASE WHEN EXISTS (SELECT 1
主键 = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in ( SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND ...
主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in ( SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid ))) ...
1、MSSQL2000 代码如下: SELECT 表名 = case when a.colorder=1 then d.name ...case when COLUMNPROPERTY( a.id,a.name,’IsIdentity’)=1 then ‘√’else ” end, 主键 = case when exists(SELECT 1 FROM sysobjects
SELECT 表名=CASE WHEN a.colorder = 1 THEN d.name ELSE ” END, 表说明=CASE WHEN a.... a.name, ‘IsIdentity’) = 1 THEN ‘√’ ELSE ” END , 主键=CASE WHEN EXISTS ( SELECT 1 FROM dbo.sysindexes si INN
5.8 The Case Expression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101 5.9 The Scalar Expression Between Brackets . . . . . . . . . . . . . . . . 106 viii Contents 5.10 The Scalar...
- FIX: The TFlexRegularPolygon object clones incorrectly drawed in case when TFlexRegularPolygon have alternative brush (gradient, texture). - ADD: Add TFlexPanel.InvalidateControl virtual method ...
Print in a line Key X is duplicated where X already exists when being inserted. After all the insertions print out the B+ tree in a top-down lever-order format as shown by the samples.
TEMP\NeroInstallFiles\NEROXXXXXXXXXXXXXXXXX\ where XXXXXXXXXXXXXXXXX = build date 20120416094558220, or files extraction date, if such folder exists. 4. Patch file nero.mmstrial.msi, continue already...
64位版本的 PLSQL 正式版,只能运行在64位...File Save dialog could give incorrect "file exists" warning when using Windows Classic Theme Code Assistant now inserts view columns without quotes if not needed
18)..Fixed: Possible "Unit XYZ was compiled with a different version of ABC" when using packages 19)..Fixed: FastMM shared MM compatibility 20)..Fixed: Minor bugs in stack tracing (which usually ...
,100 * SUM(CASE WHEN C# = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '001' THEN 1 ELSE 0 END) AS 企业管理及格百分数 ,SUM(CASE WHEN C# = '002' THEN score ELSE 0 END)/SUM(CASE C# ...
(Bug) Fixed an issue where host and protocol were not compared case-insensitively when determining whether anAJAXrequest was local or remote (#6908) (Bug) Fixed an issue where the “clone” variable ...
本文向大家介绍了使用SQL语句提取数据库所有表的表名、字段名的实例代码,在SQLserver 中进行了测试,具体内容... CASE WHEN EXISTS (SELECT 1 FROM dbo.sysobjects WHERE Xtype = 'PK' AND Name IN (SELECT Nam
,100 * SUM(CASE WHEN C# = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '001' THEN 1 ELSE 0 END) AS 企业管理及格百分数 ,SUM(CASE WHEN C# = '002' THEN score ELSE 0 END)/SUM(CASE C# ...
(Bug) Fixed an issue where host and protocol were not compared case-insensitively when determining whether anAJAXrequest was local or remote (#6908) (Bug) Fixed an issue where the “clone” variable...
数据库1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数 (ˇˍˇ) 想~ select distinct s.S# 学号,s.Sname 学生姓名,s.Ssex 性别 ,convert(date,s.Sage) 出生日期,sc.score 分数 from Student s,SC sc,SC ...
if the file to which shall be renamed already exists, it will be overwritten if the other file content is equal. --unescape this option will remove this ugly % hex sequences from filenames and turn ...
查询数据库的状态信息: ------------------------------Data file size---------------------------- if exists (select * from tempdb.sys.all_objects where name like '%#dbsize%') drop table #dbsize create ...