数据分成两份,每份都有各种类别,一份金额占60%左右

    技术2022-05-11  16

    DECLARE @t TABLE(类别 varchar(10), 货号 int, 金额 int)INSERT @t SELECT '布类', 1112, 100UNION ALL SELECT '布类', 1142, 234UNION ALL SELECT '布类', 3112, 453UNION ALL SELECT '布类', 1314, 20UNION ALL SELECT '布类', 1315, 245UNION ALL SELECT '合金', 2112, 120UNION ALL SELECT '合金', 2122, 340UNION ALL SELECT '合金', 2132, 100UNION ALL SELECT '合金', 2142, 1340UNION ALL SELECT '合金', 2152, 10UNION ALL SELECT '合金', 2162, 1040UNION ALL SELECT '合金', 2172, 232UNION ALL SELECT '合金', 2182, 1300UNION ALL SELECT '银饰', 3345, 3239UNION ALL SELECT '银饰', 3445, 322UNION ALL SELECT '银饰', 3545, 324UNION ALL SELECT '银饰', 3645, 222239UNION ALL SELECT '银饰', 3745, 139UNION ALL SELECT '银饰', 3845, 333UNION ALL SELECT '银饰', 3945, 323

    -- 40%SELECT A.*FROM @t A INNER JOIN(  SELECT 类别, 金额 = SUM(金额) * .4  FROM @t  GROUP BY 类别 )B   ON A.类别 = B.类别   AND B.金额 >= (     SELECT SUM(金额) FROM @t      WHERE 类别 = A.类别       AND (金额 < A.金额 OR 金额 = A.金额 AND 货号 <= A.货号))

    -- 60%SELECT A.*FROM @t A INNER JOIN(  SELECT 类别, 金额 = SUM(金额) * .4  FROM @t  GROUP BY 类别 )B   ON A.类别 = B.类别   AND B.金额 < (     SELECT SUM(金额) FROM @t      WHERE 类别 = A.类别       AND (金额 < A.金额 OR 金额 = A.金额 AND 货号 <= A.货号)) 


    最新回复(0)