超难问题！！！大力，蚂蚁，登山者们来看看

建议你最好的处理方法是建一个
套装代码表：
代码   名称
0001   aaaa
0002   bbbb再建销售表代码数量
0001 1
0002 2
0001 3
0002 2再关联查询，方便实用安全

以下过程是我的（处理一笔交易）一种思路。
declare @sales table(商品,数量)
/* 在此请自行导入销售商品明细，
  例如：
    insert @sales(商品,数量) select 商品编码,sum(数量) from 交易明细
      group by 商品编码 where 发票号='AP1234567'
*/
declare @group table(row int identity,套装,商品,数量,primary key(套装,商品))
/* 在此请自行(重新)导入套装数据，
  例如：
    insert @group(套装,商品,数量) select 套装代码,商品,sum(数量)
      from 表A group by 套装代码,商品
*/
declare @result table(套装)
declare @p int
set @p=1
while @p<=(select max(row) from @group)
begin
  if not exists(select * from @sales as s full join @group as g
    on a.商品=g.商品 where g.row=@p and (s.商品 isnull or s.数量<g.数量))
  begin
    insert @result(套装) select 套装 from @gropup where row=@p
    update s set 数量=数量-g.数量
      from @sales as s inner join @group as g on g.row=@p and s.商品=g.商品
  end
  else set @p=@p+1
end
--下面输出结果：
select 套装,count(*) as 套数 from @result group by 套装说明：full join那句，使用not exists的目的是剔除以下不符合“成套”的两种情况：
1. group中有的商品品种但是在sales中没有。
2. group中和sales种商品对应，但是sales中的商品数量不够组合进一套套装。一旦找到一套套装，则从sales中减去此一套套装的数量，然后继续看看此的套装是否还可以再匹配一套；否则，看看下一个套装是否成立。

上面 “declare ... table ”中均没有写数据类型，请自行填入。

不是要这个吧？
Select 套装代码 where 商品 = 'aaa' and 数量 = 1

create table A(
套装代码  char(4),
商品      varchar(4),
数量      int)insert A select '0001','aaaa',1
insert A select '0001','aaaa',1
insert A select '0001','bbbb',1
insert A select '0002','aaaa',1
insert A select '0002','cccc',1
insert A select '0002','dddd',1
insert A select '0002','eeee',2create table #t(
商品      varchar(4),
数量      int)insert #t select 'aaaa',1
insert #t select 'aaaa',1
insert #t select 'bbbb',1select distinct 套装代码 from A where 套装代码 not in
(select 套装代码 from A as B where not exists
(select * from #t where 商品 = B.商品 and 数量 = B.数量))

to:登山者
你的方法有问题,你试试这个：
insert #t select 'aaaa',1
insert #t select 'cccc',1
insert #t select 'bbbb',1
它也能把0001检索出来，这是不对的to:eminena
也就是多个商品组成一个套装商品
感谢大家的参与，另商品的顺序不确定

create table A(
套装代码  char(4),
商品      varchar(4),
数量      int)insert A select '0001','aaaa',1
insert A select '0001','aaaa',1
insert A select '0001','bbbb',1
insert A select '0002','aaaa',1
insert A select '0002','cccc',1
insert A select '0002','dddd',1
insert A select '0002','eeee',2create table #t(
商品      varchar(4),
数量      int)insert #t select 'aaaa',1
insert #t select 'aaaa',1
insert #t select 'bbbb',1
select * from A
where
(select count(*) from A aa where aa.套装代码=a.套装代码)=(select count(*) from #t)
and
(select count(*) from (select distinct 套装代码,商品,数量 from A) aa where aa.套装代码=a.套装代码)=(select count(*) from (select 商品,数量 from A aa where aa.套装代码=a.套装代码 union select 商品,数量 from #t) tem)

0001        aaaa      1
0001        aaaa      1
0001        bbbb      1

为什么代码 0001 里有两个一样的品名和数量商品: aaaa  ?

第二版：
select distinct 套装代码 from A as D where 套装代码 not in
(select 套装代码 from A as B where not exists
(select * from #t where 商品 = B.商品 and 数量 = B.数量))
and not exists
(select * from #t as C where not exists
(select * from A where 套装代码 = D.套装代码 and 商品 = C.商品 and 数量 = C.数量))

第三版：
select distinct 套装代码 from A as D where 套装代码 not in
(select 套装代码 from A as B where not exists
(select * from #t where 商品 = B.商品 and 数量 = B.数量) or
(select count(*) from A where 套装代码 = B.套装代码 and 商品 = B.商品 and 数量 = B.数量) <>
(select count(*) from #t where 商品 = B.商品 and 数量 = B.数量))
and not exists
(select * from #t as C where not exists
(select * from A where 套装代码 = D.套装代码 and 商品 = C.商品 and 数量 = C.数量))

登山者，怎么理解这句呀
not exists
(select * from #t as C where not exists
(select * from A where 套装代码 = D.套装代码 and 商品 = C.商品 and 数量 = C.数量))

测试，是证明算法有错误，而不是证明算法没有错误！测试一下这个情况：如果销售商品中可以有A套装1套、B套装2套，或者是A套装2套、B套装1套；但是不可能同时满足A套装2套、B套装2套。此时，算法是否找出的套装“太多了”。如果那样，商店可亏大了！
to: JGTM2000(铁马小子)
如果读起教科书来头头是道，解决起基本问题来连个“毛”都没有，错误百出，算是“守规矩、学得好”的话，那么也太“成事不足”了。我小时候听了一个笑话：有个小孩，他妈妈出门前给他烙了一张大饼，把饼套在他脖子上叫他饿了就吃。结果，三天后，他妈妈回来一看，他已经饿死了！原来他竟然傻到“只知道啃面前嘴巴够得着的一小块饼”!! 我想这个论坛上的人确实没几个想在天上开飞机的，但是想学以致用的不在少数。

关注思路解答好长的sql 看得头晕

我也希望有“一句SQL能够解决问题”的解答。因为那才是4GL语言的价值。上面的3GL程序只是思路。

问题分析：楼主所提的问题我想大概可以简单的映射到现实生活中的这种情形：销售公司（如麦当劳）制定了若干产品组合（即套装、套餐）并提供更优惠的价格以促进业务，某客户（如大食客）来到销售终端并随机点取若干产品（顺序、数目均不定且可能重复），现需要一解决方案，使得能够自动识别该用户定购的产品中可能存在的所有产品组合，并以最可能优惠的价格生成交货单转给后续的配送流程。解决思路：首先把问题分为两个层次：首先能够在随机产品序列中识别出产品组合；进而可以选出价格最低（或利润最高、或数量最多）的最优组合。鉴于楼主仅提到了第一个层次的需求，且为了表述简单、易于理解，本贴仅关注第一个层次的解决办法，有时间且大家还感兴趣可以继续探讨后续的问题。既然楼主希望直接利用数据库来解决这个问题，我们还是从数据库的设计原理同实现机制结合起来寻找解决问题的最佳思路。为了表述简单，我们不妨先假设四个表：产品表P、套装定义表S、定单清单表O和发货单表I。产品表中定义业务系统中的每个单位产品（及其价格，对于仅捆绑销售或搭赠的产品也可以没有价格，并入套装中体现）；套装定义表定义套装与产品之间的关系（及其总价，从某种意义上看，一个套装其实是一个组合产品，它也有价格）；定单清单表定义了每份定单中选购的产品的序列；发货单是经过逻辑处理的最终销售方案。现在问题就变成了：利用P与S将O处理生成符合条件的I。为简单起见，我们不妨假设如下套装方案间不存在嵌套关系（即不存在一种套装方案其本身仍可认为包含另一套装，如S1=(A2+B1+C1), S2=(A1+B1+C2), S3=(A3+B2+C1)，S1与S2不存在嵌套关系，而S3则可以认为嵌套了S1，因为S3=(S1)+A1+B1）。如果可以存在嵌套关系，则问题仍可以类似的解决，只是多出一些额外的循环和判断，留待大家以后讨论吧。为了解决问题，我们实际上可以将某一份给定的定单清单与所有套装中的每个产品组合一一对照，若发生订单中的产品不存在于某一组合中，则可以肯定该定单不可能包含该组合；或者若定单中的某一产品的总订数小于某一组合中该产品的组合数目，则同样可以排除该组合在订单中的可能；当且仅当某一套装中的所有产品所需数量均存在于订单中（换言之，所有产品均存在且所有产品均达到组合要求的数目且总数量差之和大于等于0），则可以认为该套装存在于该定单中（在前面的假设下，这种组合将最多仅有一种）。实现方法：现在以使用SQL Server 2000为例给出一种参考实现，请大家测试并提出改进意见。1、总量视图考虑到本方案中需要逐产品做总量比对，为套装表及订单清单表建立产品总量视图可以简化后续操作。具体讲：-- 套装视图
create view SuiteView
as
select
  SuiteID, ProductID, sum(Quantity) as Quantity
from
  Suite
group by SuiteID, ProductID-- 订单视图
create view OrdersView
as
select
  OrderID, ProductID, sum(Quantity) as Quantity
from
  Orders
group by OrderID, ProductID2、评价函数为表述清晰，我们建立利用SQL2K的UDF功能建立一个评价函数，用来对某一种产品的组合数量和订单数量作出判断。create function dbo.evalDiff
  (@orderQuantity int, @suiteQuantity int)
returns int as
begin
  if @orderQuantity >= @suiteQuantity
    return @orderQuantity - @suiteQuantity  return -1000 --使用一个足够小的数量即可
end3、联结比对首先将Suite套装表与Orders订单表中的给定订单（@orderID）联结，观察其中各产品与所有套装组合的明细：select
  @orderID OID, s.SuiteID, o.Quantity, s.Quantity,
  (dbo.evalDiff(o.quantity, s.quantity)) Diff
from
  OrdersView o, SuiteView s
where
  o.Orderid = @orderID and o.ProductID =* s.ProductID
order by
  SuiteID注意“=*”的非正规偷懒用法（等同于FULL JOIN），它用来选出那些S中存在但O中不存在的行（既然不存在，O.*都将为NULL）。我们可以发现，如果以OrderID和SuiteID分组汇总Diff的话，就能够知道整个套装是否匹配该定单明细，因此将上面的语句演变为：select
  @orderID OID, s.SuiteID, o.Quantity, s.Quantity,
  sum((dbo.evalDiff(o.quantity, s.quantity))) TotalDiff
from
  OrdersView o, SuiteView s
where
  o.OrderID = @orderID and o.ProductID =* s.ProductID
group by
  s.SuiteID观察结果看看TotalDiff的值是否吻合预定的逻辑（存在套装则TotalDiff>=0），没有问题的话，进一步通过having子句即可达到目标，最终的语句为：select
  @orderID OID, s.SuiteID, o.Quantity, s.Quantity,
  sum((dbo.evalDiff(o.quantity, s.quantity))) TotalDiff
from
  OrdersView o, SuiteView s
where
  o.OrderID = @orderID and o.ProductID =* s.ProductID
group by
  s.SuiteID
having
  sum(dbo.evalDiff(o.quantity, s.quantity)) >= 0其中s.SuiteID的值就是@orderID对应订单中包含的套装的ID。附相关数据表与运行结果：Suite表：SuiteID    ProductID  Quantity
---------- ---------- -----------
0001       aaaa       1
0001       aaaa       1
0001       bbbb       1
0002       aaaa       1
0002       cccc       1
0002       dddd       1
0002       eeee       2Orders表（你可以自己添加新的order来测试）：OrderID    ProductID  Quantity
---------- ---------- -----------
t0001      aaaa       1
t0002      aaaa       1
t0002      aaaa       2
t0002      bbbb       1
t0003      aaaa       1
t0003      cccc       1
t0003      dddd       1
t0003      eeee       1
t0003      eeee       1
t0004      aaaa       1
t0004      bbbb       1
t0004      cccc       1
t0004      dddd       1
t0004      eeee       1
t0004      aaaa       1
t0004      eeee       1
t0005      aaaa       1
t0005      bbbb       1
t0005      aaaa       1测试：declare @orderID char(10)
set @orderID = 't0005'（这里执行刚才的代码）结果：orderID    SuiteID    TotalDiff
---------- ---------- -----------
t0005      0001       0说明t0005订单中含有0001套装。后话：前面大概分析了一下解题的思路并给出了一个参考的实现方法，接下来你可以在此基础上实现更复杂的逻辑（如加入order by套装价格、成本、利润等等），将一份订单明细通过循环识别组合（记得包含在一个事务中）来生成最终的发货方案（例如：对一份订单，每次选出一个价格最高的套装组合，将其加入发货表，并从订单明细中减去，重复直到没有组合，将剩余的独立产品加入发货表）。值得注意的是，这里我们实现的仅是数据逻辑（且充分利用3GL SQL语言的处理特性），在数据库中最好不要实现业务逻辑，毕竟你还有客户端或中间层可以写4GL的高级代码（当然还有更多的原因）。尤其在多层开发中，一定要遵循一个基本设计与实现原则，即在最适合的地方做最合适的事。这种经验是在设计并开发无数个产品以后才能够体会到的，总靠一些技巧和机灵把汽车开上天是永远也不可能成为一名能够驾驭兰天的机师的。况且无数的失败或教训告诉我们，往往“败事有余”的根源恰恰在于人们对最基本的原则和规律的漠视，而这些人同样也骄傲到以为能够技巧高超的把汽车开上天就可以对飞行员嗤之以鼻了。呵呵————告诉你飞行员开车也不一定比你差噢，况且你顶多也就是开开车：）以上一些不成熟的思路仅供大家参考，我相信会有更快更好更别致的办法来完成这个“超难问题”。

to w_rose:我不知道你给我讲“小孩子吃饼饿死了”是什么意思？是感觉自己的知识贫乏到快要饿死了吗？那我想可以结合自己的经验给你提几个建议：多研究几门技术（比如说如果你喜欢数据库，不妨顺便看看数据仓库、数据分析之类的，也同时要去看看不是很顺便的其他技术，比如三层分布式结构开发之类的），知识结构丰富了就会知道很多时候是不能总啃嘴边上这点儿饼的，要转换一下角度，看看还有没有更好的方向解决同样的问题，这样就不太容易被饿死了。的确如你所说：不用不知道知识的浅薄、不用不知道理论的苍白。然而如果不学，你都不知自己的知识是否浅薄的可以、自己的理论是否苍白的要命啊……

select distinct 套装代码 from 表A where
套装代码 in (select 套装代码 from 表A A1 where A1.商品= and A1.数量=)
套装代码 in (select 套装代码 from 表A A1 where A1.商品= and A1.数量=)
套装代码 in (select 套装代码 from 表A A1 where A1.商品= and A1.数量=)

select distinct 套装代码 from 表A where
套装代码 in (select 套装代码 from 表A A1 where A1.商品= and A1.数量=)
套装代码 in (select 套装代码 from 表A A2 where A2.商品= and A2.数量=)
套装代码 in (select 套装代码 from 表A A3 where A3.商品= and A3.数量=)

我来解释什么叫做4GL。4GL就是“第四代语言”，这是上个世纪八十年代末产生的，主要指各种数据库管理系统逐渐采用的“面向数据集合”的操作语言和开发工具。比如，有较长开发数据库程序的程序员可能有此经历，foxpro最初只能针对单条数据记录编程序，只有在七、八年前才允许使用SQL语言只用一条语句就写出原来需要一大堆繁琐的代码才能写出的查询程序。4GL并不单单指SQL标准，但是推行SQL标准确实是4GL的核心。SQL，可以使程序员首先考虑查询的结果，只要说明结果，不需要过多考虑过程，从而可以说明问题的要领。拿“查询最短路程”或者PERT计算之类的问题说吧，可以用“一条SQL”语句就找出问题的答案，而不需要像一般数据结构或者系统工程教科书上所说的要一个比较冗长的程序。

如果说SQL Server或者Oracle，甚至Foxpro，都“不应该”使用SQL语言，我看最好还是先去说动开发这些数据库系统的公司去吧。我向他们不会因为“傻”才辛辛苦苦地让自己的系统支持SQL吧！！

to w_rose:谢谢你给大家解释这些教科书上都有的概念，我大概是看到你写的3GL才被误导了。不过既然你概念这么清楚，为什么还要用4GL的SQL语言来写3GL的过程化逻辑呢？据我所知，4GL的关键特性就是面向问题的非过程化解决方法，然而不可否认的是仍有很多问题是非过程化逻辑所无法解决的呀（包括典型的4GL语言SQL同样提供了过程化的语句让你能处理比较复杂的逻辑——在万不得已的时候）。诚然，针对楼主的问题，其核心环节的数据逻辑是完全可以充分利用非过程化（即面向集合的）的SQL来描述的，但是继而引发的关于业务逻辑的处理显然不是非过程化能描述的了，这就是为什么现在分布式多层应用程序开发中引入业务逻辑层的原因之一（该层中业务逻辑的实现大多采用3GL/5GL高级语言）。楼上很多弟兄不管结果如何，至少思路上都是利用4GL非过程化的特性构造解决方案，而你总是固执的非要用试图用4GL去解决他根本解决不了（或至少是不擅长）的问题，难道这就是你对4GL语言的深刻领悟吗？难道这就是你所说的供大家学习4GL语言的经典吗？本来这里是讨论技术问题的场所，我觉得没有必要针对非技术问题争论不休，不过如果一些非技术问题对大家讨论技术问题起到了不好的作用，我觉得还是有必要澄清以便是非的。包括你说的什么SQL/Oracle应不应该使用SQL语言的问题，简直就是不合逻辑，况且我也从来没说过不可以使用SQL，我在第一帖中的意思是要分清楚一个特定问题中什么是数据逻辑，什么又是业务逻辑，这是最基本的系统分析啊。数据逻辑当然是应该充分利用数据库管理系统结合4GL语言的优势解决的，然而复杂多变的业务逻辑则应该在更高的层次上使用高级语言来解决，这有什么错吗？你自己不也觉得在4GL应用环境中写出过程化的3GL逻辑是不恰当的吗？或许有些东西只有当你站的更高、看得更远的时候才能意识到，人的知识、见识、解决问题的能力也因此得以提升。正如你自己所说：只知道啃自己眼前的一角饼那迟早会饿死的啊！为了不再耽误大家讨论本问题，如果你有更多的见解，请结合问题本身再发表出来，或者给我的解决方法提些意见和建议也好啊，集思广益大家一起进步嘛！：）

非常感谢w_rose(w_rose) 、JGTM2000(铁马小子) 精彩的讨论，
让我受益非浅。
此帖到晚上8点结帖，如果还有朋友要关注的话，请继续。。

to eastpond (东塘) :感谢你提出这么一个比较有意思也有深度的题目，不过我想知道我的那种解法是否适用于您的情况？是否还有考虑不周全的地方？因为我没有真实数据，想听听您的反馈，也能间接的长些实践经验吧！谢谢：）

to:JGTM2000(铁马小子)
其实在我的系统中，我只需要输入套餐代码，然后取得套餐对应各明细商品就可以了，这是很简单的。
然后在开发的过程中，想到能不能根据输入的商品来匹配套餐(虽然客户没有提出这方面的需求，本人觉得还是有必要研究一下)。
我看了你的思路，觉得写得挺好的,基本上已经给出了整个的解决方案,应该说完全能够满足我的需求了，只是由于时间关系，所以没有去完全测试一下。
再次感谢你！！

调试易

超难问题！！！大力，蚂蚁，登山者们来看看

解决方案 »