Oracle SQL查询1997年6月前两日供货的供应商名称

关联表结构 #

• suppliers表列：SUPPLIERID, COMPANYNAME, CONTACTNAME, CONTACTTITLE, ADDRESS, CITY, REGION, POSTALCODE, COUNTRY, PHONE, FAX, HOMEPAGE
• products表列：PRODUCTID, PRODUCTNAME, SUPPLIERID, CATEGORYID, QUANTITYPERUNIT, UNITPRICE, UNITSINSTOCK, UNITSONORDER, REORDERLEVEL, DISCONTINUED
• orderdetails表列：ORDERID, PRODUCTID, UNITPRICE, QUANTITY, DISCOUNT
• orders表列：ORDERID, CUSTOMERID, EMPLOYEEID, TERRITORYID, ORDERDATE, REQUIREDDATE, SHIPPEDDATE, SHIPVIA, FREIGHT, SHIPNAME, SHIPADDRESS, SHIPCITY, SHIPREGION, SHIPPOSTALCODE, SHIPCOUNTRY

需求 #

获取1997年6月1日至2日期间有供货记录的供应商名称。

尝试的SQL（无法正常运行） #

SELECT DISTINCT companyname FROM suppliers 
JOIN products ON suppliers.supplierid=products.supplierid
JOIN orderdetails ON products.productid=orderdetails.productid
JOIN orders ON orderdetails.orderid=orders.orderid

where  orderdate between TO_DATE('01/06/97', 'DD/MM/YY')
                    AND TO_DATE('02/06/97', 'DD/MM/YY');

修正方案 #

你的SQL逻辑框架是正确的，无法运行大概率是日期函数的数据库兼容性问题，不同数据库对日期转换的语法要求不同，以下是针对主流数据库的修正版本：

1. Oracle数据库（匹配你用的TO_DATE函数） #

如果是Oracle，检查是否是日期格式或语法细节问题，修正后的语句如下：

SELECT DISTINCT s.COMPANYNAME
FROM suppliers s
JOIN products p ON s.SUPPLIERID = p.SUPPLIERID
JOIN orderdetails od ON p.PRODUCTID = od.PRODUCTID
JOIN orders o ON od.ORDERID = o.ORDERID
WHERE o.ORDERDATE BETWEEN TO_DATE('01/06/1997', 'DD/MM/YYYY')
                      AND TO_DATE('02/06/1997 23:59:59', 'DD/MM/YYYY HH24:MI:SS');

• 补充完整年份1997避免歧义
• 若ORDERDATE包含时分秒，需在结束日期加上23:59:59，否则会漏掉6月2日当天带时间的订单

2. MySQL数据库 #

MySQL不支持TO_DATE，需改用STR_TO_DATE函数：

SELECT DISTINCT s.COMPANYNAME
FROM suppliers s
JOIN products p ON s.SUPPLIERID = p.SUPPLIERID
JOIN orderdetails od ON p.PRODUCTID = od.PRODUCTID
JOIN orders o ON od.ORDERID = o.ORDERID
WHERE o.ORDERDATE BETWEEN STR_TO_DATE('01/06/1997', '%d/%m/%Y')
                      AND STR_TO_DATE('02/06/1997 23:59:59', '%d/%m/%Y %H:%i:%s');

3. SQL Server数据库 #

SQL Server使用CONVERT函数处理日期转换：

SELECT DISTINCT s.COMPANYNAME
FROM suppliers s
JOIN products p ON s.SUPPLIERID = p.SUPPLIERID
JOIN orderdetails od ON p.PRODUCTID = od.PRODUCTID
JOIN orders o ON od.ORDERID = o.ORDERID
WHERE o.ORDERDATE BETWEEN CONVERT(DATETIME, '01/06/1997', 103)
                      AND CONVERT(DATETIME, '02/06/1997 23:59:59', 103);

• 样式码103对应DD/MM/YYYY格式

内容的提问来源于stack exchange，提问作者Paula12