有一个表的数据如下:
1,fff,ggg,555
2,7ullhio,wefcerte,9ihgyyu
1,,ggg,555
3,55533399,8kkhy,20d9
4,e,532,so0df,22888
4,,wwi84,edxd,kd在表里找出以下这二列(还有其他很多这样的情形)该怎么处理?
1,,ggg,555
4,,wwi84,edxd,kd补个说明就是第一字段相同这种情况下只要第二个字段为空就找出来
1,fff,ggg,555
2,7ullhio,wefcerte,9ihgyyu
1,,ggg,555
3,55533399,8kkhy,20d9
4,e,532,so0df,22888
4,,wwi84,edxd,kd在表里找出以下这二列(还有其他很多这样的情形)该怎么处理?
1,,ggg,555
4,,wwi84,edxd,kd补个说明就是第一字段相同这种情况下只要第二个字段为空就找出来
select *
from tb t
where exists (select 1 from tb where left(col,charindex(',',col)-1)=left(col,charindex(',',t.col)-1) and col <> t.col)
and left(substering(col,charindex(',',t.col),len(col)-charindex(',',t.col)+1),
charindex(',',left(substering(col,charindex(',',t.col),len(col)-charindex(',',t.col)+1))-1) = ''
select *
from tb t
where exists (select 1 from tb where left(col,charindex(',',col)-1)=left(col,charindex(',',t.col)-1) and col <> t.col)
and left(substering(col,charindex(',',col),len(col)-charindex(',',col)+1),
charindex(',',left(substering(col,charindex(',',col),len(col)-charindex(',',col)+1))-1) = ''
declare @t table
(id int,col1 varchar(8),col2 varchar(8),col3 varchar(7),col4 VARCHAR(5))
insert into @t
select 1,'fff','ggg','555',null union all
select 2,'7ullhio','wefcerte','9ihgyyu',null union all
select 1,NULL,'ggg','555',null union all
select 3,'55533399','8kkhy','20d9',null union all
select 4,'e','532','so0df','22888' union all
select 4,NULL,'wwi84','edxd','kd'select * from @t
where id in(select id from @t group by id having(count(1)>1)) and col1 is null
/*
id col1 col2 col3 col4
----------- -------- -------- ------- -----
1 NULL ggg 555 NULL
4 NULL wwi84 edxd kd
*/
有一个表的数据如下:
1,fff,ggg,555
2,7ullhio,wefcerte,9ihgyyu
1,,ggg,555
3,55533399,8kkhy,20d9
4,e,532,so0df,22888
4,,wwi84,edxd,kd在表里找出以下这二列(还有其他很多这样的情形)该怎么处理?
1,,ggg,555
4,,wwi84,edxd,kd补个说明就是第一字段相同这种情况下只要第二个字段为空就找出来
*/
go
if OBJECT_ID('tbl')is not null
drop table tbl
go
create table tbl(
id int,
A varchar(8),
B varchar(8),
C varchar(8),
D varchar(8)
)
go
insert tbl
select 1,'fff','ggg','555',null union all
select 2,'7ullhio','wefcerte','9ihgyyu',null union all
select 1,null,'ggg','555',null union all
select 3,'55533399','8kkhy','20d9',null union all
select 4,'e','532','so0df','22888' union all
select 4,null,'wwi84','edxd','kd'
select *from
(select a.id,A,B,C,D from
(select id from tbl where A is null)a inner join tbl on a.id=tbl.id)c where c.A is null/*
结果:
id A B C D
1 NULL ggg 555 NULL
4 NULL wwi84 edxd kd
*/--楼主是这个意思吗??
有一个表的数据如下:
1,fff,ggg,555
2,7ullhio,wefcerte,9ihgyyu
1,,ggg,555
3,55533399,8kkhy,20d9
4,e,532,so0df,22888
4,,wwi84,edxd,kd在表里找出以下这二列(还有其他很多这样的情形)该怎么处理?
1,,ggg,555
4,,wwi84,edxd,kd补个说明就是第一字段相同这种情况下只要第二个字段为空就找出来
*/
go
if OBJECT_ID('tbl')is not null
drop table tbl
go
create table tbl(
id int,
A varchar(8),
B varchar(8),
C varchar(8),
D varchar(8)
)
go
insert tbl
select 1,'fff','ggg','555',null union all
select 2,'7ullhio','wefcerte','9ihgyyu',null union all
select 1,null,'ggg','555',null union all
select 3,'55533399','8kkhy','20d9',null union all
select 4,'e','532','so0df','22888' union all
select 4,null,'wwi84','edxd','kd'
select a.id,A,B,C,D from
(select id from tbl group by id having COUNT(id)>1 )a
inner join tbl on a.id=tbl.id where A is null
/*
结果:
id A B C D
1 NULL ggg 555 NULL
4 NULL wwi84 edxd kd
*/--刚刚忽略了需要ID一样的情况下