SQL Server查询帮助 [英] SQL Server Query Help

问题描述

我需要在db中查询两个表。第一个表是发票表，第二个表是行项目表。给定客户名称，我需要在发票表中搜索具有匹配客户名称的发票并检索：

客户ID

InvoiceNumber

InvoiceDate

ReceiptNumber

ReferenceNumber

TransactionNumber

被许可人



然后查找并总计每张发票的所有订单项总结

小计

运费

税

总计（总和）小计，运费，税收）



我可以根据客户编号搜索发票表。我有一些建议，但我的第一个问题版本不够具体。



如果你能提供帮助我将不胜感激。



汤姆

解决方案

你可以使用这样的东西：

 选择 sum（columnName），tableOne.CustId 来自 tableOne 内部 > join  tableTwo  on  tableOne.CustId = tableTwo.Id  group  < span class =code-keyword> by  tableOne.CustId 

你想要将两张桌子加入并做一个发票表中的客户编号上的WHERE，它将自动为您提供与该客户编号匹配的发票的所有行项目。谷歌如何使用JOIN，如果你不知道如何。

代码应该像 -

 选择 T1.Invoicenum，sum（T2.itemline）totalItemLine  from 发​​票T1  join  ItemLine T2  on  T1.Invoiceno = T2.invoiceno 其中 T1.custId = 
  group   by < ; pre lang =   C＃> T1.Invoicenum 

I need to query two tables in a db. The first table is an invoice table and the second is a line items table. Given a customer name I need to search the invoice table for invoices with a matching customer name and retrieve:
CustomerID
InvoiceNumber
InvoiceDate
ReceiptNumber
ReferenceNumber
TransactionNumber
Licensee

and then find and total all line items for each invoice summing
SubTotal
Shipping
Tax
Total (sum of subtotal,shipping, tax)

I can do the search of the invoice table based on a customer number. I have had some suggestions, but my first version of the question was not specific enough.

If you can help I would be grateful.

Tom

解决方案

You can make use of something like this:

select sum(columnName),tableOne.CustId from tableOne inner join tableTwo on tableOne.CustId = tableTwo.Id group by tableOne.CustId

You'll want to "JOIN" the two table together and do a "WHERE" on the customer number in the invoice table and that will automatically give you all line items for the invoices that match that customer number. Google for how to use "JOIN" if you don't know how.

code should be like-

Select T1.Invoicenum, sum(T2.itemline) totalItemLine from Invoice T1 join ItemLine T2 on T1.Invoiceno=T2.invoiceno where T1.custId= 
group by <pre lang="C#">T1.Invoicenum

这篇关于SQL Server查询帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！