假如我有一张表 表名为 u 此表中有以下字段 id(自增长),wid(告警ID),Uid(用户ID),dt(开始时间),enddt(结束时间) wid,uid都是整型的,存在另外一张表,现在用户uid=(277,278,288,299...n),wid=(1,2,3,4,5....n)[这个长度是有限的]
现在有这样的需求 用过用户ID,告警ID,开始时间与结束时间查询出数据来,告警ID的条件不是wid in (1,2,3,4,5,....n) 而是说某个用户他的即包含wid=1又要包含wid=2的才允许查出来信息,应该怎么样写查询语句?
现在有这样的需求 用过用户ID,告警ID,开始时间与结束时间查询出数据来,告警ID的条件不是wid in (1,2,3,4,5,....n) 而是说某个用户他的即包含wid=1又要包含wid=2的才允许查出来信息,应该怎么样写查询语句?
解决方案 »
- 什么是oracle客户端,什么事服务器端
- oracle11g 使用过程中突然莫名其妙断开的问题(ORA-01034,ORA-27101)
- 菜鸟问题 求解
- redhat 9.0 上 安装 oracle 8.1.7 请求 急救!!!!
- 初来乍到,还没有分呢,希望高手帮忙!!Io 异常: Connection refused(DESCRIPTION=(ERR=12518)(VSNNUM=150999297)。。。在线等!!!
- 在oracle9i的哪个工具里可以对表进行手工输入数据?
- !!!菜鸟请问!!!!!
- 请教:Oracle 技术问题
- oracle多行合并成一行,并且行内不同值组成新列。显示在同一行
- 如何对字符串中的数字实现完整匹配?
- oracle 德文处理
- sql多表删除
1 288 3 12-10-11 12-10-11
2 288 4 12-10-11 12-10-11
3 288 3 12-10-11 12-10-11
4 288 2 12-10-11 12-10-11
条件是查询用户ID为288 告警号为3和4都存在的记录 如果用户输入的是wid为3,4,5这样的条件,则不显示记录,意思就是说wid的条件是每个都存在时候才显示
WITH table1 AS
(
SELECT '1' AS id, '288' AS aa,'3' AS wid,'12-10-11' AS dt,'12-10-11' AS enddt FROM dual
union all
SELECT '2' AS id, '288' AS aa,'4' AS wid,'12-10-11' AS xm,'12-10-11' AS enddt FROM dual
union all
SELECT '3' AS id, '288' AS aa,'3' AS wid,'12-10-11' AS xm,'12-10-11' AS enddt FROM dual
union all
SELECT '4' AS id, '288' AS aa,'2' AS wid,'12-10-11' AS xm,'12-10-11' AS enddt FROM dual
union all
SELECT '5' AS id, '299' AS aa,'5' AS wid,'12-10-11' AS xm,'12-10-11' AS enddt FROM dual
)
select* from table1
where 0 = (
select regexp_instr(translate(replace('3,4,2,2,2,3,4'/*<-用户输入的东西*/, ',' ), str, 'XXXXXXXX'),
'\d') AS STR3 FROM (select listagg(wid) within
group(
order by wid) as str
from table1
where aa = '288')) and aa = '288'
from
(select uid from u where wid=3) a,
(select uid fron u where wid=4) b
where a.uid=b.uid
(select wid from u t2 where t2.uid = 用户1)select * from u t1
where t1.uid = 用户1
and not exists (
select 1 from
(
select regexp_substr(警告号,'[^,]+',1,level) wid from dual connect by level<=
(select length(警告号)-length(replace(警告号,',')) from dual)+1
) t4
where not exists ( select 1 from tmp_table t5 where t5.wid=t4.wid )
);