一般简单的做法是通过程序来实现
先定义一个DataTable,里面只有一列in_month,数据保存2009-01 ,2009-02,2009-03,2009-04,......2009-12然后与select to_char(in_date, 'YYYY-MM') as in_month, count(*) from t where in_date >= to_date('2009-01-01', 'YYYY-MM-DD') group by in_month order by in_month 得到的DataTable进行合并,合并字段为in_month合并方法 protected DataTable MergeDataTable(DataTable dt1, DataTable dt2, string KeyColName,bool isMergeAll)
{
//合并列
DataTable ndt1 = dt1.Copy();
DataTable ndt2 = dt2.Copy();
ndt2.PrimaryKey = new DataColumn[] { ndt2.Columns[KeyColName] };
for (int i = 0; i < ndt2.Columns.Count; i++)
{
if (ndt2.Columns[i].ColumnName.ToLower().Trim() != KeyColName.ToLower().Trim())
{
ndt1.Columns.Add(ndt2.Columns[i].ColumnName);
}
} //合并相同主键的数据
for (int i = 0; i < ndt1.Rows.Count; i++)
{
DataRow inrow = ndt2.Rows.Find(ndt1.Rows[i][KeyColName]);
if (inrow != null)
{
for (int j = 0; j < ndt2.Columns.Count; j++)
{
if (ndt2.Columns[j].ColumnName.ToLower().Trim() != KeyColName.ToLower().Trim())
{
ndt1.Rows[i][ndt2.Columns[j].ColumnName] = inrow[ndt2.Columns[j].ColumnName];
}
}
if (isMergeAll)
{
ndt2.Rows.Remove(inrow);
}
}
}
if (isMergeAll)
{
//合并不同主键的数据
for (int i = 0; i < ndt2.Rows.Count; i++)
{
DataRow row = ndt1.NewRow();
for (int j = 0; j < ndt2.Columns.Count; j++)
{
row[ndt2.Columns[j].ColumnName] = ndt2.Rows[i][ndt2.Columns[j].ColumnName];
}
ndt1.Rows.Add(row);
}
}
return ndt1;
}
先定义一个DataTable,里面只有一列in_month,数据保存2009-01 ,2009-02,2009-03,2009-04,......2009-12然后与select to_char(in_date, 'YYYY-MM') as in_month, count(*) from t where in_date >= to_date('2009-01-01', 'YYYY-MM-DD') group by in_month order by in_month 得到的DataTable进行合并,合并字段为in_month合并方法 protected DataTable MergeDataTable(DataTable dt1, DataTable dt2, string KeyColName,bool isMergeAll)
{
//合并列
DataTable ndt1 = dt1.Copy();
DataTable ndt2 = dt2.Copy();
ndt2.PrimaryKey = new DataColumn[] { ndt2.Columns[KeyColName] };
for (int i = 0; i < ndt2.Columns.Count; i++)
{
if (ndt2.Columns[i].ColumnName.ToLower().Trim() != KeyColName.ToLower().Trim())
{
ndt1.Columns.Add(ndt2.Columns[i].ColumnName);
}
} //合并相同主键的数据
for (int i = 0; i < ndt1.Rows.Count; i++)
{
DataRow inrow = ndt2.Rows.Find(ndt1.Rows[i][KeyColName]);
if (inrow != null)
{
for (int j = 0; j < ndt2.Columns.Count; j++)
{
if (ndt2.Columns[j].ColumnName.ToLower().Trim() != KeyColName.ToLower().Trim())
{
ndt1.Rows[i][ndt2.Columns[j].ColumnName] = inrow[ndt2.Columns[j].ColumnName];
}
}
if (isMergeAll)
{
ndt2.Rows.Remove(inrow);
}
}
}
if (isMergeAll)
{
//合并不同主键的数据
for (int i = 0; i < ndt2.Rows.Count; i++)
{
DataRow row = ndt1.NewRow();
for (int j = 0; j < ndt2.Columns.Count; j++)
{
row[ndt2.Columns[j].ColumnName] = ndt2.Rows[i][ndt2.Columns[j].ColumnName];
}
ndt1.Rows.Add(row);
}
}
return ndt1;
}
然后用它LEFT JOIN实际数据表