postgresselect如果数据有两个不同值的实例sql

Question

我有一个包含多个title实例的表，有些是精装的(h)，有些是平装的(p)

            title            | type
-----------------------------+------
 Franklin in the Dark        | p
 Little Women                | p
 The Cat in the Hat          | p
 Dune                        | p
 The Shining                 | p
 Programming Python          | p
 Goodnight Moon              | p
 2001: A Space Odyssey       | h
 Dynamic Anatomy             | p
 Bartholomew and the Oobleck | p
 The Cat in the Hat          | h
 Dune                        | h
 The Velveteen Rabbit        | p
 The Shining                 | h
 The Tell-Tale Heart         | p
 2001: A Space Odyssey       | p

我正在尝试返回同时具有平装和精装副本的实例。

理想情况下，该表应该只返回4个标题。

*edit这是两个不同表的一部分。

 7808 | The Shining                 |      4156 |          9
  4513 | Dune                        |      1866 |         15
  4267 | 2001: A Space Odyssey       |      2001 |         15
  1608 | The Cat in the Hat          |      1809 |          2
  1590 | Bartholomew and the Oobleck |      1809 |          2
 25908 | Franklin in the Dark        |     15990 |          2

 0385121679 |    7808 |       2 |           75 | 1993-10-01  | h
 1885418035 |     156 |       1 |          163 | 1995-03-28  | p
 0929605942 |     156 |       2 |          171 | 1998-12-01  | p
 0441172717 |    4513 |       2 |           99 | 1998-09-01  | p
 044100590X |    4513 |       3 |           99 | 1999-10-01  | h
 0451457994 |    4267 |       3 |          101 | 2000-09-12  | p
 0451198492 |    4267 |       3 |          101 | 1999-10-01  | h
 0823015505 |    2038 |       1 |           62 | 1958-01-01  | p
 0596000855 |   41473 |       2 |          113 | 2001-03-01  | p

Answer 1

这也可以。

SELECT TITLE
FROM BOOKS
GROUP BY TITLE
HAVING COUNT(DISTINCT TYPE) > 1

Answer 2

有几种方法可以做到这一点。如果你只想要一本既有精装又有平装的书的书名(我假设这是仅有的两种选择)。然后，您可以执行如下查询:

select title, count(*) from book group by title having count(*) > 1

你也可以加入到桌子。

select t0.title from 
(
  select title from book where btype = 'h'
) t0 
    inner join 
(
  select title from book where btype = 'p'
) t1 on t0.title = t1.title

为两个表编辑

select * from table_one where bookid in (
select t0.bookid
from
( 
    select bookid from table_two where type = 'h'
) t0
inner join 
(
    select bookid from table_two where type = 'p'
) t1
on t0.bookid = t1.bookid
) t2

Answer 3

这对你有用吗?

SELECT title
FROM table a
WHERE type = 'h' AND
      EXISTS (SELECT 1
              FROM table 
              WHERE title = a.title AND
              type = 'p')