如表:
name qty
a 1
a 2
a null
b 2
c null
.............经过查询后缩小表为:
name qty
a 1
a 2
b 2
c null
...........规律:
若name中的对应的qty有多个,去掉为null的记录,如a;
若name中对应的qty没有汉语null的记录 ,全部显示,如b;
若name中对应的qty只有一个,为null,那么就保留,如c。请各位高手帮忙设计如何查询?
name qty
a 1
a 2
a null
b 2
c null
.............经过查询后缩小表为:
name qty
a 1
a 2
b 2
c null
...........规律:
若name中的对应的qty有多个,去掉为null的记录,如a;
若name中对应的qty没有汉语null的记录 ,全部显示,如b;
若name中对应的qty只有一个,为null,那么就保留,如c。请各位高手帮忙设计如何查询?
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( name varchar(10),qty int )
go
insert tb SELECT
'a', 1 UNION ALL SELECT 'a', 2 UNION ALL SELECT 'a', null UNION ALL SELECT
'b', 2 UNION ALL SELECT 'c', null
go
select *
from tb t
where (select COUNT(1) from tb where t.name=name )>1 and qty is not null
union all
select *
from tb t
where (select COUNT(1) from tb where t.name=name )=1
goname qty
---------- -----------
a 1
a 2
b 2
c NULL
declare @t table(name varchar(10),qty varchar(10))
--插入数据
insert into @t
select 'a','1' union
select 'a','2' union
select 'a',null union
select 'b','2' union
select 'c',null--测试语句
select * from @t a
where qty is not null or
not exists(select 1 from @t where name=a.name and qty is not null)/*
a 1
a 2
b 2
c NULL
*/
where test11.qty is null and exists
(select 1 from tb b where tb.name=b.name
and b.qty is not null)
DROP TABLE tb
GO
CREATE TABLE tb( [name] varchar(10),qty int )
go
insert tb SELECT
'a', 1 UNION ALL SELECT 'a', 2 UNION ALL SELECT 'a', null UNION ALL SELECT
'b', 2 UNION ALL SELECT 'c', null
goselect t.*
from tb t inner join
(
select [name],count(1) as
from tb
group by [name]) LO
on t.[name] = lo.[name]
where (lo. > 1 and isnull(t.qty,'') <>'') or (lo.Mark=1)