如果主桌有2个主键，则如何使用两个表增强我的子查询以用3个表进行搜索

Question

主表是'm_order'，其中包含订单及其详细信息

M_order
row_id |Order_Num | Order_Status | created     | created_by | Account_ID
--------------------------------------------------------------
1-a    | 1        | complete     | 2016-08-25  |    jhon    | 3-a
1-b    | 2        | pending      | 2016-08-26  |    mike    | 3-b
1-c    | 3        | failed       | 2015-08-27  |    mike    | 3-c
1-d    | 4        | pending      | 2015-08-25  |    jhon    | 3-d
1-e    | 5        | failed       | 2016-08-26  |    mike    | 3-e
1-f    | 6        | New          | 2017-03-27  |    jhon    | 3-f

第二个表是" order_itmes"，其中包含每个顺序的产品列表

Order_itmes
row_id |Order_id | Product_ID
--------------------------------------------------------------
2-a    | 1-a     |  Prod_1    
2-b    | 1-b     |  Prod_2   
2-c    | 1-c     |  Prod_1 
2-d    | 1-d     |  Prod_1    
2-e    | 1-e     |  Prod_1   
2-f    | 1-f     |  Prod_3  

'm_order'表的row_id是唯一的，并且在表'order_items'中可用为order_id

我有一个查询，该查询显示了来自'order_items'

的特定product_id的订单

Select Order_Num,Order_status,created ,created_by
  from M_order where row_id in (select Order_id from Order_itmes where 
Product_ID='Prod_1')
  where Order_Status='Failed'
     Order By created desc

如果我有一个第三表'帐户'，该列是counth row_id作为counder_id中的同一列的councel_id offer offer的'm_order'

 Accounts
 row_id  | Name 
--------------------------------------------------------------
 3-a     | cutomer1
 3-b     | cutomer2
 3-c     | cutomer3
 3-d     | cutomer4
 3-e     | cutomer5
 3-f     | cutomer6

如何增强上述查询以在结果中列出客户名称，并显示以下

Order_Num | Order_status | created   | created_by  |  name
-------------------------------------------------------------   
5         | failed       |2016-08-26 | mike        | customer5
3         | failed       |2015-08-27 | mike        | customer3

Answer 1

我相信在这种情况下，最好使用JOIN，因为一旦我们拥有2个以上的表，子查询就会变得复杂。一个简单的JOIN在这里可以工作，例如：

SELECT o.order_num, o.order_status, o.created, o.created_by, a.name
FROM m_order o JOIN order_items oi ON o.row_id = oi.order_id
JOIN accounts a ON o.account_id = a.row_id
WHERE oi.product_id = 'Prod_1';

Answer 2

您可以将join添加到Accounts

select o.Order_Num, o.Order_status, o.created, o.created_by, a.name
from M_order o
  inner join Accounts a
    on o.Account_ID = a.row_id
where o.row_id in (
    select Order_id 
    from Order_items 
    where Product_ID='Prod_1'
    )
  and o.Order_Status='Failed'
order by o.created desc

Answer 3

表如何有2个pk？

我没有对索引发表评论，因为您发布了真实的表结构和真实数据类型。

wht是ID的目的为1-A或2-A或3-A？

表应自动增量ID作为CI。

还订购表包含数百万个记录，因此您不能在不使用页面的情况下简单地查询任何内容。

尝试这个，

declare @M_order table(row_id varchar(30),Order_Num int,Order_Status varchar(30)
,created date,created_by varchar(30),Account_ID varchar(30))
insert into @M_order VALUES
('1-a',1,'complete','2016-08-25','jhon','3-a')
,('1-b',2,'pending','2016-08-26','mike','3-b')
,('1-c',3,'failed','2015-08-27','mike','3-c')
,('1-d',4,'pending','2015-08-25','jhon','3-d')
,('1-e',5,'failed','2016-08-26','mike','3-e')
,('1-f',6,'New ','2017-03-27','jhon','3-f')
declare @Order_itmes table(row_id varchar(30),Order_id varchar(30), Product_ID varchar(30))
insert into @Order_itmes VALUES
('2-a','1-a','Prod_1'),('2-b','1-b','Prod_2'),('2-c','1-c','Prod_1') 
,('2-d','1-d','Prod_1'),('2-e','1-e','Prod_1'),('2-f','1-f','Prod_3') 
declare @Accounts table(row_id varchar(30),Name varchar(30)) 
insert into @Accounts values
 ('3-a','cutomer1') ,('3-b','cutomer2') ,('3-c','cutomer3')
 ,('3-d','cutomer4') ,('3-e','cutomer5') ,('3-f','cutomer6')

 select mo.Order_Num ,mo.Order_status , mo.created , mo.created_by ,name
 from @M_order MO
 inner join @Accounts A
 on mo.Account_ID=a.row_id
 where Order_status='failed'
 and
 exists(
 select Order_id from @Order_itmes OI where 
Product_ID='Prod_1' and oi.Order_id=mo.row_id
 )