用户表查询数据

查询用户取水记录

select
u.UserName,
u.OpenId,
c.CompanyName,
c.CompanyCode,
tr.Id,
tr.RealVolume,
tr.StationId,
tr.CreateTime,
tr.ReceiptTime
from 数据库.dbo.[用户表] u
inner join 数据库.dbo.[公司表] c on u.CompanyID=c.id 
left join 数据库.dbo.[取水记录表] tr on u.OpenId=tr.OpenId and u.CompanyID=tr.CompanyID
 -- where CreateTime <'2025-04-01 0:00' 
order by CreateTime asc

查询所有用户用水总量

  SELECT 
    C.[CompanyName],
    SUM(T.[RealVolume]) AS TotalVolume
FROM 
    [WaterIntake].[dbo].[用户表] U
    -- 关联公司表 (假设User和Company通过CompanyID关联)
    INNER JOIN [WaterIntake].[dbo].[公司表] C 
        ON U.[CompanyID] = C.[ID]
    -- 关联交易记录表 (假设通过OpenId关联用户与交易记录)
    LEFT JOIN [数据库].[dbo].[用水记录表] T 
        ON U.[OpenId] = T.[OpenId] 
        AND U.[CompanyID] = T.[CompanyID] -- 双重关联条件
WHERE 
    c.CompanyName IN (
       公司名称
    )
GROUP BY 
    C.[CompanyName]
ORDER BY 
    C.[CompanyName];