从MYSQL中的2个表中查询

问题描述：

I have two tables in MySQL:

Table 1

Week    From         Until
1    2015-04-01    2015-04-07
2    2015-04-08    2015-04-14
3    2015-04-15    2015-04-21
4    2015-04-22    2015-04-28

Table 2

Input_Date        Code
2015-04-10        123
2015-04-22        456
2015-04-25        123
2015-04-26        123

I used this query to select the current week based on the current date:

SELECT Week FROM table_1 WHERE (NOW() BETWEEN From AND Until)
I need to select the "code" and count it from table 2 where "Code" = 123 and "Input_Date" corresponds to the current "Week".

*If the current date is 2015-04-23, the "Week" would be = 4

The result would be:

Week   Code  Count
 4     123     2

我在MySQL中有两个表： p>

表1 p>

  Week From Until 
1 2015-04-01 2015-04-07 
2 2015-04-08 2015-04-14 
3 2015-04-15 2015-04-21  n4 2015-04-22 2015-04-28 
  code>  pre> 
 
 表2  p> 
 
 
  Input_Date Code 
2015-04  -12 >> 使用此查询根据当前日期选择当前周： p> 
 
  SELECT  Week  code> FROM  table_1  code> WHERE（NOW（）BETWEEN 从 code> AND 直到 code>） p>  li> 
 
 我需要选择“代码”并从表2中计算“代码”  “= 123和”Input_Date“对应当前的”周“。 p>  li> 
  ol> 
 
 
 *如果当前日期是2015-04-23，则” 周“将是= 4  p> 
 
 
结果将是： p> 
 
 
 周代码计数
 4 123 2 
  code>   pre> 
  div>

答

Try-

SELECT t1.Week, t2.Code, COUNT(*) 
FROM table_1 t1 
LEFT JOIN table_2 t2
ON t2.Input_Date BETWEEN t1.From AND t1.Until
WHERE (NOW() BETWEEN t1.From AND t1.Until) 
AND t2.Code = 123

http://sqlfiddle.com/#!9/3d2a6/16

答

Alternate way of doing the same thing without LEFT JOIN

SELECT a.week, b.code, count(a.week) 
FROM table_1 a, table_2 b
WHERE (b.input_date BETWEEN a.from AND a.until)
AND (NOW() BETWEEN a.From AND a.Until)
AND b.code = 123

从MYSQL中的2个表中查询

相关推荐