Oracle SQL：使用group by（count）和having子句进行SQL join

Question

我有三个表

表1.) Sale
表2.) ItemsSale
表3.) Items

表1和表2共有ID，表2和表3共有ITEMS。

我尝试了一个查询语句，但似乎无法正确获取结果，有些困扰。

我的目标是选择所有只有一行数据并符合特定条件的行，以下是我的查询语句：

select *
from sales i
    inner join itemssales j on i.id = j.id
    inner join item u on j.item = u.item
where u.code = ANY ('TEST','HI') and
    i.created_date between TO_DATE('1/4/2016 12:00:00 AM','MM/DD/YYYY HH:MI:SS AM') and
    TO_DATE('1/4/2016 11:59:59 PM','MM/DD/YYYY HH:MI:SS PM')
group by i.id
having count(i.id) = 1

在ItemSale表中有两个条目，但在销售表中只有一个。这很好……但我需要构建一个查询，只返回给我一个记录。

我认为问题出在“ANY”部分，查询只返回一行，而这一行是不符合“ANY（'TEST'，'HI'）”条件的记录。但实际上，该特定ID的记录在ItemSales中有两个记录。

我需要只返回那些真正只有一个记录的记录。

感谢您的任何帮助。

--EDIT:


COL1 | ID
-----|-----
2    |  26
3    | 85
1    | 23
1    | 88
1    | 6
1    | 85

我还会将它们分组，并确保计数等于1，但是你可以看到，ID 85在这里作为一条记录出现，这是一个“假阳性”，因为itemsales表中实际上有两条记录。

我甚至尝试在选择后更改查询为j.id，因为j是具有两个记录的表，但没有用。

--- EDIT

Sale table contains:
ID
---
85

Itemsales table contains:

ID | Position | item_id
---|----------|---------
85 |     1    |   6
85 |     2    |   7

Items table contains:

item_id | code
--------|------
   7    | HI
   6    | BOOP

它正在返回的记录是代码为“BOOP”的记录。

谢谢。

- RobertC

---

你能提供一下你目前得到的输出样本以及你期望得到的吗？ - Danilo Tommasina

那么您是否想要仅具有一种代码（ANY，HI）的记录？ - Tim Biegeleisen

正确的，但是它不能在itemssales表中出现超过一次，这就是问题所在，我认为上面的方法可以解决，但实际上并不行。 - RobertC

“样本数据”指涉及到的每个表格的数据，您能提供一些吗？（不是您现有的查询结果） - Paul Maxwell

你的意思是要查询只包含一个商品的所有销售记录吗？我认为你需要重新构思你的问题，因为它确实令人困惑，也许可以省略掉“相信”、“猜测”这些词。请明确你尝试了什么，期望得到哪些输出以及输入是什么。 - Ely

@RobertC - 请尽量保持对象的命名一致性。你的问题在单数和复数名称之间随机切换。 - APC

Answer 1

“我需要仅返回那些只有一个记录的合法记录。” 我的理解是，您只想返回仅具有一个ITEM的销售记录。此外，您需要该ITEM符合您的其他条件。以下是一种方法，对于小规模数据来说可以正常工作，但可能无法很好地扩展。如果没有正确的表描述和数据概要，就无法提供高效的解决方案。

with itmsal as    
            ( select sales.id 
              from itemsales
                    join sales on sales.id = itemsales.id 
              where sales.created_date >= date '2016-01-04'
              and sales.created_date < date '2016-01-05'
              group by sales.id having count(*) = 1)
select sales.*
       , item.*
from itmsal
     join sales on sales.id = itmsal.id
     join itemsales on itemsales.id = itmsal.id
     join items on itemsales.item = itemsales.item
where items.code in ('TEST','HI')

- APC

Answer 2

我认为你正在尝试限制结果，使得条目只能具有“TEST”或“HI”的代码。

select
     sales.*
from (
      select
          s.id
      from Sales s
          inner join Itemsales itss on s.id = itss.id
          inner join Items i on itss.item_id = i.item_id
      group by
          s.id
      where s.created_date >= date '2016-01-04'
          and s.created_date < date '2016-01-05'
      having
          sum(case when i.code IN('TEST','HI') then 0 else 1 end) = 0
     ) x
inner join sales on x.id = sales.id
... /* more here as required */

这个结构仅返回具有仅包含这两个代码的项目的sales.id。

请注意，可以使用公共表达式（CTE）来完成此操作，但我更喜欢仅在有优势时使用它们 - 在这里我没有看到任何优势。

- Paul Maxwell

Answer 3

如果我理解得正确，这个可能会起作用（未经测试）：

select *
from sales s
inner join (
   select i.id, count( i.id ) as cnt
   from sales i
   inner join itemssales j on i.id = j.id
   inner join item u on j.item = u.item and u.code IN ('TEST','HI')
   where i.created_date between TO_DATE('1/4/2016 12:00:00 AM','MM/DD/YYYY HH:MI:SS AM') and
TO_DATE('1/4/2016 11:59:59 PM','MM/DD/YYYY HH:MI:SS PM')
   group by i.id
) sj on s.id = sj.id and sj.cnt = 1

- Danilo Tommasina