这种连接出笛卡尔积的表，怎么设计更好_Sql Server

这种连接出笛卡尔积的表，如何设计更好？
1、Orders订单表。字段：OrderID，其他省略。
2、OrderDetail订单明细表，Orders表跟此表是1对多。字段：DetailID,OrderID（关联键）,ProductID,Num,UnitPrice。其他省略
3、Transaction交易明细表，Orders表跟此表是1对多。字段：TransactionID,OrderID（关联键）,Amount, TransactionType。其他省略

现在我想出这样的结果：OrderID（订单号），DiscountAmount（折扣金额）
直观想到查询的语句是这样的：


select o.OrderID,SUM(Num*UnitPrice)-SUM(t.Amount)--(如果这样写了，因为会关联出笛卡尔积，所以不能直接用SUM....)
from Orders o 
join OrderDetail od on od.OrderID=o.OrderID
join Transaction t on t.OrderID=o.OrderID
group by o.OrderID

首先我知道怎么正确写法：


with t1 as (
select o.OrderID,SUM(Num*UnitPrice) Amount 
from Orders o 
join OrderDetail od on od.OrderID=o.OrderID
group by o.OrderID
),t2 as (
select o.OrderID,SUM(t.Amount) Amount
from Orders o 
join Transaction t on t.OrderID=o.OrderID
group by o.OrderID
)
select t1.OrderID,t1.Amount-t2.Amount  from t1 join t2 on t1.OrderID=t2.OrderID

但是，我想用第一种，所有表这么直接得连接下来，再Group by，这样开发起来简单。（因为这需求是我简化的，真正需求很复杂）
所以，我想是不是自己设计表上有问题？大家遇到类似的表结构都是怎么设计的？能规避笛卡尔积的设计？或者有类似SUM distinct 的语法？（SUM (distinct ...)这样肯定不行，金额完全相同的也不能不算啊)
------解决方案--------------------

引用:

Quote: 引用:

基本都这样了无非就是加个关联表来冗余数据而不把一些冗余数据放这些表上面。

基本不抱太大期望了，但是有没有稍微有帮助的设计么？你说的这个是什么意思？

ORDERS   ID
OrderDetail  DetailID
关联表   ORDERSID DetailID
------解决方案--------------------
是否有性能问题，要在具体环境下对比执行计划
------解决方案--------------------
这个没办法的，你通过一个非唯一的对应关系，怎么可能不出现笛卡尔积呢！唯一的解决办法是在交易明细表里面记录订单明细Id，这样由于交易明细数据和订单明细数据有直接的对应关系，就不需要通过订单表去关联了。自然也就不可能出现笛卡尔积问题了。
------解决方案--------------------
路过

------解决方案--------------------
这类需求越来越多的话可以考虑引进BI系统。写SQL的话编写代码的效率是比不上BI的。
------解决方案--------------------

引用:

这是我自己写的 CLR开发的一个聚合函数。（配合 Distinct关键字用的求和函数）
用法：第一个参数，求和字段；第二个参数，按哪个字段去重复。不好理解可以看看最后的用法。
虽然写法上简单了。但性能上很差。希望MSSQL在下个版本，提供类似的内置聚合函数吧。


using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;
using System.Collections;

[Serializable]
[SqlUserDefinedAggregate(
    Format.UserDefined,
    IsInvariantToNulls = true,
    IsInvariantToDuplicates = false,
    IsInvariantToOrder = false,
    MaxByteSize = 8000)
]
public struct SumDistinctString : IBinarySerialize
{
    private decimal sum;
    public void Init()
    {
        sum = 0m;
    }

    public void Accumulate(SqlDecimal Value, SqlString DistinctField)
    {
        //什么都不操作，直接累加，DistinctField字段也不做判断条件。
        //因为这个函数里面我要放 Distinct关键字的。靠Distinct关键字，在SQL端就按两列去重复了。
        sum += Value.Value;
    }

    public void Merge(SumDistinctString Group)
    {
        sum += Group.sum;
    }

    public SqlDecimal Terminate()
    {
        return new SqlDecimal(sum);
    }

    #region IBinarySerialize Members
    public void Read(System.IO.BinaryReader r)
    {
        sum = r.ReadDecimal();
    }

    public void Write(System.IO.BinaryWriter w)
    {
        w.Write(sum);
    }
    #endregion
}

--CLR怎么部署，这里不说了，有用过的可以试试。

--SQL数据测试


use Test ;
with test_Orders as (
select 1 OrderID union all
select 2 OrderID union all
select 3 OrderID
)
select * into test_Orders from test_Orders
;
with test_Transactions as (
select 1 TransactionID,1 OrderID,100.0 Amount,1 TransactionType union all
select 2 TransactionID,1 OrderID,200.0 Amount,2 TransactionType union all
select 3 TransactionID,2 OrderID,100.0 Amount,1 TransactionType union all