我有一些业务规则需要查询数据库。现在,下面的sql正在使用子查询,但我希望将每个查询更改为join语句。
我的数据库是DB2,但是我使用mysql http://www.sqlfiddle.com/#!9/3d10ae/3创建了sql http://www.sqlfiddle.com/#!9/3d10ae/3。
如果查询返回结果,则需要检查客户是否在过去60天内购买了项目编号1025926,然后客户购买了该项目。
SELECT ord1.oditnr, ord1.odredt, itm.imdesc
FROM orderdetails ord1
LEFT JOIN items itm ON ord1.oditnr = itm.imitnr
WHERE EXISTS (SELECT *
FROM orderdetails ord2
WHERE ord1.odbcsn = ord2.odbcsn
AND ord2.oditnr = '1022925'
AND odredt >= VARCHAR_FORMAT(CURRENT TIMESTAMP - 60 DAYS, 'YYYYMMDD') )
AND ord1.odbcsn = '11677'
ORDER BY odredt desc ;我需要检查客户是否在过去60天内没有购买商品编号2521809,如果查询返回结果,那么客户没有购买该项目。
SELECT ord1.oditnr, ord1.odredt, itm.imdesc
FROM orderdetails ord1
LEFT JOIN items itm ON ord1.oditnr = itm.imitnr
WHERE NOT EXISTS (SELECT *
FROM orderdetails ord2
WHERE ord1.odbcsn = ord2.odbcsn
AND ord2.oditnr = '2521809'
AND odredt >= VARCHAR_FORMAT(CURRENT TIMESTAMP - 60 DAYS, 'YYYYMMDD') )
AND ord1.odbcsn = '11677'
ORDER BY odredt desc ;发布于 2016-01-28 20:05:04
如果找不到支持子查询的其他工具,可以尝试以下操作:
SELECT ord1.oditnr, ord1.odredt, itm.imdesc
FROM orderdetails ord1
INNER JOIN orderdetails ord2
ON ord1.odbcsn = ord2.odbcsn
LEFT JOIN items itm ON ord1.oditnr = itm.imitnr
WHERE ord2.oditnr = '1022925'
AND ord2.odredt >= (CURDATE() - interval 60 day + 0)
AND ord1.odbcsn = '11677'
ORDER BY odredt desc ;
-- Updated this query
SELECT ord1.oditnr, ord1.odredt, itm.imdesc
FROM orderdetails ord1
LEFT JOIN orderdetails ord2
ON ord1.odbcsn = ord2.odbcsn
LEFT JOIN items itm ON ord1.oditnr = itm.imitnr
WHERE ord2.oditnr = '2521809'
AND ord2.odredt >= (CURDATE() - interval 60 day + 0)
AND ord1.odbcsn = '11677'
ORDER BY odredt desc ;更新:我调整了底部查询,将内部联接更改为左联接。但是,与OP发布的查询不同,如果未找到ord2.oditnr,此版本将返回一个空集--这在直觉上是有意义的--如果找不到订单号,则不要返回任何行。如果没有某种类型的子查询,实际上就没有一种方法可以返回基于
最起码,顶部的查询与OPs输出匹配。
https://stackoverflow.com/questions/35069459
复制相似问题