Home>

I am new to SQL.
I would like to extract data under the following conditions:
How should I write it?
I want to write as simple SQL as possible.
Thank you.

//////////////////////////////////////////////// /////////
Extraction condition: I want to acquire purchase data with the purchase date of the previous month starting from this month.
Database: Oracle
Extraction source: konyu.konyubi, syohin.syohinmei, konyu.kazu
(Table.item name)
This month: 2017,2

Extract image:
Purchase Date | Product Name | Number |
-------------------
20170101 | Ame | 2 |
20170115 | Gum | 1 |

//////////////////////////////////////////////// //////////

I thought I could write the following SQL.
(Sorry, there is no execution environment ・ Because it is a beginner, I think there is a syntax error.)

SELECT
 konyu.konyubi as "Purchase Date"
, konyu.kazu as "number"
, syohin.syohinmei as "Product Name"
FROM
 konyu
, syohin
WHERE
 konyu.konyubi = xxx

How about putting the following SQL in the xxx part?

Year ([SalesDate]) * 12 + DatePart ("m", [SalesDate]) = Year (Date ()) * 12 + DatePart ("m", Date ())-1

(↑ is the SQL found on the net)

//////////////////////////////////////////////// /////////

sql
  • Answer # 1

    What if the purchase date is DATE type?

    SELECT
     konyu.konyubi as "Purchase Date"
    , konyu.kazu as "number"
    , syohin.syohinmei as "Product Name"
    FROM
     konyubi
    , syohin
    WHERE
     konyu.konyubi>= trunc (add_months (sysdate, -1), 'MM')
     and konyu.konyubi<trunc (sysdate, 'MM')


    --- Addendum ---
    That!
    Are there two tables?
    Since you don't know the join conditions, please do it well.

  • Answer # 2

    How to narrow down product names is not explained, but if you guess the line after WHERE with the product code of the common key as shohincode,

    WHERE
      konyu.shohincode = syohin.shohincode
      AND konyu.konyubi>20170100
      AND konyu.konyubi<20170200


    It will be the condition specification like.
    Although it is more efficient to use the RIGHT JOIN clause to narrow down product names by product code, it is more difficult.

  • Answer # 3

    Oracle stores DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE types for Oracle,
    It can be NUMBER (8) or VARCHAR2 (8).
    In the following, we will proceed on the assumption of DATE type and TIMESTAMP type.

    There are CURRENT_DATE and CURRENT_TIMESTAMP as functions to find today's date,
    ROUND (ADD_MONTHS (CURRENT_DATE, -1),'MONTH')on these
    For the time being, you can get last month,
    When narrowing down by WHERE clause, it means "From the first day of the previous month to the end of the previous month",
    TRUNC (konyubi,'MONTH') = TRUNC (ADD_MONTHS (CURRENT_DATE, -1),'MONTH')
    There is also a hand to do, but it seems to be slow because of the operation for all records,
    If konyubi is TIMESTAMP type
    konyubi>= TRUNC (ADD_MONTHS (CURRENT_DATE, -1),'MONTH') AND konyubi<TRUNC (CURRENT_DATE,'MONTH')
    DATE type
    konyubi>= TRUNC (ADD_MONTHS (CURRENT_DATE, -1),'MONTH') AND konyubi<= LAST_DAY (TRUNC (ADD_MONTHS (CURRENT_DATE, -1),'MONTH'))< br /> Is n’t it?

    (Since it's usually PostgreSQL, I'm sorry if there was a mistake.)

  • Answer # 4

    There are already many answers from other people, so just a little.

    ALTER SESSION SET NLS_DATE_FORMAT ='YYYY/MM/DD HH24: MI: SS';

    SELECT ADD_MONTHS (TRUNC (SYSDATE,'MM'), -1) AS FROM1
    , LAST_DAY (ADD_MONTHS (TO_DATE (TO_CHAR (SYSDATE,'YYYYMMDD') ||'235959&apos ;,'YYYYMMDDHH24MISS'), -1)) AS TO1
    FROM DUAL;

    FROM1 TO1
    ------------------- -------------------
    2017/01/01 00:00:00 2017/01/31 23:59:59

    Use this result for BETWEEN.

  • Answer # 5

    SELECT
    konyu.konyubi as"Purchase Date"
    , konyu.kazu as"number"
    , syohin.syohinmei as"Product Name"
    FROM
    konyu
    , syohin
    WHERE
    konyu.konyubi like to_char (add_months (sysdate, -1),'yyyymm') ||'%'
    and konyu.code = syohin.code
    ;
    If sysdate is"20170202"and
    konyu.konyubi has a string of"20170102"etc.
    "201701 "+"%"Try searching with LIKE?

    Also, "Product name" is in the syohin table,
    Maybe tied to the konyu (purchase) table with a code, syohin (product) table
    If there is always, there's Outer/LeftJoin if there isn't