首页 > 试题广场 >

有如下示例数据：订单表: A order_id user_

[不定项选择题]

有如下示例数据：
订单表: A

order_id user_id purchase_time
11701245001 11001 1498882475
11701245002 11002 1498882475

订单表：B

id order_id goods_id price
1 11701245001 1001 10
2 11701245001 1002 20
3 11701245002 1001 10

问题：
用SQL 查询2019年1月1日起至2019年8月16号之间(不含7月1号)购买过商品ID为1001的user_id和order_id、goods_id和price

select user_id,order_id,goods_id,price from A ,B where A.order_id=B.order_id and b.goods_id='1001' and purchase_time >='2019-01-01 00:00:00' and purchase_time <'2019-08-16 00:00:00';

SELECT b.`order_id`,a.`user_id`,b.`goods_id`,b.`price` FROM test_order a,test_order_item b WHERE a.`purchase_time` >= UNIX_TIMESTAMP('2019-01-01 00:00:00') AND a.`purchase_time` < UNIX_TIMESTAMP('2019-08-16 00:00:00') AND b.`goods_id` = '1001' AND a.`order_id` = b.`order_id`;

SELECT b.`order_id`,a.`user_id`,b.`goods_id`,b.`price` FROM test_order a LEFT JOIN test_order_item b ON b.`order_id` = a.`order_id` WHERE a.purchase_time >= UNIX_TIMESTAMP('2019-01-01 00:00:00') AND a.purchase_time < UNIX_TIMESTAMP('2019-08-16 00:00:00') AND b.`goods_id`='1001';

select a.user_id,a.order_id,b.goods_id,b.price from A a,B b where a.order_id=b.order_id and b.goods_id='1001' and a.purchase_time BETWEEN unix_timestamp('2019-01-01 00:00:00') and unix_timestamp('2019-08-16 00:00:00');

查看答案及解析