查询不等于1的数据。保留 NULL、0 - 编程论坛

#2

yht44822016-08-29 17:09

只有本站会员才能查看附件，请登录

，截图文件

#3

mywisdom882016-08-29 17:39

select * from 表1 where closed<>1 or closed is null

#4

yht44822016-08-30 07:01

谢谢！！！！！

#5

yht44822016-08-30 07:26

回复 3楼 mywisdom88

改后是这样
SQL("JDBC3", "SELECT avg(VAL) FROM " + $P1 + " WHERE (POINTID = 'YBPer1_tBlrMnStm_H.S') AND DUTYID = 3 AND CLOSED <>1 or CLOSED is null AND DAY(ETIME)>=" + ($P3 - 10) + " AND DAY(ETIME)<" + $P3, 1)

取值不对啊，哪错了，请帮助！！

#6

mywisdom882016-08-30 08:34

以下是引用yht4482在2016-8-30 07:26:00的发言：

改后是这样
SQL("JDBC3", "SELECT avg(VAL) FROM " + $P1 + " WHERE (POINTID = 'YBPer1_tBlrMnStm_H.S') AND DUTYID = 3 AND CLOSED <>1 or CLOSED is null AND DAY(ETIME)>=" + ($P3 - 10) + " AND DAY(ETIME)<" + $P3, 1)

取值不对啊，哪错了，请帮助！！

select * from 表1 where closed<>1 or closed is null
是在SQL2000上运行的，你上面语句，是在那里运行的。

#7

mywisdom882016-08-30 08:38

AND DUTYID = 3 AND CLOSED <>1 or CLOSED is null AND
改为
AND DUTYID = 3 AND (CLOSED <>1 or CLOSED is null) AND

#8

yht44822016-08-30 09:30

我这个是统计满足条件的数据平均值，主要是WHERE 后面的部分，closed 列有1、0、和NULL。当为1时，不进行计算。CLOSED <>1 时少了为NULL部分数据，CLOSED is null 时少了为0部分的数据，
WHERE (POINTID = 'YBPer1_tBlrMnStm_H.S') AND DUTYID = 3 AND CLOSED <>1 or CLOSED is null AND DAY(ETIME)>=" + ($P3 - 10) + " AND DAY(ETIME)<" + $P3, 1)
(POINTID = 'YBPer1_tBlrMnStm_H.S') 为测点名，DUTYID为班组号，DAY(ETIME)>=" + ($P3 - 10) + " AND DAY(ETIME)<" + $P3, 1)为日期范围，

#9

yht44822016-08-30 09:31

回复 7楼 mywisdom88

谢谢！！！主要我太菜了！！

#10

mywisdom882016-08-31 08:58

以下是引用yht4482在2016-8-30 09:31:24的发言：

谢谢！！！主要我太菜了！！

CLOSED <>1 or  CLOSED is null
这个条件是2者只要1个满足就可以，所以，当和其他的条件一起使用时，就要括号起来，重新组成1个条件，即
(CLOSED <>1 or  CLOSED is null)

select * from 表 where 条件1 and 条件2 and (CLOSED <>1 or  CLOSED is null) and 条件3