MKCQ

brtcxsf

于 2012-04-23 22:39:22 发布

阅读量394

点赞数

CC 4.0 BY-SA版权

分类专栏： c# 文章标签： object date class

本文链接：https://blog.youkuaiyun.com/brtcxsf/article/details/7491681

c# 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了使用C#语言在特定日期内进行时间段数据筛选，并将其导出为文本文件的过程。具体包括了日期时间格式转换、时间段定义、SQL查询语句构造、参数设置以及数据导出等步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

備忘：

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.OleDb;

//SELECT DISTINCT [字段名] ，[其他字段名] FROM [表名] WHERE [检索条件字句]
//SELECT * FROM TEST WHERE id in (SELECT MIN(id) FROM TEST GROUP BY B)

namespace Csharp_MKCQ
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            txtDate.Text = DateTime.Now.ToString("yyyy-MM-dd");
        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            StringBuilder sb = new StringBuilder();
            DateTime morningFrom = DateTime.Parse(txtDate.Text+" 06:00");
            DateTime morningTo = DateTime.Parse(txtDate.Text + " 09:00");

DateTime nooningFrom = DateTime.Parse(txtDate.Text + " 11:30");
DateTime nooningTo = DateTime.Parse(txtDate.Text + " 13:30");

DateTime afternoonFrom = DateTime.Parse(txtDate.Text + " 17:00");
DateTime afternoonTo = DateTime.Parse(txtDate.Text + " 19:00");

DateTime nightFrom = DateTime.Parse(txtDate.Text + " 23:00");
DateTime nightTo = DateTime.Parse(txtDate.Text + " 00:30").AddDays(1);

DateTime date = DateTime.Parse(txtDate.Text.Trim());

            //sb.Append("SELECT '早餐' AS [TYPE], MIN(CHECKTIME) AS MIN_TIME, USERID FROM CHECKINOUT WHERE CHECKTIME>=@MORNING_FROM AND CHECKTIME<=@MORNING_TO GROUP BY USERID");
            //sb.Append(" UNION ALL ");
            //sb.Append("SELECT '中餐' AS [TYPE], MIN(CHECKTIME) AS MIN_TIME, USERID FROM CHECKINOUT WHERE CHECKTIME>=@NOONING_FROM AND CHECKTIME<=@NOONING_TO GROUP BY USERID");
            //sb.Append(" UNION ALL ");
            //sb.Append("SELECT '晚餐' AS [TYPE], MIN(CHECKTIME) AS MIN_TIME, USERID FROM CHECKINOUT WHERE CHECKTIME>=@AFTERNOON_FROM AND CHECKTIME<=@AFTERNOON_TO GROUP BY USERID");
            //sb.Append(" UNION ALL ");
            //sb.Append("SELECT '夜宵' AS [TYPE], MIN(CHECKTIME) AS MIN_TIME, USERID FROM CHECKINOUT WHERE CHECKTIME>=@NIGHT_FROM AND CHECKTIME<=@NIGHT_TO GROUP BY USERID");

            //OleDbParameter[] paras ={ new OleDbParameter("@MORNING_FROM",morningFrom),
            //                            new OleDbParameter("@MORNING_FROM",morningTo),
            //                            new OleDbParameter("@NOONING_FROM",nooningFrom),
            //                            new OleDbParameter("@NOONING_TO",nooningTo),
            //                            new OleDbParameter("@AFTERNOON_FROM",afternoonFrom),
            //                            new OleDbParameter("@AFTERNOON_TO",afternoonTo),
            //                            new OleDbParameter("@NIGHT_FROM",nightFrom),
            //                            new OleDbParameter("@NIGHT_TO",nightTo)
            //                        };

            sb.Append("SELECT SWITCH(CHECKTIME>=@MORNING_FROM AND CHECKTIME<=@MORNING_TO,'早餐',CHECKTIME>=@NOONING_FROM AND CHECKTIME<=@NOONING_TO,'午餐') AS [TYPE], CHECKTIME AS MIN_TIME, USERID FROM CHECKINOUT ");
            //sb.Append(" UNION ALL ");
            //sb.Append("SELECT '中餐' AS [TYPE], MIN(CHECKTIME) AS MIN_TIME, USERID FROM CHECKINOUT WHERE CHECKTIME>=@NOONING_FROM AND CHECKTIME<=@NOONING_TO GROUP BY USERID");
            //sb.Append(" UNION ALL ");
            //sb.Append("SELECT '晚餐' AS [TYPE], MIN(CHECKTIME) AS MIN_TIME, USERID FROM CHECKINOUT WHERE CHECKTIME>=@AFTERNOON_FROM AND CHECKTIME<=@AFTERNOON_TO GROUP BY USERID");
            //sb.Append(" UNION ALL ");
            //sb.Append("SELECT '夜宵' AS [TYPE], MIN(CHECKTIME) AS MIN_TIME, USERID FROM CHECKINOUT WHERE CHECKTIME>=@NIGHT_FROM AND CHECKTIME<=@NIGHT_TO GROUP BY USERID");

            OleDbParameter[] paras ={ new OleDbParameter("@MORNING_FROM",morningFrom),
                                        new OleDbParameter("@MORNING_FROM",morningTo),
                                        new OleDbParameter("@NOONING_FROM",nooningFrom),
                                        new OleDbParameter("@NOONING_TO",nooningTo)
                                        //new OleDbParameter("@AFTERNOON_FROM",afternoonFrom),
                                        //new OleDbParameter("@AFTERNOON_TO",afternoonTo),
                                        //new OleDbParameter("@NIGHT_FROM",nightFrom),
                                        //new OleDbParameter("@NIGHT_TO",nightTo)
                                    };
            DataTable dt = clAccess.ExecuteDataSet(sb.ToString(),paras).Tables[0];
            gvDetails.DataSource=dt;

        }
    }
}