获取包含特定单元格中输入值的行，按其他单元格过滤，返回其他单元格值 [英] Get rows that include value of input in specific cell, filter by other cell, return other cell values
问题描述
感谢 Paul Spiegel对上一个问题的回答，我在数据库dab中有一个表tab，并且它由四列组成.一个简单的ID列(id)也是主键(int)，一个pat列(varchar(255))，一个path列(varchar(191))，最后是has_tree (位(1)).
id pat path has_tree
1 AA 1 1
2 ABA 1/2 1
3 ABCA 1/3 1
4 ABA 1/5 0
5 AB 2 0
6 BBB 2/1 1
7 CAB 2/2 1
8 ABC 1/4 0
9 ABC 1/5/7 1
10 ABA 3/2 1
我想要一个pat值(如果存在)
- 所有唯一的
pat值(如果输入是has_tree==1，则包括输入值) - 其路径包含输入的路径，
- 以及
has_tree == 1.
因此输入AA应该返回['AA', 'ABA', 'ABCA', 'ABC'].
重要说明是输入pat的path不一定是单个项根路径"，换句话说:输入path很有可能像1/12/45/966那样，其中情况下，我需要所有唯一的pat值，其中path是1/12/45/966的后代，而has_tree是1.另一点是路径可能很深，因此即使输入path为1，结果也可能会深很多级别，例如1/22/36/88/98/455/878/1205/2555.
dab中有一个表tab时，我想执行脚本.)SELECT t.*
FROM tree t
CROSS JOIN tree r -- root
WHERE r.CELL = '3B0'
AND t.path LIKE CONCAT(r.path, '%');
我正在尝试使用PHP执行此操作，然后调用一些SQL数据库. 是否可以以通用的高性能形式编写此代码，使其适用于PostgreSQL，MySQL(i)和其他应用程序?如果没有，我至少希望看到MySQLi和PostgreSQL的变体.
基于上面Paul的示例代码，我认为这符合这些原则，但是我不确定. 我也不确定为什么JOIN运算符是必需的，以及在这种情况下它是做什么的(我知道它通常会做什么，但在这种情况下不是必需的).我确定此代码可以针对所有SQL风格进行改进，修复和更通用吗?此外，我看不到我在下面的尝试中将当前输入pat包含在输出中.
SELECT t.pat
FROM `tab` t
JOIN `tab` r
WHERE r.pat = 'AA' -- input
AND t.path LIKE CONCAT(r.path, '/', '%')
AND t.has_tree = 1;
奖金问题:除has_tree外，我已为所有列建立索引.同时为该列编制索引是否有益?
首先注意:路径应以反斜杠('1/'，'1/2/'而不是'1'和'1/2')结尾.这很重要，因为否则您将无法匹配子树的根.示例:
WHERE path LIKE '1/%'
将不匹配'1'，但将匹配'1/'.
您也不能只跳过查询中的反斜杠:
WHERE path LIKE '1%'
将匹配根'1'，但也将匹配'10..'
现在，您的问题是:如果您已经知道根节点的路径，则不需要联接:
SELECT * FROM tab WHERE path LIKE '1/%' AND has_tree
要仅获得独特"拍子，可以使用DISTINCT关键字:
SELECT DISTINCT pat
FROM tab
WHERE path LIKE '1/%'
AND has_tree
就是这样.
如果您不知道根路径，而仅知道pat值，则可能需要运行两个查询(首先获取路径，然后获取后代)或使用联接.
PostgreSQL:
SELECT DISTINCT t.pat
FROM tab t
JOIN tab r ON t.path LIKE r.path || '%'
WHERE r.pat = 'AA' -- input
AND t.has_tree
演示: http://rextester.com/EXZT43019
MySQL:
SELECT DISTINCT t.pat
FROM tab t
JOIN tab r ON t.path LIKE CONCAT(r.path, '%')
WHERE r.pat = 'AA' -- input
AND t.has_tree
演示: http://rextester.com/DNHRJ83456
注意:正如您将在domo中看到的那样，如果通过以下方式更改模式，也可以在MySQL中使用管道(||)进行串联:
SET sql_mode=PIPES_AS_CONCAT;
关于索引:布尔列上的索引通常不是很有用.但是，您只能确定是否进行测试.对于给定的查询，(has_tree, path)上的复合索引可能会提高性能.
Thanks to Paul Spiegel's answer on a previous question, I have a table tab in database dab and it consists of four columns. A simple, a ID column (id) that is also the primary key (int), a pat column (varchar(255)), a path column (varchar(191)), and finally has_tree (bit(1)).
id pat path has_tree
1 AA 1 1
2 ABA 1/2 1
3 ABCA 1/3 1
4 ABA 1/5 0
5 AB 2 0
6 BBB 2/1 1
7 CAB 2/2 1
8 ABC 1/4 0
9 ABC 1/5/7 1
10 ABA 3/2 1
Given a pat value (if it exists), I want
- all unique
patvalues (including the input's if ithas_tree==1), - where their path contains the path of the input,
- and where
has_tree == 1.
So input AA should return ['AA', 'ABA', 'ABCA', 'ABC'].
Important remarks are that the path for the input pat is not necessarily a single item 'root path', in other words: it's very well possible that the input path is something like 1/12/45/966 in which case I want all the unique pat values where the path is a descendant of 1/12/45/966 and where has_tree is 1. Another remark is that the path can be very deep, so even if the input path is 1, the result can be many levels deeper, e.g. 1/22/36/88/98/455/878/1205/2555.
Paul suggested the following query, which worked for the proposed data structure, however as you can see the current structure and requirements are slightly different. (Also note that I have one database with multiple tables. So given a table tab in database dab I'd like to execute the script.)
SELECT t.*
FROM tree t
CROSS JOIN tree r -- root
WHERE r.CELL = '3B0'
AND t.path LIKE CONCAT(r.path, '%');
I am trying to do this with PHP and then calling some SQL database. Is it possible to write this in a generic, performant form such that it works for PostgreSQL, MySQL(i) and others? If not, I'd at least like to see the MySQLi and PostgreSQL variants.
Building on Paul's example code above, I think it would be something along these lines, but I am not sure. I am also not sure why the JOIN operator is necessary and what it does in this context (I know what it generally does, but not why it is necessary in this case). I am sure this code can be improved, fixed, and made more general for all SQL flavours? Additionally, I do not see that my attempt below includes the current input pat in the output.
SELECT t.pat
FROM `tab` t
JOIN `tab` r
WHERE r.pat = 'AA' -- input
AND t.path LIKE CONCAT(r.path, '/', '%')
AND t.has_tree = 1;
Bonus question: I have indexed all columns except for has_tree. Would it be beneficial to index this column as well?
First note: The paths should end with a backslash ('1/', '1/2/' instead of '1' and '1/2'). This is important because otherwise you can not match the the root of the subtree. Example:
WHERE path LIKE '1/%'
will not match '1' but will match '1/'.
You can also not just skip the backslash in the query:
WHERE path LIKE '1%'
will match the root '1' but also '10..'
Now to your question: If you already know the path of the root node, you don't need a join:
SELECT * FROM tab WHERE path LIKE '1/%' AND has_tree
To get only "unique" pats you can use the DISTINCT keyword:
SELECT DISTINCT pat
FROM tab
WHERE path LIKE '1/%'
AND has_tree
That's it.
If you don't know the roots path but only the pat value, you would either need to run two queries (first get the path then get the descendants) or use a join.
PostgreSQL:
SELECT DISTINCT t.pat
FROM tab t
JOIN tab r ON t.path LIKE r.path || '%'
WHERE r.pat = 'AA' -- input
AND t.has_tree
Demo: http://rextester.com/EXZT43019
MySQL:
SELECT DISTINCT t.pat
FROM tab t
JOIN tab r ON t.path LIKE CONCAT(r.path, '%')
WHERE r.pat = 'AA' -- input
AND t.has_tree
Demo: http://rextester.com/DNHRJ83456
Note: As you will see in the domo, you can also use pipes (||) in MySQL for concatenation if you change the mode with:
SET sql_mode=PIPES_AS_CONCAT;
Regarding the index: An index on a boolean column is usually not very usefull. However you can only know for sure if you test it. For the given queries a composite index on (has_tree, path) might improve the performance.
这篇关于获取包含特定单元格中输入值的行，按其他单元格过滤，返回其他单元格值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
