mysql - 无法使用子查询 mysql 获取日期值

Question

我有两个表，名为

1 个表smartpos.pos_order_Id

+---------+--------------+---------+--------+--------------+----------------+------------+
| orderId | restaurantId | tableId | closed | customerName | customerNumber | dateorderd |
+---------+--------------+---------+--------+--------------+----------------+------------+
|       7 |           14 |       0 | yes    |              |                | 21/03/2018 |
|       8 |           14 |       0 | yes    |              |                | 21/03/2018 |
|       9 |           14 |       0 | no     |              |                | 20/03/2018 |
|      10 |           14 |       0 | yes    | soumya       | 1234567890     | 21/03/2018 |
|      11 |           14 |       0 | yes    |              |                | 21/03/2018 |
|      12 |           14 |       0 | yes    |              |                | 21/03/2018 |
|      13 |           14 |       0 | yes    |              |                | 21/03/2018 |
|      14 |           14 |       0 | yes    |              |                | 20/03/2018 |
|      15 |           14 |       0 | no     |              |                | 22/03/2018 |
+---------+--------------+---------+--------+--------------+----------------+------------+

2smartpos.pos_invoice

   +---------------+---------+----------+-------------+-------------+------------+-------------+---------------+
| invoiceNumber | orderId | totalAmt | discountAmt | totalTaxAmt | grandTotal | paymentmode | paymentrefNum |
+---------------+---------+----------+-------------+-------------+------------+-------------+---------------+
|             1 |       7 |      200 |          34 |          46 |        212 | Cash        |               |
|             2 |      10 |     1200 |         200 |         280 |       1280 | Cash        |               |
|             3 |       1 |      720 |          34 |         120 |        806 | Cash        |               |
|             4 |      12 |      240 |          34 |          58 |        264 | Cash        |               |
|             5 |      13 |      330 |          32 |          83 |        381 | Cash        |               |
|             6 |      14 |       80 |           2 |          22 |        100 | Cash        |               |
+---------------+---------+----------+-------------+-------------+------------+-------------+---------------+

我想使用restaurantId和两个日期获取发票详细信息，方法是提供如下的restaurantId和日期

select inv.invoiceNumber ,inv.totalAmt,inv.discountAmt,inv.totalTaxAmt,inv.grandTotal,i.dateorderd from smartpos.pos_invoice inv,smartpos.pos_order_Id i where inv.invoiceNumber in (select invv.invoiceNumber from  smartpos.pos_invoice invv where invv.orderId in(select ii.orderId from smartpos.pos_order_Id ii where ii.closed='yes' and ii.restaurantId=14 and STR_TO_DATE(dateorderd,'%d/%m/%Y') between  STR_TO_DATE('20/03/2018','%d/%m/%Y') and STR_TO_DATE('21/03/2018','%d/%m/%Y'))) group by inv.invoiceNumber ;

输出:

+---------------+----------+-------------+-------------+------------+------------+
| invoiceNumber | totalAmt | discountAmt | totalTaxAmt | grandTotal | dateorderd |
+---------------+----------+-------------+-------------+------------+------------+
|             1 |      200 |          34 |          46 |        212 | NULL       |
|             2 |     1200 |         200 |         280 |       1280 | NULL       |
|             4 |      240 |          34 |          58 |        264 | NULL       |
|             5 |      330 |          32 |          83 |        381 | NULL       |
|             6 |       80 |           2 |          22 |        100 | NULL       |
+---------------+----------+-------------+-------------+------------+------------+

但是当我运行上面的查询时它给出空值，如何获取日期？

Answer 1

很难理解你写的内容，但我认为你的查询比看起来简单得多，尝试使用这种方法:

select 
    inv.invoiceNumber ,
    inv.totalAmt,
    inv.discountAmt,
    inv.totalTaxAmt,
    inv.grandTotal,
    i.dateorderd 
from 
    smartpos.pos_invoice inv,
    smartpos.pos_order_Id i 
where 
    inv.orderId = i.orderId
and 
    i.closed='yes' 
and 
    i.restaurantId=14
and 
    STR_TO_DATE(dateorderd,'%d/%m/%Y') between STR_TO_DATE('20/03/2018','%d/%m/%Y') and STR_TO_DATE('21/03/2018','%d/%m/%Y')
group by 
    inv.invoiceNumber;