可能在SQL中找到重复项

I've been trying the following suggestions from this post, but this does not apply for my case or at least I am not capable of adapting the query to my needs.

我一直在尝试这篇文章中的以下建议，但这不适用于我的情况，或者至少我无法根据我的需要调整查询。

I have three tables: one stands for documents header, one stands for documents lines and one stands for item's information (Code, Description etc).

我有三个表：一个代表文档标题，一个代表文档行，一个代表项目的信息（代码，描述等）。

I would like to extract all the documents number that have the same value (this is the information from the documents header table), the same items (the code of the item) and the same quantity (from the lines of the documents tables). How can I extract this information? Thanks

我想提取所有具有相同值的文档编号（这是文档头表中的信息），相同的项目（项目的代码）和相同的数量（来自文档表的行）。我该如何提取这些信息？谢谢

The tables are -

表格是 -

DocHeader              DocLines             Items

ID                     fDocID               ID 
Code                   fItemID              Code
Date                   Quantity             Description
----                   --------             -----------
TotalValue             etc                  etc

Later edit

稍后编辑

Output should like something like:

输出应该像：

DocCode    ItemCode   Quantity   TotalValue
01         001            5        1000
01         002            5        1000
01         003            4        1000 
02         001            5        1000 
02         002            5        1000
02         003            4        1000

---

DDL

DDL

create table DocHeader 
(
    Id bigint not null identity(1,1) primary key clustered
    , Code nvarchar(32) not null
    , [Date] datetime not null
)
go
create table Items 
(
    Id bigint not null identity(1,1) primary key clustered
    , Code nvarchar(32) not null
    , [Description] nvarchar(256)
    , UnitPrice money not null
)
go
create table DocLines
(
    Id bigint not null identity(1,1) primary key clustered
    ,fDocId bigint not null constraint fk_DocLines_fDocId foreign key references DocHeader(Id)
    ,fItemId bigint not null constraint fk_DocLines_fDocId foreign key references Items(Id)
    ,Quantity int not null
)
go
create view vDocHeader as
select dh.*
, x.TotalValue
from DocHeader
left outer join 
(
    select dl.fDocId
    , sum(dl.Quantity * i.UnitPrice) TotalValue
    from DocLines dl
    inner join Items i
    on i.Id = dl.fItemId
    group by dl.fDocId
) x
on x.fDocId = dh.Id

1 个解决方案

#1

Why not simple grouping?

为什么不简单分组？

SELECT dh.Code AS DocCode, i.Code AS ItemCode, Quantity, SUM(dl.Quantity * i.UnitPrice) AS TotalValue
FROM DocHeader dh
LEFT JOIN DocLines dl ON dl.fDocId=dh.id
JOIN Items i ON i.Id = dl.fItemId
GROUP BY dh.Code,i.Code,Quantity