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

Question

如图：求前n条数据，这n条数据的产量之和，占总产量的60%
（注意要按产品排序后的前n条数据）

Answer 1

太复杂了，拿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