mysql - 具有多个 where 子句和日期之间的 Oracle Sql 合并表和 Day week month

Question

I have two tables mentioned below  

   Table 1
Pk     Type       EVENTID    TimeStamp                                
1      Audio1          1     11-JAN-15 12.33.49.000000000 AM           
2      Video1          1     11-JAN-15 12.33.49.000000000 AM           
3      Audio2          2     11-JAN-15 12.35.50.000000000 AM           
4      Video2          2     11-JAN-15 12.35.50.000000000 AM           
5      Audio3          3     11-JAN-15 12.33.49.000000000 AM           
6      Video3          3     11-JAN-15 12.33.49.000000000 AM           
7      Audio4          4     12-JAN-15 12.40.50.000000000 AM           
8      Video4          4     12-JAN-15 12.40.50.000000000 AM     

Above is my first table.With audio and video files.    

My second table is     
 Table 2
    Pk    EVENTID      TimeStamp                              Date   
    1        1        11-JAN-15 12.33.49.000000000 AM          11-JAN-15 
    2        2        11-JAN-15 12.33.49.000000000 AM          11-JAN-15
    3        3        11-JAN-15 12.35.50.000000000 AM          11-JAN-15
    4        4        12-JAN-15 12.35.50.000000000 AM          12-JAN-15      


I am looking for an output mentioned in the table below.    


Table 3
    Type        Type      EventId           TimeStamp                                Date
    Audio1     Video1      1            11-JAN-15 12.33.49.000000000 AM           11-JAN-15
    Audio2     Video2      2            11-JAN-15 12.33.50.000000000 AM           11-JAN-15
    Audio3     Video3      3            11-JAN-15 12.35.50.000000000 AM           11-JAN-15
    Audio4     Video4      4            11-JAN-15 12.40.50.000000000 AM           12-JAN-15

EventID 在两个表之间是唯一的。我想根据表 2 中的日期获取结果，即表 2。Date1 和 Date2 之间的日期以及日、周和月。是否有可能获得此结果。如果有的话，请帮助我。

Thanks 
Shyam

Answer 1

仔细检查后，您的查询比看起来更简单，因为 TimeStamp 列对于给定 EVENTID 的音频和视频记录是相同的。因此，您真正需要做的就是通过WHERE 限制将两个表JOIN 并添加一个新的Type 列。

SELECT t1.Type AS Type, CONCAT('Video', t1.EVENTID) AS Type, t1.EVENTID AS EventId,
    t1.TimeStamp AS TimeStamp, t2.Date AS Date
FROM Table1 t1 INNER JOIN Table2 t2
ON t1.Pk = t2.Pk
WHERE SUBSTRING(t1.Type, 1, 5) = 'Audio' AND
    t2.Date >= '2015-01-11' AND t2.Date <= '2015-01-12'