问个简单的问题

[cscxxx]

有一个表的数据如下:
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

补个说明就是第一字段相同这种情况下只要第二个字段为空就找出来

[AcHerat]

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) = ''

[AcHerat]

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) = ''

[AcHerat]

试试2楼的。

[叶子]

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一样的情况下