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

来源：互联网发布：中美贸易发展历程数据编辑：程序博客网时间：2024/06/05 01:17

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