获取有关发票的信息，并使用单个SELECT命令返回发票上的发票项目数

Question

我使用的是chinook数据库和sqlite3。我的目标是返回一个发票列表，其中包含特定客户的发票id、发票日期和发票上的项目数。前两个非常简单，

SELECT InvoiceId, InvoiceDate 
FROM invoices 
WHERE CustomerId = 2;

退货：

1  |2009-01-01 00:00:00    
12 |2009-02-11 00:00:00    
67 |2009-10-12 00:00:00    
196|2011-05-19 00:00:00    
219|2011-08-21 00:00:00    
241|2011-11-23 00:00:00    
293|2012-07-13 00:00:00

但是，发票行项目在另一个表中。我可以用计算与特定发票对应的发票

SELECT count(*) 
FROM invoice_items 
WHERE Invoiceid = 12;

返回CCD_ 1

但我想返回一个列表，如：

1 |2009-01-01 00:00:00|2    
12|2009-02-11 00:00:00|14

Answer 1

您的查询需要一个内部联接；它看起来像这样：

SELECT 
    invoices.InvoiceId, invoices.InvoiceDate,
    COUNT(DISTINCT(items.ID)) AS Items 
FROM 
    invoices
JOIN 
    invoice_items AS items ON invoices.ID = itemsID
GROUP BY invoices.InvoiceId

当然，我猜的是ID列的名称

Answer 2

尝试如下子查询：

select invoiceid, invoicedate, (select count(*) from invoice_items where invoiceid = i.invoiceid) as CountInvoiceItems from invoices

要获得所有结果，否则您可以为发票12重新添加where条款。

Answer 3

要获得特定客户的发票，您应该使用join

这里有一个例子：

SELECT InvoiceId, InvoiceDate, count(*) 
FROM invoices 
JOIN invoice_items ON invoices.InvoiceId=invoice_items.invoiceid 
GROUP BY invoices.InvoiceId