如何与内部连接结合

Question

我有三个独立的桌子，bs_products，td_products和fv_products。

    SELECT 
bso.order_id, bso.order_num, bso.Salesman, bso.salesman_name, bso.date, bso.status,

bsp.product_id, bsp.product_branch, bsp.product_name, bsp.description, bsp.vatable, bsp.critical_stock,
bsp.quantity, bsp.sell_price, bsp.category_id, bsp.expiry_date, bsp.date, bsp.isEmpty, bsp.empties,

bss.sws_id, bss.sws_proname, bss.sws_isEmpty, bss.sws_category, bss.sws_unitprice, bss.sws_prodesc,
bss.sws_proexp, bss.sws_vat, bss.sws_number, bss.date, bss.sws_salesman, bss.sws_route, bss.sws_smname,
bss.sws_driver, bss.sws_plate, bss.sws_vehicle, bss.sws_load, bss.sws_productid, bss.sws_quantity
FROM bs_orders as bso INNER JOIN bs_products as bsp
INNER JOIN bs_sws as bss WHERE status = 'Completed' 
AND bso.date = '11/30/2016' 
AND bso.order_num = bss.sws_number 
AND bss.sws_productid = bsp.product_id 

UNION

SELECT 

tdo.order_id, tdo.order_num, tdo.Salesman, tdo.salesman_name, tdo.date, tdo.status,

tdp.product_id, tdp.product_branch, tdp.product_name, tdp.description, tdp.vatable, tdp.critical_stock,
tdp.quantity, tdp.sell_price, tdp.category_id, tdp.expiry_date, tdp.date, tdp.isEmpty, tdp.empties,

tds.sws_id, tds.sws_proname, tds.sws_isEmpty, tds.sws_category, tds.sws_unitprice, tds.sws_prodesc,
tds.sws_proexp, tds.sws_vat, tds.sws_number, tds.date, tds.sws_salesman, tds.sws_route, tds.sws_smname,
tds.sws_driver, tds.sws_plate, tds.sws_vehicle, tds.sws_load, tds.sws_productid, tds.sws_quantity

FROM td_orders as tdo INNER JOIN td_products as tdp
INNER JOIN td_sws as tds WHERE status = 'Completed' 
AND tdo.date = '11/30/2016' 
AND tdo.order_num = tds.sws_number 
AND tds.sws_productid = tdp.product_id

UNION

SELECT 

fvo.order_id, fvo.order_num, fvo.Salesman, fvo.salesman_name, fvo.date, fvo.status,

fvp.product_id, fvp.product_branch, fvp.product_name, fvp.description, fvp.vatable, fvp.critical_stock,
fvp.quantity, fvp.sell_price, fvp.category_id, fvp.expiry_date, fvp.date, fvp.isEmpty, fvp.empties,

fvs.sws_id, fvs.sws_proname, fvs.sws_isEmpty, fvs.sws_category, fvs.sws_unitprice, fvs.sws_prodesc,
fvs.sws_proexp, fvs.sws_vat, fvs.sws_number, fvs.date, fvs.sws_salesman, fvs.sws_route, fvs.sws_smname,
fvs.sws_driver, fvs.sws_plate, fvs.sws_vehicle, fvs.sws_load, fvs.sws_productid, fvs.sws_quantity

FROM fv_orders as fvo INNER JOIN fv_products as fvp
INNER JOIN fv_sws as fvs WHERE status = 'Completed' 
AND fvo.date = '11/30/2016' 
AND fvo.order_num = fvs.sws_number 
AND fvs.sws_productid = fvp.product_id

我试着在phpMyAdmin上运行这个程序，但是它给了我一个错误

1221 -不正确使用UNION和限值

对此的正确查询是什么？

_orders表

order_id (Primary)  int(11)                 
order_num           int(255)                    
Salesman            varchar(255)                    
salesman_name       varchar(255)                    
date                varchar(255)                
status              varchar(255)

_products表

product_id (Primary)    int(11)                 
product_branch          varchar(255)                    
product_name            varchar(255)                    
description             varchar(255)                    
vatable                 tinyint(1)                  
critical_stock          int(11)             
quantity                int(11)                 
sell_price              double                  
category_id             int(11)                 
expiry_date             varchar(255)                    
date                    varchar(255)                    
isEmpty                 tinyint(1)                  
empties                 int(11) 

_sws表

sws_id (Primary)    int(11) No              
sws_proname             varchar(255)                    
sws_isEmpty             tinyint(1)                  
sws_category            int(11)                 
sws_unitprice           double                  
sws_prodesc             varchar(255)                    
sws_proexp              varchar(255)                    
sws_vat                tinyint(1)               
sws_number             int(255)                 
date                    varchar(255)                    
sws_salesman            varchar(255)            
sws_route   varchar(255)                    
sws_smname  varchar(255)                    
sws_driver  varchar(255)                    
sws_plate   varchar(255)                
sws_vehicle varchar(255)                    
sws_load        int(255)                
sws_productid   int(255)                    
sws_quantity    int(255)    

Answer 1

在使用UNION时，每个子查询中的列数必须相同，而且它们必须具有相同的数据类型。

您需要调整查询，以便不使用*，而是从每个表定义每个列。

有关更多信息，请参见以下内容：

• http://www.mysqltutorial.org/sql-union-mysql.aspx
• https://dev.mysql.com/doc/refman/5.7/en/union.html

要使用UNION操作符，需要遵守一些规则：

• 相应SELECT语句中出现的列数必须相等。
• 列出现在每个SELECT语句的相应位置，必须具有相同的数据类型，或者至少是可转换数据类型。

Answer 2

你得重新考虑一下你的计划。

要执行联合，您需要具有相同名称和数据类型的字段。您不能简单地选择*并期望不同的表组合在一个结果集中。

您可能需要在select的不同部分更改名称或添加虚拟列以使其工作。

如果您张贴表结构和字段名，人们可能会帮助您更多。