请教高手,一个 sql语句怎么写??

pimlee 2006-11-01 01:22:19
select factoryid,qualityid,customerid
from 表1 where factoryid not in (case when(qualityid='20023')
then '51001','20201'
when (qualityid='20030') then '51002','33224'
when(qualityid='10021' or qualityid='10022') then '' end)
and to_char(ndate,'yyyy-mm-dd')>='2006-08-07'
and to_char(ndate,'yyyy-mm-dd')<='2006-08-07'
问题就是这段:
factoryid not in (case when(qualityid='20023')
then '51001','20201'
when (qualityid='20030') then '51002','33224'
when(qualityid='10021' or qualityid='10022') then '' end)

我要实现的是:当qualityid='20023'的时候,factoryid不能等于'510021','20201'。
当qualityid='20030'的时候,factoryid不能等于'51002','33224'。
我上面的那个语句不对,所以请教怎么写这个sql语句阿。

谢谢。
...全文
224 13 打赏 收藏 举报
写回复
13 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
jxnetinfo 2006-11-02
  • 打赏
  • 举报
回复
select factoryid,qualityid,customerid
from 表1 where to_char(ndate,'yyyy-mm-dd')>='2006-08-07'
and to_char(ndate,'yyyy-mm-dd')<='2006-08-07'
and
factoryid not in
(
select case when(qualityid='20023') then (select '51001'union select '20201')
when (qualityid='20030') then (select '51002' union select '33224')
when(qualityid='10021' or qualityid='10022') then '' end
From 表1
)

我的QQ:54184236,MSN:jxrensoft@hotmail.com 我们在这里聊
jxnetinfo 2006-11-02
  • 打赏
  • 举报
回复
你这段和我写的不一样啊
wangping06 2006-11-02
  • 打赏
  • 举报
回复
select factoryid,qualityid,customerid
from 表1 where


((qualityid='20023' and factoryid!='510021' and factoryid!='20201') or (qualityid='20030' and factoryid!='51002' and factoryid!='33224')or(qualityid!='20023' and qualityid!='20030'))

and to_char(ndate,'yyyy-mm-dd')>='2006-08-07'
and to_char(ndate,'yyyy-mm-dd')<='2006-08-07'
pimlee 2006-11-02
  • 打赏
  • 举报
回复
运行错误, 未找到预期 FROM 关键字
在看看吧,高手
股神 2006-11-01
  • 打赏
  • 举报
回复
up
pimlee 2006-11-01
  • 打赏
  • 举报
回复
好好,谢谢了。明天试一下
jxnetinfo 2006-11-01
  • 打赏
  • 举报
回复
晕,写错了
select factoryid,qualityid,customerid
from 表1 where to_char(ndate,'yyyy-mm-dd')>='2006-08-07'
and to_char(ndate,'yyyy-mm-dd')<='2006-08-07'
and
factoryid not in
(
select case when(qualityid='20023') then (select '51001'union select '20201')
when (qualityid='20030') then (select '51002' union select '33224')
when(qualityid='10021' or qualityid='10022') then '' end
From 表1
)
pimlee 2006-11-01
  • 打赏
  • 举报
回复
From factoryid ???
jxnetinfo 2006-11-01
  • 打赏
  • 举报
回复
select factoryid,qualityid,customerid
from 表1 where to_char(ndate,'yyyy-mm-dd')>='2006-08-07'
and to_char(ndate,'yyyy-mm-dd')<='2006-08-07'
and
factoryid not in
(
select case when(qualityid='20023') then (select '51001'union select '20201')
when (qualityid='20030') then (select '51002' union select '33224')
when(qualityid='10021' or qualityid='10022') then '' end
From factoryid
)
pimlee 2006-11-01
  • 打赏
  • 举报
回复
不对,那20201,33224 写到哪里???
jxnetinfo 2006-11-01
  • 打赏
  • 举报
回复
楼主的题目是乎有些逻辑问题,就是第一个和第二个then后面有有两列,最后一个then后面为'',其实就是一列,列数不确定,并且采用in查询,要求结果集只能是一列,我下面给出返回一列的方式参考:
select factoryid,qualityid,customerid
from 表1 where to_char(ndate,'yyyy-mm-dd')>='2006-08-07'
and to_char(ndate,'yyyy-mm-dd')<='2006-08-07'
and factoryid not in
(
select case when(qualityid='20023') then '51001'
when (qualityid='20030') then '51002'
when(qualityid='10021' or qualityid='10022') then '' end
From factoryid
)
pimlee 2006-11-01
  • 打赏
  • 举报
回复
一路相北的方法不太准确。,,
copico 2006-11-01
  • 打赏
  • 举报
回复
select factoryid,qualityid,customerid
from 表1 where factoryid not in
(select factoryid from table1 where (qualityid = '20023' and ( factoryid = '510021' or factoryid = '20201')) or
(qualityid = '20030' and ( factoryid = '51002' or factoryid = '33224')))
相关推荐
发帖
C#

10.8w+

社区成员

.NET技术 C#
社区管理员
  • C#
  • Web++
  • by_封爱
加入社区
帖子事件
创建了帖子
2006-11-01 01:22
社区公告

让您成为最强悍的C#开发者