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

最新推荐文章于 2025-09-15 02:25:10 发布

转载最新推荐文章于 2025-09-15 02:25:10 发布 · 750 阅读

文章标签：

#join #insert #table

sql 专栏收录该内容

90 篇文章

订阅专栏

本文通过具体示例展示了如何使用SQL进行复杂的数据筛选与汇总操作，包括如何利用内连接及子查询来找出不同类别中金额达到特定百分比的记录。

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.货号))