DBMS是我正在使用Mysql。我有这些表:
路径表:
id idSentence idPath token isTV idC
1 s0001 p1 test1 true ic000041
2 s0001 p1 test2 true ic000041
3 s0002 p2 test3 true ic000042
4 s0002 p3 test4 false ic000042
5 s0002 p3 test5 true ic000042
6 s0002 p4 test6 false ic000042
7 s0002 p4 test7 true ic000042
8 s0002 p4 test8 true ic000042
9 s0002 p4 test9 false ic000042
10 s0003 p5 test10 false ic000044
11 s0003 p5 test11 false ic000044
12 s0003 p5 test12 false ic000044
13 s0003 p6 test13 false ic000044
14 s0003 p6 test14 true ic000044
关系表:
id id2 rel
3 4 nsubj
4 5 dobj
6 7 pobj
8 9 nsubjpass
10 11 pobj
内容表:
idC tag
ic000040 a
ic000041 p
ic000042 div
ic000043 b
ic000044 i
我想创建一个查询,该查询针对每个idSentence(路径表)选择具有以下条件的idSentence,idPath,令牌,isTV,rel和tag的每个元组:
我创建了这个查询:
SELECT p.idSentence, p.idPath, p.token, p.isTV, r.rel, t.tag
FROM path p LEFT OUTER JOIN relation r
ON (p.id = r.id) JOIN content t ON(p.idC=t.idC)
JOIN
(select idPath, max(case when p.isTV = 'true' then 1 else 0 end) as HasTv,
(case when COUNT(*) between 2 and 3 then 1 else 0 end) as Has
from path p
group by idPath
) pf
on p.idPath = pf.idPath and
pf.HasTv = 1 and pf.Has = 1;
但我还要在连接条件中添加中心:
select count(distinct(idPath)) as cc from path group by idSentence
如何修改查询以添加此条件?
我已经添加了条件:
select count(*) as cc from path group by idPath
通过以下语句:
(case when COUNT(*) between 2 and 3 then 1 else 0 end) as Has
我测试了查询,并且部分有效:QUERY
要检查每个序列中不同路径的数量,您必须按idSequence进行分组,因此您不能只为按另一列分组的子查询放置另一个联接条件。
最好的选择是使用不同的连接条件来连接另一个子查询(为了便于阅读,我对查询部分做了一些简化):
SELECT p.idSentence, p.idPath, p.token, p.isTV, r.rel, t.tag
FROM path p
LEFT JOIN relation r ON p.id = r.id
JOIN content t ON p.idC = t.idC
JOIN
( SELECT idPath,
MAX(isTV = 'true') AS HasTv,
COUNT(*) AS pathLength
FROM path
GROUP BY idPath
) pf
ON p.idPath = pf.idPath
AND pf.HasTv = 1
AND pathLength BETWEEN 2 AND 3
JOIN
( SELECT idSentence, COUNT(DISTINCT(idPath)) AS paths
FROM path
GROUP BY idSentence
) ps
ON p.idSentence = ps.idSentence
AND ps.paths BETWEEN 2 AND 3
检查Sqlfiddle。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句