sql - 在相关子查询中查找多个不同的属性

Question

我试图理解相关子查询，但无法理解这个问题。我需要列出所有具有不止一个不同供应商的 POS 数据的 bookjob 信息。

我必须在 job_id 上使用相关子查询。我不能使用 group by 或两者之一。

SELECT *
FROM bookjobs a
WHERE EXISTS 
            (SELECT DISTINCT vendor_id 
             FROM pos b 
             WHERE b.job_id = a.job_id);

job_id      cust_id     job_date    descr       jobtype
----------  ----------  ----------  ----------  ----------
002         E05         1990-03-03  BUS REPORT  N
004         A01         1990-01-01  PAMPHLETS   R
005         A01         1989-11-23  GOVT        N
006         D04         1988-07-04  CAMPAIGN    H

我也不能用这个来回答这个问题：

SELECT *
FROM bookjobs a
WHERE EXISTS 
             (SELECT job_id 
              FROM pos b 
              WHERE b.job_id = a.job_id 
              GROUP BY job_id 
              HAVING COUNT(job_id) > 1 );

job_id      cust_id     job_date    descr       jobtype
----------  ----------  ----------  ----------  ----------
002         E05         1990-03-03  BUS REPORT  N
004         A01         1990-01-01  PAMPHLETS   R
005         A01         1989-11-23  GOVT        N

架构

CREATE TABLE pos (
  po_id     CHAR(3) NOT NULL,
  job_id    CHAR(3) NOT NULL,
  po_date   DATE,
  vendor_id CHAR(3),

  PRIMARY KEY (po_id),
  FOREIGN KEY (job_id) REFERENCES bookjobs(job_id)

CREATE TABLE bookjobs (
  job_id   CHAR(3) NOT NULL,
  cust_id  CHAR(3) NOT NULL,
  job_date DATE,
  descr    CHAR(10),
  jobtype  CHAR(1),

  PRIMARY KEY (job_id),
  FOREIGN KEY (cust_id) REFERENCES publishers(cust_id)

预期的结果应该是：

job_id      cust_id     job_date    descr       jobtype
----------  ----------  ----------  ----------  ----------
002         E05         1990-03-03  BUS REPORT  N
004         A01         1990-01-01  PAMPHLETS   R
005         A01         1989-11-23  GOVT        N

标签： sqlsqlite

Answer 1

尝试在按 JobId 分组时计算 where 子句中的供应商

SELECT
     *
FROM
    BookJobs AS B
WHERE
    EXISTS 
        (
        SELECT 
            P.JobId
            ,Count (Vender)
        FROM 
            Pos P
        WHERE
            P.JobId = B.JobId
        Group By 
            P.JobId
        HAVING 
            Count(Vender) > 1
        )