備忘:
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;
}
}
}