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.
Extraction condition: I want to acquire purchase data with the purchase date of the previous month starting from this month.
Extraction source: konyu.konyubi, syohin.syohinmei, konyu.kazu
This month: 2017,2
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)
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 ---
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')
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
2017/01/01 00:00:00 2017/01/31 23:59:59
Use this result for BETWEEN.
Answer # 5
konyu.konyubi as"Purchase Date"
, konyu.kazu as"number"
, syohin.syohinmei as"Product Name"
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
- mysql - [sql] i want to get data that is not in one of the tables
- Get form data easily with JS
- sql how to extract "during" data
- Must be SQL statements (c) data insertion
- NET DateTime function to get the start and end time of the previous month
- SQL Server data summary
- Socket, GET, POST data example in PHP
- python - get exchange chart data with api
- Get data of selected row in jqGrid
- C # method to get data from DataTable
- JS date range initialization method to get the previous month date
- php get excel file data
- js get current week, previous week, next week date
- nodejs get data from database
- mysql - i want to get two data side by side from one table with sql
- python - how to get map data by scraping
- python - how to get data from related objects
- python 3x - typeerror: 'method' object is not subscriptable
- python - you may need to restart the kernel to use updated packages error
- xcode - pod install [!] no `podfile 'found in the project directory
- vuejs - [vuetify] unable to locate target [data-app] i want to unit test to avoid warning
- android studio - emulator: dsound: could not initialize about the error message directsoundcapture
- android studio - unresolved reference comes out in kotlin
- mysql startup failed [error] innodb: the innodb_system data file 'ibdata1' must be writable
- django - oserror: [winerror 123] the file name, directory name, or volume label syntax is incorrect : '<frozen importlib_boot
- python - importerror: cannot import name md5 error cannot be resolved