求C#应用对接SQL数据库按有效期设置价格折扣的SQL查询实现方案

一、前置字段假设 #

以下逻辑基于通用表结构设计做预设，你可根据实际业务字段调整：

• New表默认包含字段：商品唯一标识goods_id、商品名称goods_name、商品原价origin_price、当前售价current_price、商品生产/入库时间production_date（datetime类型）
• validity字段默认存储商品总有效时长（单位：天，int类型）；如果该字段存储的是商品到期日（datetime类型），可参考后续逻辑说明调整计算规则
• 所有SQL语法基于SQL Server编写，其他数据库可替换对应日期函数

二、SQL语句实现 #

2.1 动态查询折扣价语句 #

查询时实时计算商品对应折扣价，不会修改表内原始数据：

USE Market;
SELECT 
    goods_id,
    goods_name,
    origin_price,
    validity,
    production_date,
    -- 计算剩余有效期占比
    (validity - DATEDIFF(DAY, production_date, GETDATE())) * 1.0 / validity AS remaining_ratio,
    -- 按规则计算折扣后价格
    CASE
        WHEN (validity - DATEDIFF(DAY, production_date, GETDATE())) * 1.0 / validity <= 0.25 THEN origin_price * 0.5
        WHEN (validity - DATEDIFF(DAY, production_date, GETDATE())) * 1.0 / validity <= 0.5 THEN origin_price * 0.8
        ELSE origin_price
    END AS discount_price
FROM [New]
-- 过滤未过期商品
WHERE (validity - DATEDIFF(DAY, production_date, GETDATE())) > 0;

2.2 批量更新商品售价语句 #

直接更新表内current_price字段值，适合定时任务执行：

USE Market;
UPDATE [New]
SET current_price = CASE
    WHEN (validity - DATEDIFF(DAY, production_date, GETDATE())) * 1.0 / validity <= 0.25 THEN origin_price * 0.5
    WHEN (validity - DATEDIFF(DAY, production_date, GETDATE())) * 1.0 / validity <= 0.5 THEN origin_price * 0.8
    ELSE origin_price
END
WHERE (validity - DATEDIFF(DAY, production_date, GETDATE())) > 0;

如果validity字段存储的是商品到期日（datetime类型），替换计算逻辑即可：
总有效时长 = DATEDIFF(DAY, production_date, validity)
剩余时长 = DATEDIFF(DAY, GETDATE(), validity)

三、C#对接实现指导 #

3.1 ADO.NET原生实现 #

适合轻量项目，.NET Core/.NET 5+需先安装Nuget包System.Data.SqlClient：

using System.Data.SqlClient;
using System.Collections.Generic;

// 替换为自己的数据库连接字符串
string connString = "Server=你的数据库地址;Database=Market;Uid=账号;Pwd=密码;TrustServerCertificate=True;";

// 1. 查询商品折扣价列表
List<GoodsInfo> goodsList = new List<GoodsInfo>();
using (SqlConnection conn = new SqlConnection(connString))
{
    string querySql = @"SELECT 
                        goods_id,
                        goods_name,
                        origin_price,
                        CASE
                            WHEN (validity - DATEDIFF(DAY, production_date, GETDATE())) * 1.0 / validity <= 0.25 THEN origin_price * 0.5
                            WHEN (validity - DATEDIFF(DAY, production_date, GETDATE())) * 1.0 / validity <= 0.5 THEN origin_price * 0.8
                            ELSE origin_price
                        END AS discount_price
                    FROM [New]
                    WHERE (validity - DATEDIFF(DAY, production_date, GETDATE())) > 0";
    using (SqlCommand cmd = new SqlCommand(querySql, conn))
    {
        conn.Open();
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                goodsList.Add(new GoodsInfo
                {
                    GoodsId = reader.GetInt32(0),
                    GoodsName = reader.GetString(1),
                    OriginPrice = reader.GetDecimal(2),
                    DiscountPrice = reader.GetDecimal(3)
                });
            }
        }
    }
}

// 2. 批量更新商品售价
using (SqlConnection conn = new SqlConnection(connString))
{
    string updateSql = @"UPDATE [New]
                        SET current_price = CASE
                            WHEN (validity - DATEDIFF(DAY, production_date, GETDATE())) * 1.0 / validity <= 0.25 THEN origin_price * 0.5
                            WHEN (validity - DATEDIFF(DAY, production_date, GETDATE())) * 1.0 / validity <= 0.5 THEN origin_price * 0.8
                            ELSE origin_price
                        END
                        WHERE (validity - DATEDIFF(DAY, production_date, GETDATE())) > 0";
    using (SqlCommand cmd = new SqlCommand(updateSql, conn))
    {
        conn.Open();
        int affectRows = cmd.ExecuteNonQuery();
        Console.WriteLine($"共更新{affectRows}条商品价格");
    }
}

// 商品实体类
public class GoodsInfo
{
    public int GoodsId { get; set; }
    public string GoodsName { get; set; }
    public decimal OriginPrice { get; set; }
    public decimal DiscountPrice { get; set; }
}

3.2 EF Core实现 #

适合中大型项目，需先安装Nuget包Microsoft.EntityFrameworkCore、Microsoft.EntityFrameworkCore.SqlServer：

using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System;

// 数据库上下文类
public class MarketDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("Server=你的数据库地址;Database=Market;Uid=账号;Pwd=密码;TrustServerCertificate=True;");
    }
    public DbSet<Goods> NewGoods { get; set; }
}

// 表映射实体类
public class Goods
{
    public int GoodsId { get; set; }
    public string GoodsName { get; set; }
    public decimal OriginPrice { get; set; }
    public decimal CurrentPrice { get; set; }
    public int Validity { get; set; }
    public DateTime ProductionDate { get; set; }
}

// 业务逻辑代码
public class GoodsService
{
    // 查询折扣商品列表
    public List<object> GetDiscountGoods()
    {
        using (var db = new MarketDbContext())
        {
            return db.NewGoods
                .Where(g => EF.Functions.DateDiffDay(g.ProductionDate, DateTime.Now) < g.Validity)
                .Select(g => new
                {
                    g.GoodsId,
                    g.GoodsName,
                    g.OriginPrice,
                    DiscountPrice = (EF.Functions.DateDiffDay(g.ProductionDate, DateTime.Now) * 1.0 / g.Validity) >= 0.75 ? g.OriginPrice * 0.5m :
                                    (EF.Functions.DateDiffDay(g.ProductionDate, DateTime.Now) * 1.0 / g.Validity) >= 0.5 ? g.OriginPrice * 0.8m :
                                    g.OriginPrice
                }).ToList<object>();
        }
    }

    // 批量更新商品价格
    public int UpdateGoodsPrice()
    {
        using (var db = new MarketDbContext())
        {
            var validGoods = db.NewGoods.Where(g => EF.Functions.DateDiffDay(g.ProductionDate, DateTime.Now) < g.Validity).ToList();
            foreach (var goods in validGoods)
            {
                var usedRatio = EF.Functions.DateDiffDay(goods.ProductionDate, DateTime.Now) * 1.0 / goods.Validity;
                goods.CurrentPrice = usedRatio >= 0.75 ? goods.OriginPrice * 0.5m :
                                    usedRatio >= 0.5 ? goods.OriginPrice * 0.8m :
                                    goods.OriginPrice;
            }
            return db.SaveChanges();
        }
    }
}

四、注意事项 #

• 价格字段统一使用decimal类型，避免浮点运算精度丢失
• 建议将价格更新逻辑封装为每日定时任务执行，无需每次查询实时更新，降低数据库压力
• 表名New是SQL关键字，操作时需要加转义符（SQL Server用[]，MySQL用反引号）

内容的提问来源于stack exchange，提问作者marianm