sID tID Name Name2
101 1 趋势服务 TEST
102 1 趋势服务 TEST
201 2 趋势服务 TEST
202 2 趋势服务 TEST
303 3 趋势服务 TEST
401 4 TEST
501 5 趋势服务 TEST
502 5 TEST
10101 101 趋势服务 TEST
10102 101 趋势服务 TEST这是表A的数据,我想要的是获取不重复的tID,select distinct(tID),还有要是当sID里的值与tID里的值相同时(比如sID 101=tID 101)该列就不获取,还有判断Name列要是没有值就去Name2里的值!
高手帮帮啊!快!
101 1 趋势服务 TEST
102 1 趋势服务 TEST
201 2 趋势服务 TEST
202 2 趋势服务 TEST
303 3 趋势服务 TEST
401 4 TEST
501 5 趋势服务 TEST
502 5 TEST
10101 101 趋势服务 TEST
10102 101 趋势服务 TEST这是表A的数据,我想要的是获取不重复的tID,select distinct(tID),还有要是当sID里的值与tID里的值相同时(比如sID 101=tID 101)该列就不获取,还有判断Name列要是没有值就去Name2里的值!
高手帮帮啊!快!
else Name end Name2 from table where sID<>tID
还是谢谢你的回答!
问题是要获取不重复的tID就是select distinct(tID),然后判断tID里的值是否有与sID里的值相同,如相同不获取该行,还有判断Name列是否为空,如为空则获取Name2的值
from table
where tID in (
select sID,tID,case when Name='' then Name2
else Name end Name2
from table
where sID<>tID
)
else [Name] end [Name] from #Table
where tid in (select distinct(tid) from #table)
and Tid not in (select sid from #Table)
2 趋势服务
3 趋势服务
4 TEST
5 TEST
5 趋势服务
else Name end Name2 from t1 where sid<>tid) as f group by
tid,name
select tid ,name from ( select sid,tid, case when Name='' then Name2
else Name end Name from t1 where sid<>tid) as f group by tid,name
5 趋势服务
假如说这两条数据,你想要那条,删除那条,你告诉我判断依据,
按你说的这两条是没办法取舍的,
5 趋势服务
获取第一个也就是5 TEST这条
else Name end Name from t1 where sid<>tid) as f group by tid,name分组去
else [Name] end [Name] from #Table
inner join (select min(sid) as sid ,tid from #table group by tid) T
on T.sid =#Table.Sid
and T.Tid not in (select sid from #Table)
select sID,tID,case when Name='' then Name2
else Name end as Name from test where sID<>tID and sID in (select MIN(sID) from test group by tid)可以实现,估计效率不高。
select T.tID,case when [Name]='' then Name2
else [Name] end [Name] from #Table
inner join (select min(sid) as sid ,tid from #table group by tid) T
on T.sid =#Table.Sid
and T.Tid not in (select sid from #Table)
else [Name] end [Name] from #Table
where sid in (select min(sid) from #table group by tid)
and Tid not in (select sid from #Table)
5 TEST
5 趋势服务
这两条数据,也有可能是第二个,不一定是第一个,现在有一个ID,
ID sID tID Name Name2
1 101 5 趋势 test
2 102 5 趋势2
如果获取Name就获取ID最小的,也就是进行对ID排序在获取,是不是直接在最后面加上order by ID就行!
else [Name] end [Name] from #Table
inner join (select min(id) as id ,tid from #table group by tid) T
on T.id =#Table.id
and T.Tid not in (select sid from #Table)
declare @table table (sID int,tID int,Name varchar(8),Name2 varchar(4))
insert into @table
select 101,1,'趋势服务','TEST' union all
select 102,1,'趋势服务','TEST' union all
select 201,2,'趋势服务','TEST' union all
select 202,2,'趋势服务','TEST' union all
select 303,3,'趋势服务','TEST' union all
select 401,4,null,'TEST' union all
select 501,5,'趋势服务','TEST' union all
select 502,5,null,'TEST' union all
select 10101,101,'趋势服务','TEST' union all
select 10102,101,'趋势服务','TEST'select tID,isnull(Name,Name2) as Name from @table t
where sID=(select min(sID) from @table where tID=t.tID)
and tID not in (select distinct sID from @table)
/*
tID Name
----------- --------
1 趋势服务
2 趋势服务
3 趋势服务
4 TEST
5 趋势服务
*/
declare @table table (sID int,tID int,Name varchar(8),Name2 varchar(4))
insert into @table
select 101,1,'趋势服务','TEST' union all
select 102,1,'趋势服务','TEST' union all
select 201,2,'趋势服务','TEST' union all
select 202,2,'趋势服务','TEST' union all
select 303,3,'趋势服务','TEST' union all
select 401,4,null,'TEST' union all
select 501,5,'趋势服务','TEST' union all
select 502,5,null,'TEST' union all
select 10101,101,'趋势服务','TEST' union all
select 10102,101,'趋势服务','TEST'select a.* from (
select tID,isnull(Name,Name2) as Name from @table t
where sID=(select min(sID) from @table where tID=t.tID)
) a left join (select distinct sID from @table) b
on a.tID=b.sID where sID is null
/*
tID Name
----------- --------
1 趋势服务
2 趋势服务
3 趋势服务
4 TEST
5 趋势服务
*/--not in的效率很差,建议去掉