sql - 如何从不同的表中获取两列并仅当第二列中的值小于 3 时才显示数据？

Question

我需要来自两个不同表的两列(i.Inventory_Name II，p.Inventory_Quanity IP)仅在 p.Inventory_Quanity IP < 3 时显示。

我尝试了 GROUP BY、HAVING 和 COUNT 原因，但它只为每个表生成重复数据的相同结果。

创造:

create table II ( 
  Inventory_ID            number,  
  Inventory_Name          varchar2(50),    
  Inventory_Type          varchar2(50),
  constraint pk_II primary key (Inventory_ID) 
)

create table IP (
  Inventory_Price       varchar2(50),      
  Inventory_Quanity     number,
  Inventory_ID            number,
  constraint pk_IP primary key (Inventory_Quanity),   
  constraint fk_IP_id foreign key (Inventory_ID)
  references II (Inventory_ID)
)

插入:

insert into II (Inventory_ID, Inventory_Type, Inventory_Name) values 
   (1,'Currency', 'Cash');
insert into II (Inventory_ID, Inventory_Type, Inventory_Name) values 
   (2,'Food', 'Apples');
insert into II (Inventory_ID, Inventory_Type, Inventory_Name) values 
   (3,'Currency', 'Electronic check');
insert into II (Inventory_ID, Inventory_Type, Inventory_Name) values 
   (4,'Food', 'Pizza');
insert into II (Inventory_ID, Inventory_Type, Inventory_Name) values 
   (5,'Cutlery', 'Plates');

insert into IP (Inventory_Price, Inventory_Quanity) values 
   ('$0', 60);
   insert into IP (Inventory_Price, Inventory_Quanity) values 
   ('$5', 5);
   insert into IP (Inventory_Price, Inventory_Quanity) values 
   ('+0', 80);
   insert into IP (Inventory_Price, Inventory_Quanity) values 
   ('$10', 2);
   insert into IP (Inventory_Price, Inventory_Quanity) values 
   ('$4', 1);

显示:

SELECT  i.Inventory_Name II, p.Inventory_Quanity IP FROM II i, IP p  
WHERE  p.Inventory_Quanity IN 
 (SELECT  p.Inventory_Quanity IP FROM IP p 
  where  p.Inventory_Quanity < 3GROUP BY  p.Inventory_Quanity HAVING COUNT ( p.Inventory_Quanity) < 3)

结果:

II                    IP
Cash                  1
Cash                  2
Apples                1
Apples                2
Electronic check      1
Electronic check      2
Pizza                 1
Pizza                 2
Plates                1
Plates                2

Answer 1

你可以试试下面——

DEMO

SELECT  i.Inventory_Name II, sum(p.Inventory_Quanity) IP FROM II i inner join IP p 
on i.Inventory_ID=p.Inventory_ID
group by i.Inventory_Name
having sum(p.Inventory_Quanity)<3

输出:

II      IP
Pizza   2
Plates  1