- 例如我查询成功取票数(STATE = 1)为:
SELECT BOOK_TICKET_ITEM_ID, COUNT(1) AS SUCCESS_DRAW_NUMBER FROM T_BOOK_SESSION WHERE AREA_ID=10097 AND TO_DATE('2020-09-21 00:00:00', 'yyyy-mm-dd hh24:mi:ss') <= BOOKING_DATE AND
TO_DATE('2020-09-22 00:00:00', 'yyyy-mm-dd hh24:mi:ss') >= BOOKING_DATE AND STATE = 0 GROUP BY BOOK_TICKET_ITEM_ID
显示结果为(也是正是我想要的结果类型):
-
但是我想要查找取消预约的票数(STATE = 2),就不能得到类似成功取票数的结果,sql为
SELECT BOOK_TICKET_ITEM_ID, COUNT(1) AS CANCEL_BOOK_NUMBER FROM T_BOOK_SESSION WHERE AREA_ID=10097 AND TO_DATE('2020-09-21 00:00:00', 'yyyy-mm-dd hh24:mi:ss') <= BOOKING_DATE AND TO_DATE('2020-09-22 00:00:00', 'yyyy-mm-dd hh24:mi:ss') >= BOOKING_DATE AND STATE = 2 GROUP BY BOOK_TICKET_ITEM_ID
因为state=2,是确实没有记录的,而group by是要求要有记录才行,否则显示空值,就是如上图所示。而我期待的结果为BOOK_TICKET_ITEM_ID=113 , CANCEL_BOOK_NUMBER=0 这样一条结果记录。哪位大神帮告诉下解决方案,十分感谢。