SQL中的高级过滤器 [英] Advanced filter in SQL

问题描述

我在我的SQL中有一个问题。请查看数据库的模式。

I have a problem with filtering in my SQL. Please take a look at the schema of database.

现在，我想让程序员知道PHP技能●●/●●●和Java技能●●●。我执行以下查询：

Now, I'd like to get programmers who know PHP with skills ●●/●●● and Java with skills ●●●. I executed following query:

SELECT p.name, p.city FROM programmers p INNER JOIN skills s
ON p.id = s.programmer
WHERE ( s.lang = 'PHP' AND s.level > 1 ) OR ( s.lang = 'Java' AND s.level = 3 );

我得到的是

• 乔治亚，亚特兰大（因为Java = 3）


• 乔治亚，亚特兰大（因为PHP> 1）


• Christopher PHP> 1）

但Christopher不知道Java，在主要条件之间替换或与和之间不会返回任何内容。

But Christopher does not know Java, yet he is returned. Replacing OR with AND between main conditions cause nothing is returned.

此外，我想获得一行程序员的所有技能;这里约书亚分成两行。如何解决？

Moreover, I want to get all skills of a programmer in one row; here Joshua is in two separate rows. How to fix it?

考虑所有事情，最后一个问题。如何获取：

All things considered, last questions. How to get:

• 程序员技能PHP●●/●●●，JS●●●和HTML●/●●/●● ●


• 程序员技能PHP●●/●●●和PHP经验2+年和JS●●●有经验4年以上


• 程序员与技能PHP / /●●/●●●和JS●●●在两个/任何
中至少有3年以上的经验

• programmers with skills PHP ●●/●●●, JS ●●● and HTML ●/●●/●●●
• programmers with skills PHP ●●/●●● and PHP experience 2+ years and JS ●●● with experience 4+ years
• programmers with skills PHP ●/●●/●●● and JS ●●● with experience at least 3+ years in both/any

希望它的可能。提前感谢。

Hope it's possible. Thanks in advance.

EDIT
专注于日期的操作没有问题，没有必要计算日期之间的差异，SQL只能包含年，即2010年。一般日期不是点。

EDIT Operations focused on dates are no problem and there are no need to calculate the difference between dates, SQL can contain just year, i.e. 2010. Generally date is not the point.

推荐答案

p>这是一个set-within-sets问题的例子。我喜欢使用聚合来解决这些问题，并且有：

This is an example of a "set-within-sets" problem. I like to solve these using aggregation and having:

SELECT p.name, p.city
FROM programmers p INNER JOIN
     skills s
     ON p.id = s.programmer
GROUP BY p.name, p.city
HAVING SUM( s.lang = 'PHP' AND s.level > 1 ) > 0 AND
       SUM( s.lang = 'Java' AND s.level = 3 ) > 0;

编辑：

技能列表（不是原始问题的一部分），然后使用 group_concat（）：

If you want the list of skills (which wasn't part of the original question), then use group_concat():

SELECT p.name, p.city, group_concat(s.lang, ':', s.level separator '; ');
FROM programmers p INNER JOIN
     skills s
     ON p.id = s.programmer
GROUP BY p.name, p.city
HAVING SUM( s.lang = 'PHP' AND s.level > 1 ) > 0 AND
       SUM( s.lang = 'Java' AND s.level = 3 ) > 0;

这篇关于SQL中的高级过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！