sql联接查询之间的问题 [英] problem in sql join query with between and where clause

问题描述

我在使用SQL查询时遇到问题.查询如下

i am having problem with sql query. The query is as below

SELECT Products.ProductName, Products.origionalPrice, OrderDetails.Quantity,OrderDetails.UnitCost FROM Products LEFT JOIN OrderDetails ON Products.ProductID=OrderDetails.ProductID where OrderDetails.PurchaseDate between '1/4/2012' and '1/4/2012' 

但是，此休闲查询可以正常工作并显示结果.但是在SQL查询中的where子句和命令之间没有结果表是空的我在OrdersDetails表的purchasdate列中有一个日期为1/4/2012. y数据未显示在表中

however this fallowing query works fine and shows results. but with where clause and between command in sql query gives no results the table is empty i have a date in purchasdate colum of OrdersDetails table which is 1/4/2012. y data is not displayed in table

SELECT Products.ProductName, Products.origionalPrice, OrderDetails.Quantity,OrderDetails.UnitCost FROM Products LEFT JOIN OrderDetails ON Products.ProductID=OrderDetails.ProductID

public void bind()
    {
               string query = "SELECT Products.ProductName, Products.origionalPrice, OrderDetails.Quantity,OrderDetails.UnitCost FROM Products LEFT JOIN OrderDetails ON Products.ProductID=OrderDetails.ProductID where OrderDetails.PurchaseDate Between '" + DropDownList1.Text + "' and '" + DropDownList2.Text + "'";
        DataSet ds = obj.fillgrid(query);
        GridView1.DataSource = ds.Tables[0];
        GridView1.DataBind();
    }

推荐答案

首先，不要使用文字，请始终使用

First of all, don''t use literals, always use SqlParameter[^]. This will help you with conversion problems and protect against SQL injections.

If the problem is the time portion, you can either define the column in the database as date if you don''t need the time potion at all or if you need it, then one way is to eliminate it in your query. For the latter the querty could look something like:

SELECT Products.ProductName, Products.origionalPrice, OrderDetails.Quantity,OrderDetails.UnitCost 
FROM Products LEFT JOIN OrderDetails 
ON Products.ProductID=OrderDetails.ProductID 
WHERE  CAST(OrderDetails.PurchaseDate AS date) BETWEEN '1/4/2012' and '1/4/2012' 

或者实际上，当正确使用参数时，将是:

Or actually when parameters are correctly used, it would be:

SELECT Products.ProductName, Products.origionalPrice, OrderDetails.Quantity,OrderDetails.UnitCost 
FROM Products LEFT JOIN OrderDetails 
ON Products.ProductID=OrderDetails.ProductID 
WHERE  CAST(OrderDetails.PurchaseDate AS date) BETWEEN @startDate and @endDate 

它将"1/4/2012"转换为"1/4/2012 00": 00''
所以您在''1/4/2012 00:00''和''1/4/2012 00:00''之间没有任何东西

将您的第二个日期更改为"2012年1月5日"

It is converting ''1/4/2012'' to ''1/4/2012 00:00''
So you don''t have any between ''1/4/2012 00:00'' and ''1/4/2012 00:00''

Change your second date to ''1/5/2012''

我看到几个问题:
1)完全与日期时间保持一致总是一个好主意

一种方法是使用标准的SQLS不分隔格式"yyyymmdd hh:mm:ss.mmm"定义日期

例如

从产品中选择SELECT Products.ProductName，Products.origionalPrice，OrderDetails.Quantity，OrderDetails.UnitCost在产品上左加入OrderDetails.ProductID = OrderDetails.ProductID，其中OrderDetails.PurchaseDate在"20120104"和"20120104 23:59:59.999"之间

(或者，如果您想使第一个日期显然是2012年1月3日午夜，则可以在"20120104 00:00:00.000"和"20120104 23:59:59.999"之间进行选择)

如果时间分量不重要，那么总可以选择忽略它...

其中cast(datediff(day，0，OrderDetails.PurchaseDate)作为datetime)在cast(datediff(day，0，''20120104'')作为datetime)和cast(datediff(day，0，''20120104'')之间datetime)将返回某个日期为4日某个日期的所有行

2)查询本身是可疑的，where子句不会作为左联接的一部分执行.因此，如果您没有与购买日期相匹配的订单详细信息，那么您也将看不到产品详细信息.

如果您希望查看产品详细信息，而不管是否出现订单详细信息，则查询需要更改为以下内容(包括日期为2012年1月4日或5日的所有行):

从产品中选择Products.ProductName，Products.origionalPrice，OrderDetails.Quantity，OrderDetails.UnitCost从产品左侧加入OrderDetails到Products.ProductID = OrderDetails.ProductID，并在cast(datediff( day，0，''20120104'')作为datetime)和cast(datediff(day，0，''20120105'')作为datetime)

I see a couple of problems:
1) It is always a good idea to be completely unambiguous with datetimes

One way is to use the standard SQLS unseparated format ''yyyymmdd hh:mm:ss.mmm'' to define your dates

e.g.

SELECT Products.ProductName, Products.origionalPrice, OrderDetails.Quantity,OrderDetails.UnitCost FROM Products LEFT JOIN OrderDetails ON Products.ProductID=OrderDetails.ProductID where OrderDetails.PurchaseDate between ''20120104'' and ''20120104 23:59:59.999''

(or between ''20120104 00:00:00.000'' and ''20120104 23:59:59.999'' if you want to make it obvious that the first date is midnight of 3rd Jan 2012)

If the time component isn''t significant then there is always the option of ignoring it...

where cast(datediff(day,0,OrderDetails.PurchaseDate) as datetime) between cast(datediff(day,0,''20120104'') as datetime) and cast(datediff(day,0,''20120104'') as datetime) will return all rows with a date sometime on the 4th

2) The query itself is suspect, the where clause is not executed as part of the left join. Therefore if you have no order details with a matching purchase date then you will also not see the product details.

If you wish to see the product details irrespective of whether the order details appear then the query needs to change to something like this (includes all rows with a date on the 4th or 5th Jan 2012):

SELECT Products.ProductName, Products.origionalPrice, OrderDetails.Quantity,OrderDetails.UnitCost FROM Products LEFT JOIN OrderDetails ON Products.ProductID=OrderDetails.ProductID and cast(datediff(day,0,OrderDetails.PurchaseDate) as datetime) between cast(datediff(day,0,''20120104'') as datetime) and cast(datediff(day,0,''20120105'') as datetime)

这篇关于sql联接查询之间的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！