如何使sql(postgresql)查询?
问题描述:
/* Create a table called tbl1 */
CREATE TABLE tbl1(id integer PRIMARY KEY, name text, lang text);
/* Create few records in this table */
INSERT INTO tbl1 VALUES(1,'John', 'Perl');
INSERT INTO tbl1 VALUES(2,'Pete', 'Perl');
INSERT INTO tbl1 VALUES(3,'Sam', 'Java');
INSERT INTO tbl1 VALUES(4,'John', 'Php');
INSERT INTO tbl1 VALUES(5,'Sam', 'Perl');
INSERT INTO tbl1 VALUES(6,'Sam', 'Php');
INSERT INTO tbl1 VALUES(7,'Pete', 'C');
INSERT INTO tbl1 VALUES(8,'Bob', 'Java');
派生知道Perl和Php的人的名字。如何使sql(postgresql)查询?
答
SELECT distinct(name)
FROM tbl1
WHERE name IN (SELECT name FROM tbl1 WHERE lang = 'Perl')
AND name IN (SELECT name FROM tbl1 WHERE lang = 'Php');
是好答案?我不这么认为。 我想找到更好的方法。
2个额外的答案
SELECT n FROM (
SELECT tbl1.name, string_agg(tbl1.lang, '-')
FROM tbl1
GROUP BY name) AS t (n, l)
WHERE
position('Perl' in l) <> 0
AND
position('Php' in l) <> 0;
===================================
SELECT t1.name FROM (
SELECT name FROM tbl1 WHERE lang = 'Perl'
) AS t1, (
SELECT name FROM tbl1 WHERE lang = 'Php'
) AS t2
WHERE t1.name = t2.name;
你有尝试过什么吗? –
首先,规范化你的数据... –
从数据透视到半哈希将会有大约20-30个可行的答案。这似乎是作业,但你应该尝试学习一个答案。 – Twelfth