SqlServer中Group By高级使用--Inner Join分组统计
当我们在使用Group By进行分组统计的时候，往往都会根据一个字段进行分组统计（并且这个字段是关联在这张表中的），这应该好实现。
但是当这张数据表中的某些数据要根据列一张表的某个字段进行分组统计的时候，往往会借助Inner Join来实现，以下图为例：
现在要达到的效果是：表2中的数据要根据表1来进行分组统计，在统计表2中的数据时，表2中的ReadDate要大于或者等于表1中的CreateDate，并且小于表1中的EndDate。
以下是这两张表的Sql语句：
表1：
SELECT TOP 2 aa.AccountID,aa.AccountCode,aa.AccountName,aa.CreateDate,aa.EndDate,aa.EndAccountFlag
FROM ArchiveAccount aa
WHERE aa.AccountID IN
(
SELECT DISTINCT rmfd.AccountID FROM ReadMeterFeeDetail rmfd
LEFT JOIN ArchiveAccount aa ON rmfd.AccountID=aa.AccountID
WHERE rmfd.CustID IN
(
SELECT CustID FROM Archive_CustInfo aci
WHERE aci.CbbID IN
(
SELECT aci2.CbbID FROM Archive_CbbInfo aci2
WHERE aci2.GaswellID=2 AND aci2.CompanyID=1
)
AND aci.CompanyID=1
) AND aa.CompanyID=1 AND aa.EndAccountFlag=1
)
ORDER BY aa.CreateDate DESC
表2：
SELECT (ModifyGasCount+ReadMeterNumber-LastReadMeterNumber) DData,ReadDate FROM
(
SELECT rmgd.ReadDate,rmgd.LastReadMeterNumber,rmgd.ReadMeterNumber,rmgd.ModifyGasCount
FROM ReadMeterGaswellDetail rmgd
LEFT JOIN Archive_Gaswell ag ON rmgd.GaswellID=ag.GaswellID
LEFT JOIN DC_Archive_Llj dal ON rmgd.lljID=dal.id
WHERE rmgd.GaswellID=2 AND dal.startorstop=0 AND ag.CompanyID=1
) tb_Total
使用Inner Join就可以按照表1来进行分组统计，Sql语句如下所示：
--表2
SELECT SUM(DData) DData,AccountID FROM
(
SELECT (ModifyGasCount+ReadMeterNumber-LastReadMeterNumber) DData,AccountID FROM
(
SELECT rmgd.ReadDate,rmgd.LastReadMeterNumber,rmgd.ReadMeterNumber,rmgd.ModifyGasCount,aa.AccountID,aa.CreateDate
FROM ReadMeterGaswellDetail rmgd
LEFT JOIN Archive_Gaswell ag ON rmgd.GaswellID=ag.GaswellID
LEFT JOIN DC_Archive_Llj dal ON rmgd.lljID=dal.id
INNER JOIN
(
--表1
SELECT TOP 2 aa.AccountID,aa.AccountCode,aa.AccountName,aa.CreateDate,aa.EndDate,aa.EndAccountFlag
FROM ArchiveAccount aa
WHERE aa.AccountID IN
(
SELECT DISTINCT rmfd.AccountID FROM ReadMeterFeeDetail rmfd
LEFT JOIN ArchiveAccount aa ON rmfd.AccountID=aa.AccountID
WHERE rmfd.CustID IN
(
SELECT CustID FROM Archive_CustInfo aci
WHERE aci.CbbID IN
(
SELECT aci2.CbbID FROM Archive_CbbInfo aci2
WHERE aci2.GaswellID=2 AND aci2.CompanyID=1
)
AND aci.CompanyID=1
) AND aa.CompanyID=1 AND aa.EndAccountFlag=1
)
ORDER BY aa.CreateDate DESC
)
aa ON
(
rmgd.ReadDate>=aa.CreateDate AND
rmgd.ReadDate<(CASE aa.EndAccountFlag WHEN 1 THEN aa.EndDate ELSE GETDATE() end)
)
WHERE rmgd.GaswellID=2 AND dal.startorstop=0 AND ag.CompanyID=1
) tb_Total WHERE tb_Total.CreateDate>='2012-01-01 00:00:00.000' AND tb_Total.CreateDate<='2012-12-12 23:59:59.999'
) tb_Total1 GROUP BY tb_Total1.AccountID
运行结果如下图所示：