百钱买鸡兔_SQL版

百钱买鸡兔：
老母鸡3块1只，
小母鸡4块5只，
大白兔2块1只，
小白兔3块4只，

要求买回来的动物总共100只，
并且脚不少于240条不多于320条。
花100块钱来买这些动物，

要求每种动物都至少要购买一只且钱正好花完，
输出所有的可能情况，
并依次按老母鸡、小母鸡、大白兔、小白兔的数量顺序排序。

WITH M1 AS (
            SELECT 'LMJ'NM, 1 Z,3 K,2 J FROM DUAL UNION ALL
            SELECT 'XMJ', 5,4,2 FROM DUAL UNION ALL
            SELECT 'DBT', 1,2,4 FROM DUAL UNION ALL
            SELECT 'XBT', 4,3,4 FROM DUAL
           )
SELECT  T1.CNT LMJ
       ,T2.CNT  XMJ
       ,T3.CNT  DBT
       ,T4.CNT  XBT
FROM (
      SELECT M1.NM
             ,T2.CNT
             ,T2.CNT * M1.K/M1.Z K
             ,T2.CNT * M1.J AS J
      FROM M1
           ,(SELECT LEVEL CNT FROM DUAL CONNECT BY LEVEL <= 33) T2
      WHERE M1.NM = 'LMJ'
     ) T1
     ,(
      SELECT M1.NM
             ,T2.CNT
             ,T2.CNT * M1.K/M1.Z K
             ,T2.CNT * M1.J AS J
      FROM M1
           ,(SELECT LEVEL*5 CNT FROM DUAL CONNECT BY LEVEL <= 100/5) T2
      WHERE M1.NM = 'XMJ'
    ) T2
    ,(
      SELECT M1.NM
             ,T2.CNT
             ,T2.CNT * M1.K/M1.Z K
             ,T2.CNT * M1.J AS J
      FROM M1
           ,(SELECT LEVEL CNT FROM DUAL CONNECT BY LEVEL <= 50) T2
      WHERE M1.NM = 'DBT'
    ) T3
    ,(
      SELECT M1.NM
             ,T2.CNT
             ,T2.CNT * M1.K/M1.Z K
             ,T2.CNT * M1.J AS J
      FROM M1
           ,(SELECT LEVEL*4 CNT FROM DUAL CONNECT BY LEVEL <= 100/4) T2
      WHERE M1.NM = 'XBT'
      AND T2.CNT * M1.J <= 320
    ) T4
WHERE T1.K + T2.K + T3.K + T4.K = 100
AND   T1.CNT + T2.CNT + T3.CNT + T4.CNT = 100
AND   T1.J + T2.J + T3.J + T4.J BETWEEN 240 AND 320

本题的关键是使用 CONNECT BY LEVEL <= 100 创造循环，再用CROSS JOIN 进行循环，筛选结果后得出。

SELECT LEVEL*5 CNT FROM DUAL CONNECT BY LEVEL <= 100/5

SELECT LEVEL CNT FROM DUAL CONNECT BY LEVEL <= 10

 可用于产生多行，带序号的结果。