Question

我有一个如下的问题:

我有两个表1表格供顾客购买物品,另一个表格供雇员销售:

Table 1 (Emp)
EmpId 
ProductId
dateSold
Price
Qty

Table 2 (Cust)
CustomerId
ProductId
dateSold
Price
Qty

此时此操作是查询 IM tring :

SELECT  SUM(EmployeeSales.productPrice * EmployeeSales.Qty + 
            userBoughtItems.ProductPrice * userBoughtItems.qty) AS Total, 
        EmployeeSales.dateSold, 
        userBoughtItems.dateSold AS Expr1
FROM    EmployeeSales 
INNER JOIN userBoughtItems ON EmployeeSales.dateSold = userBoughtItems.dateSold
GROUP BY EmployeeSales.dateSold, userBoughtItems.dateSold

我想在同一天得到两张桌子的总数...

Answer 1

join 重复左边表格中每个匹配行的右表。在您的示例 I 中, I 期望这会导致很多重复行。考虑使用 < code> union 来代替 < code > union :

select  cast(dt as date)
,       sum(sales)
from    (
        select  dateSold as dt
        ,       productPrice * Qty as sales
        from    EmployeeSales
        union all
        select  dateSold
        ,       ProductPrice * qty
        from    userBoughtItems
        ) as SubQueryAlias
group by
        cast(dt as date)

子查询包含一份所有销售清单,外部查询每天加起来。

如果您重新使用 SQL Sever 2005 或更高级的 SQL Sever 2005, 将 castard(dt as date) 替换为 dateadadadd(ddd, 0, dt) 。

Answer 2

不按条款分组, n t 就可以工作,它会扔出以下错误。

Msg 8120, Level 16, State 1, Line 4 Column employeesales.dateSold is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

我稍稍变了一下

使用此

select dt , sum(sales) from ( select dateSold as dt , sum(Price * Qty) as sales from employeesales group by dateSold union all select dateSold , sum(Price * qty) from customerboughtitems group by dateSold ) as SubQueryAlias group by dt

友情链接