为什么order by count(lab_results. testd) desc在c#中不工作-但在SQL Serv

Question

我需要在c#中运行SELECT语句和ORDER BY COUNT()。

SELECT在SQL Server中工作得很好，但在我的c# Windows窗体应用程序中不工作。

这是点击按钮时使用的代码:

private void button2_Click(object sender, EventArgs e)
{
string sql = @" SELECT COUNT(lab_results.testid) as 'Test Count' , labtests.TestName  
FROM LabTests , lab_results 
WHERE labtests.testid = lab_results.testid
AND lab_results.approved_date IS NOT NULL ");
string condition = "";
string groupby = "GROUP BY labtests.testname";
string orderby = "ORDER BY COUNT(lab_results.testid) DESC";
DateTime fromDate;
DateTime toDate;
if (!DateTime.TryParse(dtFromDate.Value.ToString(), out fromDate))
{
System.Windows.Forms.MessageBox.Show("Invalid From Date");
}
else if (!DateTime.TryParse(dtToDate.Value.ToString(), out toDate))
{
System.Windows.Forms.MessageBox.Show("Invalid to Date");
}
else
{
condition += " and cast(lab_results.approved_date as date) between '" + fromDate + "' and '" + toDate + "'";
}
DataTable dt = data.fireDatatable(string.Format(sql + condition + groupby + orderby));
OrdersDataGridView.DataSource = dt;
OrdersDataGridView.Refresh();
}

没有顺序的代码工作，但有顺序没有输出，在SQL Server的SELECT是工作

order by的错误是什么?我需要你的帮助。

Answer 1

谢谢大家，我收到了你们的评论，现在我把旧样式的join改为新样式，使用INNER JOIN并解决了这个问题，这就是解决方案:

private void button2_Click(object sender, EventArgs e)
{
string sql = @" SELECT COUNT(lab_results.testid) as 'Test Count' , labtests.TestName  
FROM lab_results 
inner join labtests on  lab_results.testid = labtests.testid
where lab_results.approved_date IS NOT NULL  ");
string condition = "";
string groupby = "  GROUP BY labtests.testname";
string orderby = "  ORDER BY COUNT(lab_results.testid) DESC";
DateTime fromDate;
DateTime toDate;
if (!DateTime.TryParse(dtFromDate.Value.ToString(), out fromDate))
{
System.Windows.Forms.MessageBox.Show("Invalid From Date");
}
else if (!DateTime.TryParse(dtToDate.Value.ToString(), out toDate))
{
System.Windows.Forms.MessageBox.Show("Invalid to Date");
}
else
{
condition += " and cast(lab_results.approved_date as date) between '" + fromDate + "' and '" + toDate + "'";
}
DataTable dt = data.fireDatatable(string.Format(sql + condition + groupby + orderby));
OrdersDataGridView.DataSource = dt;
OrdersDataGridView.Refresh();
}

下周我也将尝试其他解决方案，并使用参数和发布其他方式。