Mysql SELECT ...WHERE IN (多个sub从多个表中选择)
Mysql需要从一个表中获取数据，其中的in与另一个表中的列值相同。但是从另一个表中只能获取列值，其in与其他两个表中的列值相同(IN)。
各表如下：
表名为sales_invoices，包含列(下面我不会显示所有列，有些列是问题所必需的)。
IdInvoices | Date
包含列的表名purchase_invoices
IdInvoicesP | Date
包含列的表名items
IdI | IdGoodsServices | IdInvoices
包含列的表名goods_services
IdGs (唯一值)
下面是表之间的链接
IdGs = IdGoodsServices
IdInvoices ( items) = IdInvoices (sales_invoices) = IdInvoicesP
已尝试多种代码变体。在所有情况下都会出现错误。下面是最后使用的代码。
SELECT count(DISTINCT `IdGs`) FROM `goods_services` WHERE `IdGs` IN
( SELECT `IdGoodsServices` FROM `items` WHERE `IdInvoices` IN
( SELECT `IdInvoices`, `IdInvoicesP` FROM
`sales_invoices`, `purchase_invoices` WHERE `Date` <= ? )
) 想要计算销售和采购发票中包含的IdGs (产品或商品/服务的代码)，开(写)到特定日期。
在goods_services和sales_invoices / purchase_invoices之间没有直接链接。表items同时链接到goods_services和(sales_invoices / purchase_invoices)
使用上面的代码获取错误
SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'Date' in where clause is ambiguous
仅从一个表中尝试了子子选择，得到了一些结果，没有错误。
下面是查询
SELECT count(DISTINCT `IdGs`) FROM `goods_services` WHERE `IdGs` IN
( SELECT `IdGoodsServices` FROM `30u2s5r_items` WHERE `IdInvoices` IN
( SELECT `IdInvoicesP` FROM
`30u2s5r_purchase_invoices` WHERE `Date` <= ? )
) 下面是工作代码(就像我之前测试的那样)。
SELECT count(DISTINCT `IdGs`) FROM `goods_services` WHERE `IdGs` IN
(SELECT `IdGoodsServices` FROM `items` WHERE
`IdInvoices` IN
(SELECT DISTINCT `IdInvoices` FROM `sales_invoices`
WHERE `sales_invoices`.`Date` <= ? )
OR
`IdInvoices` IN
( SELECT DISTINCT `IdInvoicesP` FROM `purchase_invoices`
WHERE `purchase_invoices`.`Date` <= ? )
)https://stackoverflow.com/questions/56515979
复制相似问题