问题描述:

the below one is my table row structure

select c1 from table where c7 is not null

i need c1 value if c7 if any one of the row is having null value it should not

come may i know who can i do it..

right now i am getting column which is having values but i need if one time is having null values that entry should not come.

c1 c2 c3 c4 c5 c6 c7

31 1 1 BOQ NULL 1 Item5

31 2 1 BOQ NULL 2 Item5

31 3 2 BOQ NULL 3 Itmem7

31 4 3 BOQ NULL 4 Item9

31 5 4 BOQ NULL 5 Item5

31 6 5 BOQ NULL 6 Item5

31 7 6 BOQ NULL 7 NULL

31 8 7 BOQ NULL 8 NULL

31 9 8 BOQ NULL 9 NULL

31 10 9 BOQ NULL 10 NULL

32 1 1 BOQ NULL 1 NULL

32 2 2 BOQ NULL 2 NULL

33 1 0 BOQ NULL 1 NULL

33 2 1 BOQ NULL 3 NULL

网友答案:

Try this:

select c1
from table
group by c1
having sum(c7 is null) = 0

This returns all values of c1 for which there is no c7 value that is null.

For a more generally useful ( all databases) solution, use:

having sum(case when c7 is null then 1 end) = 0

Or

having count(c7) = count(*)
网友答案:

A NOT EXISTS version:

select c1
from tablename t1
where not exists (select 1 from tablename t2
                  where t1.c1 = t2.c1 and t2.c7 is not null)

Perhaps I misunderstood OP. Above answer returns c1 values who have only NULL for c7.

If the question is to find c1 values with no NULL for c7, change the sub-select's IS NOT NULL to IS NULL.

If a c1 value should be returned only once even if several occurrences, do SELECT DISTINCT.

相关阅读:
Top