任何人都可以看到这个SQL语句的问题？ [英] Can anyone see a problem with this SQL statement?

问题描述

嘿伙计们，

这句话没有结果，没有给出任何错误，但它总是返回0.它应该返回通过EFTpos支付的销售总额，给定一个id和日期范围。

 字符串 sqleft =   SELECT SUM（SalePrice）SalesPrice from Sales +  < span class =code-string>其中Stall =' + textBox1.Text +  'AND EFTPos = 1 AND date BETWEEN' + dateTimePicker1.Value.ToString（）+  'AND'  + dateTimePicker2.Value.ToString（）+  '; 
 SqlCommand execsqleft =  new  SqlCommand（sqleft，cn）; 
 
 execsqleft.ExecuteNonQuery（）; 
  double  resulteft =  0  .0d; 
 result = Convert.ToDouble（execsql.ExecuteScalar（））; 

我这样的所有其他陈述都有效，所以我相信EFTPos专栏存在问题。列数据类型是BIT，并将存储1或0.

这是语句在运行时读取的内容



SELECT SUM（ SalePrice）销售价格来自销售，其中Stall ='A5'和EFTPos = 1和日期BETWEEN '11 / 4/2014 12:00:00 AM'和'12 / 4/2014 12:00:00 AM'



谢谢！

解决方案

对于像SUM那样的聚合函数，你需要将GROUPPrice与其他一个GROUP BY联系起来字段和过滤器与你的条件。

例如

 选择总和（价格）来自订单 group   by  productid 其中 orderdate = 2014 

好吧，显然你应该检查where语句的每个子句在合并它们之前拥有它们 - 例如，做一个

 选择离散摊位

例如确定有一个'A5'值，从选择中删除EFTPos短语以查看是否得到结果，如果你这样做，那就是你下次工作的地方



你可以想要考虑一下，对于EFTPos部分

 EFTPos = cast（1比特）

试试..

 string strStall = textBox1。 Text ; 
 
  DateTime  dt1 = Convert.ToDatetime（dateTimePicker1.Value.ToString（））; 
  DateTime  dt2 = Convert.ToDatetime（dateTimePicker2.Value.ToString（））; 
 
 string sqleft =   SELECT SUM（Isnull（SalePrice，0））SalesPrice from Sales where Stall = @Stall AND EFTPos = 1 AND cast（日期为日期）BETWEEN强制转换（@ date1 as date）和强制转换（（@ date2 as date）; 
 SqlCommand execsqleft = new SqlCommand（sqleft， cn）; 
 
 execsqleft.Parameters.AddWithValue（  @Stall， strStall）; 
 execsqleft.Parameters.AddWithValue（  @ date1，dt1）; 
 execsqleft.Parameters.AddWithValue（  @ date2，dt2）; 
 
  double  resulteft =  0  .0d; 
 result = 转换 .ToDouble（execsql.ExecuteScalar（）的ToString（））; 

Hey guys,
This statement is returning no result, no error is given however it always returns 0. It should return the total amount of sales that have been paid via EFTpos, given an id and a date range.

string sqleft = "SELECT SUM(SalePrice) SalePrice from Sales " + "where Stall = '" + textBox1.Text + "' AND EFTPos = 1 AND date BETWEEN '" + dateTimePicker1.Value.ToString() + "' AND '" + dateTimePicker2.Value.ToString() + "'";
                SqlCommand execsqleft = new SqlCommand(sqleft, cn);

                execsqleft.ExecuteNonQuery();
                double resulteft = 0.0d;
                result = Convert.ToDouble(execsql.ExecuteScalar());

All my other statements like this one work, so I believe there is a problem with the EFTPos column. The column data type is BIT, and will store either 1 or 0.
Here is what the statement reads during runtime

SELECT SUM(SalePrice) SalePrice from Sales where Stall = 'A5' AND EFTPos = 1 AND date BETWEEN '11/4/2014 12:00:00 AM' AND '12/4/2014 12:00:00 AM'

Thank you!

解决方案

For aggregate functions like SUM you need to 'GROUP BY' the SalePrice with one of the other fields and the filter with your where conditions.
e.g

select sum(price) from orders group by productid where orderdate=2014

well, obviously you should check each clause of the where statement on its own before combining them - for example, do a

select discrete stall

for example to make sure there's an 'A5' value, remove the EFTPos phrase from the select to see if you get results, and if you do, that's where you work next

you may wish to think about this, for the EFTPos part

EFTPos = cast(1 as bit)

try..

string strStall=textBox1.Text;

DateTime dt1=Convert.ToDatetime(dateTimePicker1.Value.ToString());
DateTime dt2=Convert.ToDatetime(dateTimePicker2.Value.ToString());

string sqleft = "SELECT SUM(Isnull(SalePrice,0)) SalePrice from Sales where Stall = @Stall AND EFTPos = 1 AND cast(date as date) BETWEEN cast(@date1 as date) and cast((@date2 as date)";
SqlCommand execsqleft = new SqlCommand(sqleft, cn);

execsqleft.Parameters.AddWithValue("@Stall ",strStall);
execsqleft.Parameters.AddWithValue("@date1 ",dt1);
execsqleft.Parameters.AddWithValue("@date2",dt2);

double resulteft = 0.0d;
result = Convert.ToDouble(execsql.ExecuteScalar().ToString());

这篇关于任何人都可以看到这个SQL语句的问题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！