日期:2014-05-16  浏览次数:20456 次

这种连接出笛卡尔积的表,如何设计更好?
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)
    {
&