跨多个表的复杂 SQL 联接，所有表具有多个条件

发布于 2024-09-12 09:24:52 字数 790 浏览 3 评论 0原文

给出下表：

labels              tags_labels  
|id   |name   |     |url   |labelid |  
|-----|-------|     |/a/b  |1       |  
|1    |punk   |     |/a/c  |2       |  
|2    |ska    |     |/a/b  |3       |
|3    |stuff  |     |/a/z  |4       |  

artists             tags  
|id  |name    |     |url   |artistid   |albumid  |  
|----|--------|     |------|-----------|---------|  
|1   |Foobar  |     |/a/b  |1          |2637     |
|2   |Barfoo  |     |/a/z  |2          |23       |  
|3   |Spongebob|    |/a/c  |1          |32       |

我想获取与几个条件匹配的 url 列表（用户可以将其输入到使用这些语句的脚本中）。例如，用户可能想要列出具有标签“(1 OR 2) AND 3”的所有 url，但前提是它们是由艺术家“Spongebob OR What”创作的。

是否可以使用内部/哈利波特/交叉/自我连接在单个语句中执行此操作？或者我是否必须将查询分布在多个语句中并在脚本中缓冲结果？

编辑：
如果可能的话，声明会是什么样子？：p

原文

Given the following tables:

labels              tags_labels  
|id   |name   |     |url   |labelid |  
|-----|-------|     |/a/b  |1       |  
|1    |punk   |     |/a/c  |2       |  
|2    |ska    |     |/a/b  |3       |
|3    |stuff  |     |/a/z  |4       |  

artists             tags  
|id  |name    |     |url   |artistid   |albumid  |  
|----|--------|     |------|-----------|---------|  
|1   |Foobar  |     |/a/b  |1          |2637     |
|2   |Barfoo  |     |/a/z  |2          |23       |  
|3   |Spongebob|    |/a/c  |1          |32       |

I would like to get a list of urls that match a couple of conditions (which can be entered by the user into the script that uses these statements).
For example, the user might want to list all urls that have the labels "(1 OR 2) AND 3", but only if they are by the artists "Spongebob OR Whatever".

Is it possible to do this within a single statement using inner/harry potter/cross/self JOINs?
Or would I have to spread the query across multiple statements and buffer the results inside my script?

Edit:
And if it is possible, what would the statement look like? :p

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

送舟行 2024-09-19 09:24:52

是的，您可以在一个查询中完成此操作。也许一种有效的方法是根据用户输入的条件动态生成 SQL 语句。

回复收藏 0 原文

少年亿悲伤 2024-09-19 09:24:52

此查询允许您按标签名称或艺术家名称进行过滤。
动态构建 sql 以连接用户参数或
将所需的参数传递到存储过程中显然会改变
where 子句，但这实际上取决于您的“脚本”必须有多动态......

SELECT tl.url
FROM labels l INNER JOIN tags_labels tl ON l.id = tl.labelid
WHERE l.name IN ('ska','stuff') 
UNION (
SELECT t.url
FROM artists a INNER JOIN tags t ON a.id = t.artistid
WHERE a.name LIKE '%foo%'
)

祝您好运！

This query would allow you to filter by label name or artist name.
Building the sql dynamically to concatenate the user parameters or
passing the desired parameters into a stored procedure would obviously change
the where clauses but that really depends on how dynamic your 'script' must be...

SELECT tl.url
FROM labels l INNER JOIN tags_labels tl ON l.id = tl.labelid
WHERE l.name IN ('ska','stuff') 
UNION (
SELECT t.url
FROM artists a INNER JOIN tags t ON a.id = t.artistid
WHERE a.name LIKE '%foo%'
)

Good Luck!

回复收藏 0 原文

~没有更多了~