34,576
社区成员
发帖
与我相关
我的任务
分享
--1 select a.*,项目所有合同的金额 from dbo.项目 as a inner join (select 项目编号,sum(合同金额) 项目所有合同的金额 from 合同 group by 项目编号) b on a.项目编号=b.项目编号
--2 select a.项目编号,sum(付款金额) 项目所有合同金额 from dbo.合同 a left join dbo.付款 b on a.合同编号=b.合同编号 group by a.项目编号
--3 select e.项目编号,e.项目名称,d.项目所有合同已付款金额 from (select a.项目编号,sum(付款金额) 项目所有合同已付款金额 from dbo.合同 a left join dbo.付款 b on a.合同编号=b.合同编号 group by a.项目编号) as d inner join dbo.项目 e on d.项目编号=e.项目编号
--4 select e.项目编号,e.项目名称,d.项目所有合同已付款金额,f.项目所有合同的金额 from (select a.项目编号,sum(付款金额) 项目所有合同已付款金额 from dbo.合同 a left join dbo.付款 b on a.合同编号=b.合同编号 group by a.项目编号) as d inner join dbo.项目 e on d.项目编号=e.项目编号 inner join (select a.*,项目所有合同的金额 from dbo.项目 as a inner join (select 项目编号,sum(合同金额) 项目所有合同的金额 from 合同 group by 项目编号) b on a.项目编号=b.项目编号) f on d.项目编号=f.项目编号 -
CREATE TABLE Vendor
(
VendorID int identity,
VendorName VARCHAR(20)
)
GO
CREATE TABLE List
(
ListID int identity,
VendorID int
)
GO
CREATE TABLE Record
(
RecordID int identity,
ListID int,
[Money] int
)
GO
INSERT INTO Vendor select 'Huawei'
INSERT INTO Vendor select 'IBM'
INSERT INTO Vendor select 'Google'
INSERT INTO List select 1
INSERT INTO List select 2
INSERT INTO List SELECT 3
INSERT INTO Record SELECT 1,200
INSERT INTO Record SELECT 2,300
INSERT INTO Record SELECT 3,400
SELECT DISTINCT L.ListID,V.VendorNAME,R.MONEY
FROM Vendor v,LIST L
LEFT JOIN
(
SELECT r.ListID, SUM(R.[Money])OVER(PARTITION BY R.ListID) [MONEY]
FROM Record r
)R
ON R.ListID=L.ListID
WHERE V.VendorID=L.VendorID
ORDER BY L.ListID
ListID VendorNAME MONEY
----------- -------------------- -----------
1 Huawei 200
2 IBM 300
3 Google 400
(3 row(s) affected)
SELECT 表单.表单ID, 供应商.供应商Name, SUM(记录.金额)
FROM 表单,记录,供应商
WHERE 表单.供应商ID=供应商.供应商ID AND 记录.表单ID=表单.表单ID
Group By 表单.表单ID,供应商.供应商Name
SELECT 表单.表单ID, 供应商.供应商Name, SUM(记录.金额)
FROM 表单,记录,供应商
WHERE 表单.供应商ID=供应商.供应商ID AND 记录.表单ID=表单.表单ID
Group By 表单ID,供应商.供应商Name