实例如下:存在 TAST(任务表)与 CHECKITEM(检查事项表),由于任务表中一条记录会对应多个检查事项,根据数据库范式,单独建了下面的检查事项表。
现在问题是如何根据检查项条件获取对应的任务信息?检查项可多选且要求完全匹配。
如何在CHECKITEM表中找到完全匹配cheitcode='a,b,c’的taskid?
CHECKITEM
id | taskid | cheitcode |
---|---|---|
1 | 0001 | a |
2 | 0001 | b |
3 | 0001 | c |
4 | 0002 | a |
5 | 0002 | b |
6 | 0002 | d |
7 | 0003 | a |
TASK
id | … | … |
---|---|---|
0001 | ~ | ~ |
0002 | ~ | ~ |
0003 | ~ | ~ |
初次写的时候是
select *
from TASK d
where 1 = 1
and d.ID in (select distinct taskid
from (select taskid,
count(distinct cheitcode) cnt,
wm_concat(to_char(cheitcode)) codes
from CHECKITEM
group by taskid)
where cnt = '3'
and codes = 'a,b,c')
写的很直肠子…后来发现当wm_concat(to_char(cheitcode))过大会有
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
报错的情况。
实际情况中cheitcode是32位字母数字混合的编号,测试库中存在一条id匹配19个code的情况……
后来换了个思路:
select *
from TASK d
where 1 = 1
and (select count(cheitcode)
from CHECKITEM
where taskid = d.id) = '3'
and d.ID in
(select taskid
from CHECKITEM
where taskid = d.id
and cheitcode in ('a','b','c'))
总结:
- wm_concat 可以用于查询,条件中尽量不要使用。
- 一对多,外键是关键!!多对多,中间表是关键!!