前前浪 2020-05-27 16:08 采纳率: 33.3%
浏览 318
已采纳

sql 语句:对某字段排序后,求占总数60%的前x条数据

如图:求前n条数据,这n条数据的产量之和,占总产量的60%
(注意要按产品排序后的前n条数据)

图片说明

  • 写回答

1条回答 默认 最新

  • threenewbee 2020-05-27 16:40
    关注

    太复杂了,拿LINQ写了一个,看帮你转换的SQL

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    
    namespace Q1078172
    {
        class Program
        {
            static void Main(string[] args)
            {
                DataClasses1DataContext db = new DataClasses1DataContext();
                db.Log = Console.Out;
                var query =
                    (from x in db.Table1s.OrderByDescending(x => x.qnt)
                    let acc = x.qnt + (db.Table1s.Any(y => x.qnt <= y.qnt && x.name != y.name) ? db.Table1s.Where(y => x.qnt <= y.qnt && x.name != y.name).Select(y => y.qnt).Sum() : 0)
                     where acc < db.Table1s.Select(y => y.qnt).Sum() * 60 / 100
                     select new { acc, x }).OrderBy(x => x.acc);
    
                foreach (var item in query)
                    Console.WriteLine(item.x.name + "," + item.x.qnt + ",      " + item.acc);
            }
        }
    }
    
    
    hung,100,      100
    thai,80,      260
    cn,80,      260
    

    对应的sql (表名叫Table1,国家id叫id,国家名称叫name,产量叫qnt)

    SELECT [t3].[value] AS [acc], [t3].[id], [t3].[name], [t3].[qnt]
    FROM (
        SELECT [t0].[id], [t0].[name], [t0].[qnt], [t0].[qnt] + (
            (CASE
                WHEN EXISTS(
                    SELECT NULL AS [EMPTY]
                    FROM [dbo].[Table1] AS [t1]
                    WHERE ([t0].[qnt] <= [t1].[qnt]) AND ([t0].[name] <> [t1].[name]
    )
                    ) THEN (
                    SELECT SUM([t2].[qnt])
                    FROM [dbo].[Table1] AS [t2]
                    WHERE ([t0].[qnt] <= [t2].[qnt]) AND ([t0].[name] <> [t2].[name]
    )
                    )
                ELSE 0
             END)) AS [value]
        FROM [dbo].[Table1] AS [t0]
        ) AS [t3]
    WHERE [t3].[value] < (SELECT SUM([t0].[qnt]) * 60 / 100
    FROM [dbo].[Table1] AS [t0])
    ORDER BY [t3].[value], [t3].[qnt] DESC
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥20 qt中connect两个signal
  • ¥20 pix2pixHD运行测试命令时出现数据类型错误无法反向传播的问题
  • ¥15 python处理Excel符合条件的行自动填写数据分类
  • ¥15 汇编hook举例并讲解(通俗易懂,学习用)
  • ¥20 用c++语言模拟键盘电子琴设计
  • ¥15 STM32cubemx生成keil工程,有问题与正常的情况不同,求解!
  • ¥15 如何自动点击银行app的安全键盘,实现密码自动输入
  • ¥15 关于四边形重叠的问题
  • ¥15 用verilog语言设计一个简易的八音符电子琴,可通过按键输入来控制音响。演奏时可以选择是手演奏(由键盘输入)或自动演奏已存入的乐曲。能够自动演奏多首乐曲,且每首乐曲可重复演奏
  • ¥15 sap gui脚本每次到导出Excel的时候就停住不动。不会另存为。