现有3张表 分别 单据表A,明细表B,审批记录C
结构
A表中俩个字段ID 和编号No
B表中3个字段ID ,单据id (aId), 名称 (name)
C表中ID,记录表ID (bID),状态 state ,结束时间 endDate, 审批人 userId怎么查出单据表中的所有明细记录的审批状态都为1, 结束时间endDate为空的 单据ID和编号A 表
ID NO
1 A001
2 A002
B 表
ID aID name
1 1 water
2 1 tea
3 2 apple
C表
ID bID state endDate userID
1 1 1 201106 a
2 1 1 201106 b
3 2 0 a
4 2 0 b
5 3 1 a
6 3 1 b查询结果应该是
AID ANO
2 A002
谢谢了。
结构
A表中俩个字段ID 和编号No
B表中3个字段ID ,单据id (aId), 名称 (name)
C表中ID,记录表ID (bID),状态 state ,结束时间 endDate, 审批人 userId怎么查出单据表中的所有明细记录的审批状态都为1, 结束时间endDate为空的 单据ID和编号A 表
ID NO
1 A001
2 A002
B 表
ID aID name
1 1 water
2 1 tea
3 2 apple
C表
ID bID state endDate userID
1 1 1 201106 a
2 1 1 201106 b
3 2 0 a
4 2 0 b
5 3 1 a
6 3 1 b查询结果应该是
AID ANO
2 A002
谢谢了。
解决方案 »
- ora 01017 报错怎么解决?
- 多个session能同时调用执行同一个存储过程
- 检索oracle数据里所有表
- 谁知道pl/sql的,我想问个简单的问题。
- 为什么我在过程里写的execute immediate str 没有权限运行,可是将str单独执行却是可以的?
- 询问一下Oracle安装的问题
- 将一个用户的数据移到另一个表空间上,出现一些奇怪的问题!
- ORACLE 同时更新多列方法
- dba_objects和dba_indexes中同一个user的index数量不一致
- 求一个对比两个数据库相同结构表的过程,如果一致则不变化,如果不一致,则生成增删改语句存到一张表中
- 如何从oracle11g数据库中提取精简客户端,需要那些dll,要使用sqlldr.exe
- merge into 的事务处理问题
SELECT B.aID,A.NO
FROM A,B,C
WHERE A.ID = B.aID
AND B.ID = C.bID
AND C.endDate IS NULL
AND C.state = 1;
where a.id = b.aid
and b.id = c.bid
and c.state = '1' and c.enddate is null
--希望符合你的要求
select a.id,a.no
from a,b,c
where a.id = b.aid
and b.id = c.bid
and c.state = '1' and c.enddate is null
FROM a a,b b,c c
WHERE c.bId=b.aId AND
b.aId=c.ID AND
c.state=1 AND
TRIM(endDate) IS NULL
现有3张表 分别 单据表A,明细表B,审批记录C
结构
A表中俩个字段ID 和编号No
B表中3个字段ID ,单据id (aId), 名称 (name)
C表中ID,记录表ID (bID),状态 state ,结束时间 endDate, 审批人 userId怎么查出单据表中的所有明细记录的审批状态都为1,
并且同一个明细的结束时间endDate全部为空的 单据ID和编号A 表
ID NO
1 A001
2 A002
B 表
ID aID name
1 1 water
2 1 tea
3 2 apple
4 2 pen
5 2 milk
C表
ID bID state endDate userID
1 1 1 201106 a
2 1 1 201106 b
3 2 0 a
4 2 0 b
5 3 1 201106 a
6 3 1 b
7 4 1 a
8 4 1 b
9 4 1 a
10 4 1是否可以能查询出id,no ,及明细id
AID ANO BID BName
2 A002 4 pen
2 A002 5 milk
ID bID state endDate userID
1 1 1 201106 a
2 1 1 201106 b
3 2 0 a
4 2 0 b
5 3 1 201106 a
6 3 1 b
7 4 1 a
8 4 1 b
9 5 1 a
10 5 1 b
--按照你的描述少一个apple,另外写的sql没有变化 只是多了一个distinct
[TEST@myorcl] SQL>WITH A AS(
2 SELECT 1 ID,'A001' NO FROM DUAL UNION ALL
3 SELECT 2 ID,'A002' NO FROM DUAL
4 ), B AS(
5 SELECT 1 ID,1 aID,'water' NAME FROM DUAL UNION ALL
6 SELECT 2 ID,1 aID,'tea' NAME FROM DUAL UNION ALL
7 SELECT 3 ID,2 aID,'apple' NAME FROM DUAL UNION ALL
8 SELECT 4 ID,2 aID,'pen' NAME FROM DUAL UNION ALL
9 SELECT 5 ID,2 aID,'milk' NAME FROM DUAL
10 ), C AS(
11 SELECT '1' ID , 1 bID, 1 state , '201106' endDate, 'a' userID from dual union all
12 SELECT '2' ID , 1 bID, 1 state , '201106' endDate, 'b' userID from dual union all
13 SELECT '3' ID , 2 bID, 0 state , null endDate, 'a' userID from dual union all
14 SELECT '4' ID , 2 bID, 0 state , null endDate, 'b' userID from dual union all
15 SELECT '5' ID , 3 bID, 1 state , '201106' endDate, 'a' userID from dual union all
16 SELECT '6' ID , 3 bID, 1 state , null endDate, 'b' userID from dual union all
17 SELECT '7' ID , 4 bID, 1 state , null endDate, 'a' userID from dual union all
18 SELECT '8' ID , 4 bID, 1 state , null endDate, 'b' userID from dual union all
19 SELECT '9' ID , 5 bID, 1 state , null endDate, 'a' userID from dual union all
20 SELECT '10' ID, 5 bID, 1 state , null endDate, 'b' userID from dual
21 )SELECT DISTINCT B.aID,A.NO,C.BID,B.NAME
22 FROM A,B,C
23 WHERE A.ID = B.aID
24 AND B.ID = C.bID
25 AND C.endDate IS NULL
26 AND C.state = 1; AID NO BID NAME
---------- ---- ---------- -----
2 A002 5 milk
2 A002 4 pen
2 A002 3 apple
--那就改造一下。
[TEST@orcl] SQL> WITH A AS(
2 SELECT 1 ID,'A001' NO FROM DUAL UNION ALL
3 SELECT 2 ID,'A002' NO FROM DUAL
4 ), B AS(
5 SELECT 1 ID,1 aID,'water' NAME FROM DUAL UNION ALL
6 SELECT 2 ID,1 aID,'tea' NAME FROM DUAL UNION ALL
7 SELECT 3 ID,2 aID,'apple' NAME FROM DUAL UNION ALL
8 SELECT 4 ID,2 aID,'pen' NAME FROM DUAL UNION ALL
9 SELECT 5 ID,2 aID,'milk' NAME FROM DUAL
10 ), C AS(
11 SELECT '1' ID , 1 bID, 1 state , '201106' endDate, 'a' userID from dual union all
12 SELECT '2' ID , 1 bID, 1 state , '201106' endDate, 'b' userID from dual union all
13 SELECT '3' ID , 2 bID, 0 state , null endDate, 'a' userID from dual union all
14 SELECT '4' ID , 2 bID, 0 state , null endDate, 'b' userID from dual union all
15 SELECT '5' ID , 3 bID, 1 state , '201106' endDate, 'a' userID from dual union all
16 SELECT '6' ID , 3 bID, 1 state , null endDate, 'b' userID from dual union all
17 SELECT '7' ID , 4 bID, 1 state , null endDate, 'a' userID from dual union all
18 SELECT '8' ID , 4 bID, 1 state , null endDate, 'b' userID from dual union all
19 SELECT '9' ID , 5 bID, 1 state , null endDate, 'a' userID from dual union all
20 SELECT '10' ID, 5 bID, 1 state , null endDate, 'b' userID from dual
21 )SELECT DISTINCT B.aID,A.NO,C.BID,B.NAME
22 FROM A,B,(SELECT bID,MAX(endDate)endDate FROM C WHERE C.state = 1 GROUP BY bID )C
23 WHERE A.ID = B.aID
24 AND B.ID = C.bID
25 AND C.endDate IS NULL; AID NO BID NAME
---------- ---- ---------- -----
2 A002 5 milk
2 A002 4 pen
ID bID state endDate userID
1 1 1 201106 a
2 1 1 201106 b
3 2 0 a
4 2 0 b
5 3 1 201106 a
6 3 1 b
7 4 1 201106 a
8 4 1 201106 b
9 4 1 a
10 4 1 b
11 5 1 a
12 5 1 b
WITH A AS(
SELECT 1 ID,'A001' NO FROM DUAL UNION ALL
SELECT 2 ID,'A002' NO FROM DUAL
), B AS(
SELECT 1 ID,1 aID,'water' NAME FROM DUAL UNION ALL
SELECT 2 ID,1 aID,'tea' NAME FROM DUAL UNION ALL
SELECT 3 ID,2 aID,'apple' NAME FROM DUAL UNION ALL
SELECT 4 ID,2 aID,'pen' NAME FROM DUAL UNION ALL
SELECT 5 ID,2 aID,'milk' NAME FROM DUAL
), C AS(
SELECT '1' ID , 1 bID, 1 state , '201106' endDate, 'a' userID, '0' PID from dual union all
SELECT '2' ID , 1 bID, 1 state , '201106' endDate, 'b' userID, '0' PID from dual union all
SELECT '3' ID , 2 bID, 0 state , null endDate, 'a' userID, '0' PID from dual union all
SELECT '4' ID , 2 bID, 0 state , null endDate, 'b' userID, '0' PID from dual union all
SELECT '5' ID , 3 bID, 1 state , '201106' endDate, 'a' userID, '0' PID from dual union all
SELECT '6' ID , 3 bID, 1 state , null endDate, 'b' userID, '0' PID from dual union all
SELECT '7' ID , 4 bID, 1 state , '201106' endDate, 'a' userID, '0' PID from dual union all
SELECT '8' ID , 4 bID, 1 state , '201106' endDate, 'b' userID, '0' PID from dual union all
SELECT '9' ID , 4 bID, 1 state , null endDate, 'a' userID, '7' PID from dual union all
SELECT '10' ID , 4 bID, 1 state , null endDate, 'b' userID, '8' PID from dual union all
SELECT '11' ID , 5 bID, 1 state , null endDate, 'a' userID, '0' PID from dual union all
SELECT '12' ID, 5 bID, 1 state , null endDate, 'b' userID , '0' PID from dual
)SELECT DISTINCT B.aID,A.NO,C.BID,B.NAME
FROM A,B,(SELECT bID,MAX(endDate)endDate FROM C WHERE C.state = 1 GROUP BY bID )C
WHERE A.ID = B.aID
AND B.ID = C.bID
AND C.endDate IS NULL;
执行上面sql只能查出
AID NO BID NAME
---------- ---- ---------- -----
2 A002 5 milk
现在C表中的记录 9 ,10 也是有问题的,这两条记录state=1并且 时间为空。应该如何查出来。还有如果要修改C表中有问题的数据,如9,10,11,12 该如果操作,现在只知道C表中的BID
你先是说 bID 为 3的 apple 不应该被查出来, 因为在c表中 bID =3 有一个 endDate 不为空
然后又说 现在C表中的记录 9 ,10 也是有问题的,这两条记录state=1并且 时间为空。应该如何查出来
你举的例子里面 9 和 10 里,也就是 在C表中bID = 4 有一个endDate不为空了,按照你的原则是不取的。真不明白你的需求了。
---------- ---- ---------- -----
2 A002 4 pen
原因也很明显:bID 为 4的 pen 不应该被查出来, 因为在c表中 bID =4 有一个 endDate 不为空
感谢大侠起这么早回帖,,因为审批记录c表是有上下层关系的。7和8是属于同一个步骤,
只要同一个BID记录属于同一个步骤并且有一个审批enddate 就算是正常的,9,10是7,8的下一个审批节点,因为state=1 但endDate为空所以 9,10 也有问题。还请大侠帮忙,感激
WITH A AS(
SELECT 1 ID,'A001' NO FROM DUAL UNION ALL
SELECT 2 ID,'A002' NO FROM DUAL
), B AS(
SELECT 1 ID,1 aID,'water' NAME FROM DUAL UNION ALL
SELECT 2 ID,1 aID,'tea' NAME FROM DUAL UNION ALL
SELECT 3 ID,2 aID,'apple' NAME FROM DUAL UNION ALL
SELECT 4 ID,2 aID,'pen' NAME FROM DUAL UNION ALL
SELECT 5 ID,2 aID,'milk' NAME FROM DUAL
), C AS(
SELECT '1' ID , 1 bID, 1 state , '201106' endDate, 'a' userID, '0' PID from dual union all
SELECT '2' ID , 1 bID, 1 state , '201106' endDate, 'b' userID, '0' PID from dual union all
SELECT '3' ID , 2 bID, 0 state , null endDate, 'a' userID, '0' PID from dual union all
SELECT '4' ID , 2 bID, 0 state , null endDate, 'b' userID, '0' PID from dual union all
SELECT '5' ID , 3 bID, 1 state , '201106' endDate, 'a' userID, '0' PID from dual union all
SELECT '6' ID , 3 bID, 1 state , null endDate, 'b' userID, '0' PID from dual union all
SELECT '7' ID , 4 bID, 1 state , '201106' endDate, 'a' userID, '0' PID from dual union all
SELECT '8' ID , 4 bID, 1 state , '201106' endDate, 'b' userID, '0' PID from dual union all
SELECT '9' ID , 4 bID, 1 state , null endDate, 'a' userID, '7' PID from dual union all
SELECT '10' ID , 4 bID, 1 state , null endDate, 'b' userID, '8' PID from dual union all
SELECT '11' ID , 5 bID, 1 state , null endDate, 'a' userID, '0' PID from dual union all
SELECT '12' ID, 5 bID, 1 state , null endDate, 'b' userID , '0' PID from dual
)SELECT DISTINCT B.aID,A.NO,C.BID,B.NAME
FROM A,B,(SELECT bID,MAX(endDate)endDate FROM C WHERE C.state = 1 GROUP BY bID,pID )C
WHERE A.ID = B.aID
AND B.ID = C.bID
AND C.endDate IS NULL;分组是吧PID 加上就可以查出来了。
AID NO BID NAME
---------- ---- ---------- -----
2 A002 5 milk
2 A002 4 pen但我要修改c表中的数据 9,10,11,12 。通过上面的查询在c表中可以根据BID查询出来查询