这种连接出笛卡尔积的表，如何设计更好？

Question

设计

Answer 1

或者你用union all来实现？

Answer 2

with Orders as (
select 1 OrderID union all
select 2 OrderID union all
select 3 OrderID
)
select * into Orders from Orders
;
with 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
select 4 TransactionID,2 OrderID,300.0 Amount,2 TransactionType union all
select 5 TransactionID,3 OrderID,50000.0 Amount,1 TransactionType 
)
select * into Transactions from Transactions
;
with OrderDetail as (
select 1 DetailID,1 OrderID,1 ProductID,3 Num,10.0 Amount union all
select 2 DetailID,1 OrderID,2 ProductID,2 Num,50.0 Amount union all
select 3 DetailID,1 OrderID,3 ProductID,5 Num,40.0 Amount union all
select 4 DetailID,2 OrderID,2 ProductID,4 Num,50.0 Amount union all
select 5 DetailID,2 OrderID,3 ProductID,8 Num,40.0 Amount union all
select 6 DetailID,3 OrderID,4 ProductID,55 Num,1000.0 Amount 
)
select * into OrderDetail from OrderDetail这个作为测试数据
1个Order可以对应2条订单明细，也可以对应3条支付明细。这是合理的场景。

Answer 3

这样的数据，直接连接Sum(Amount) ,结果比实际多了。

Answer 4

你的测试数据有点问题，第一个SUM中的字段不知道是哪个表的，我改了一下，是不是你要的？
select o.OrderID,
SUM(Num*od.Amount)-SUM(t.Amount)
from Orders o join OrderDetail od on od.OrderID=o.OrderID
join Transactions t on t.OrderID=o.OrderID
group by o.OrderID/*
OrderID     
----------- ---------------------------------------
1           -240.0
2           240.0
3           5000.0
*/

Answer 5

嗯，字段名错了。上面的OrderDetail表的Amount字段改名为UnitPrice。我的期望：用这种直接连接查询，直接用SUM()计算结果select o.OrderID,SUM(Num*UnitPrice)-SUM(t.Amount)
from Orders o 
join OrderDetail od on od.OrderID=o.OrderID
join Transaction t on t.OrderID=o.OrderID
group by o.OrderID但这种写法是错的，必须用第二种写法。所以，我想问问设计，或者别的我不知道的关键字（如 SumDistinct，呵呵，希望ms，推出这样的函数或关键字） 

Answer 6

像你说的，你的实际需求，要比你上面描述的还要复杂，这种情况下，确实会增加代码量，我在原来公司写了很多报表，写到后面，很多都是由于按照你的第一种写法，而导致错误的，就是因为不是一对一的对应关系，后来都改成你的第二种写法了。当然，原来公司的报表都是非常复杂的，中等的报表都有1000行的sql实现，通过采用第二种写法，性能更好，结合临时表，把group by后比较小的结果集，存到临时表，然后再关联，性能更好

Answer 7

你可以加个计算列来存Num*UnitPrice你也可以join Transaction t on t.OrderID=o.OrderID
改为：
(select orderid,sum(amount)amount from Transaction group by orderid) t on t.OrderID=o.OrderID然后上面的select中最后那个sum就直接用amount

Answer 8

基本不抱太大期望了，但是有没有稍微有帮助的设计么？你说的这个是什么意思？
ORDERS   ID
OrderDetail  DetailID
关联表   ORDERSID DetailID

Answer 9

嗯就是一个意思，只不过放到with里而已。

Answer 10

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

Answer 11

这是我自己写的 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
select 4 TransactionID,2 OrderID,300.0 Amount,2 TransactionType union all
select 5 TransactionID,3 OrderID,50000.0 Amount,1 TransactionType 
)
select * into test_Transactions from test_Transactions
;
with test_OrderDetail as (
select 1 DetailID,1 OrderID,1 ProductID,3 Num,10.0 UnitPrice union all
select 2 DetailID,1 OrderID,2 ProductID,2 Num,50.0 UnitPrice union all
select 3 DetailID,1 OrderID,3 ProductID,5 Num,40.0 UnitPrice union all
select 4 DetailID,2 OrderID,2 ProductID,4 Num,50.0 UnitPrice union all
select 5 DetailID,2 OrderID,3 ProductID,8 Num,40.0 UnitPrice union all
select 6 DetailID,3 OrderID,4 ProductID,55 Num,1000.0 UnitPrice 
)
select * into test_OrderDetail from test_OrderDetail;
--目前正确写法
with t1 as (
select 
o.orderid,SUM(d.Num*d.UnitPrice) Amount
from 
test_Orders o 
join test_OrderDetail d on d.OrderID=o.OrderID
group by o.orderid
)
,t2 as (
select 
o.orderid,SUM(amount) Amount
from 
test_Orders o 
join test_Transactions t on t.OrderID=o.OrderID
group by o.orderid
)
select 
t1.orderid,t1.Amount OrderAmount,t2.Amount TransactionAmount,t1.Amount-t2.Amount DiscountAmount
from t1 join t2 on t1.orderid=t2.orderid;
/*
orderid     OrderAmount                             TransactionAmount                       DiscountAmount
----------- --------------------------------------- --------------------------------------- ---------------------------------------
1           330.0                                   300.0                                   30.0
2           520.0                                   400.0                                   120.0
3           55000.0                                 50000.0                                 5000.0
*/--用自建的CLR聚合函数，直联到底的简单写法。
--但性能上不行。可能自己建的CLR在性能上本身就有欠缺啊？
select 
o.OrderID
,dbo.SumDistinctString(distinct d.Num*d.UnitPrice,d.DetailID)OrderAmount
,dbo.SumDistinctString(distinct t.Amount,t.TransactionID)TransactionAmount--distinct 两个字段，就保证了在同一个OrderID下同一个TransactionID只被计算一次Amount
,dbo.SumDistinctString(distinct d.Num*d.UnitPrice,d.DetailID)-dbo.SumDistinctString(distinct t.Amount,t.TransactionID)DiscountAmount
from test_Orders o 
join test_OrderDetail d on d.OrderID=o.OrderID
join test_Transactions t on t.OrderID=o.OrderID
group by o.OrderID
;
/*
OrderID     OrderAmount                             TransactionAmount                       DiscountAmount
----------- --------------------------------------- --------------------------------------- ---------------------------------------
1           330.0000000                             300.0000000                             30.0000000
2           520.0000000                             400.0000000                             120.0000000
3           55000.0000000                           50000.0000000                           5000.0000000
*/

Answer 12

想法挺好的，不过想到之前看过一本 叫，人月神话的书，上面说没有银弹，就是很多时候，我们总想着有没有什么神话，多快好省，这样多好啊，不仅代码写的简短，性能还好，还能按时完成项目进度，还能具有很强的扩展性。这个其实是不可能的，要是代码这么容易写，我估计写sql的以后肯定会失业，写c#的夜会失业

Answer 13

单纯从这个题目来说，没必要那么复杂的功能，CLR由于是外部应用，sqlserver本身很难管控这部分的问题，包括性能

Answer 14

除了上述3个表外，应该还须建立一个折扣表了，通过存储过程来计算各订单的折扣并存放在该表里
而不是每次查询订单的折扣情况就通过sql语句算一次了
楼主的那3个表设计没有问题