我正在尝试创建一份关于每天花费的总金额的报告。在数据库中有这两个表。它们使用创建时制作的“UID”进行匹配。
我创建了这个查询，但它导致重复的日期。
Select LEFT(f.timestamp, 10) timestamp, sum(s.Total) Total
FROM dbo.purchasing AS f
Join (SELECT uid,SUM(CONVERT(DECIMAL(18,2), (CONVERT(DECIMAL(18,4), qty) * price))) Total
FROM dbo.purchasingitems
GROUP BY uid)
AS s ON f.uid = s.uid
GROUP BY TIMESTAMP
购买：
+--+---------+------------+--------+---+
|ID| UID | timestamp | contact|...|
+--+---------+------------+--------+---+
| 1|abr92nas9| 01/01/2018 | ROB |...|
| 2|nsa93m187| 02/02/2018 | ROB |...|
+--+---------+------------+--------+---+
采购项目：
+--+---------+-----+--------+---+
|ID| UID | QTY | Price |...|
+--+---------+-----+--------+---+
| 1|abr92nas9| 20 | 0.2435 |...|
| 2|abr92nas9| 5 | 0.5 |...|
| 3|nsa93m187| 1 | 100 |...|
| 4|nsa93m187| 4 | 15.5 |...|
+--+---------+-----+--------+---+
您需要按表达式分组：
SELECT LEFT(f.timestamp, 10) as timestamp, sum(s.Total) as Total
FROM dbo.purchasing f JOIN
(SELECT uid, SUM(CONVERT(DECIMAL(18,2), (CONVERT(DECIMAL(18,4), qty) * price))) as Total
FROM dbo.purchasingitems
GROUP BY uid
) s
ON f.uid = s.uid
GROUP BY LEFT(f.timestamp, 10);
笔记：
timestamp是日期，则应使用cast(timestamp as date).timestamp是SQL Server 中的关键字（虽然不是保留的），因此它不是列名的好选择。GROUP BY timestamp是指在表达SELECT。SQL Server 不支持列别名，因此它只能引用该名称的列。order by确保结果集的顺序合理。本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句