获取连接表数据库 [英] Get join tables database

问题描述

我有2张桌子：

I have 2 tables:

Table 1:
+-----+------------+---------------------+
| id  | first_name | created_at          |
+-----+------------+---------------------+
| 522 | dddd       | 2013-04-11 18:44:07 |
| 523 | cccc       | 2013-03-01 06:19:12 |
| 524 | ffff       | 2013-03-06 21:07:06 |
| 525 | rrrr       | 2013-03-06 22:05:08 |
| 590 | tttt       | 2013-03-16 16:39:59 |
| 584 | yyyy       | 2013-03-22 14:46:28 |

Table 2:
+-----+---------------------+---------------------+
| id  | referred_by_user_id | created_at          |
+-----+---------------------+---------------------+
| 298 |                 522 | 2013-02-22 22:19:06 |
| 299 |                 584 | 2013-03-22 18:27:49 |
| 300 |                 584 | 2013-03-22 18:48:23 |
| 301 |                 584 | 2013-03-22 19:01:40 |
| 302 |                 584 | 2013-03-22 19:05:24 |
| 303 |                 584 | 2013-03-22 19:13:18 |
| 304 |                 590 | 2013-04-10 23:24:17 |
| 305 |                 522 | 2013-04-11 18:44:07 |

有没有办法让表1中的所有用户ID与表2相匹配来自日期的refer_by_user_id？

Is there a way to get all users id from table 1 to match table 2 referred_by_user_id from a date?

Example: from 2013-03-06 to 2013-03-22
+---------------------+------
| referred_by_user_id |  got
+---------------------+------
|                 524 |  0
|                 525 |  0
|                 590 |  1
|                 584 |  4

检查第一张表的日期而不是第二张表。

Check date from first table not the second table.

推荐答案

要从第一个表中获取所有记录，您需要使用 LEFT JOIN 语句。

To get all records from first table you need to use LEFT JOIN statement.

SELECT t1.id AS referred_by_user_id, COUNT(t2.referred_by_user_id) AS [got]
FROM Table1 AS t1 LEFT JOIN Table2 AS t2 ON t1.id = t2.referred_by_user_id
GROUP BY t1.id

从第一张桌子检查日期而不是第二张桌子。 - 我没收到;（

这是否意味着您只想获得那些 referenced_by_user_id ''sf rom first table哪个日期等于特定 id 的第二个表？当你解释它时，我会改进我的答案。





对不起，我忘记了分组选项; （



现在，结果：

Check date from first table not the second table. - i don''t get it ;(
Does it mean you want to get only those referred_by_user_id''s from first table which date is equal to second table for that particular id? When you explain it, i''ll improve my answer.


Sorry, i forgot about grouping options ;(

Now, result:

ref. got
522 2
523 0
524 0
525 0
584 5
590 1

SELECT t1.id，t2.created_at FROM Table1 t1

INNER JOIN Table2 t2 ON t2.referred_by_user_id = t1.id

WHERE t2.created_at =' '2013-03-22'' - <（或您想在此处检索的任何日期） -

SELECT t1.id, t2.created_at FROM Table1 t1
INNER JOIN Table2 t2 ON t2.referred_by_user_id = t1.id
WHERE t2.created_at = ''2013-03-22'' --<(or whatever date you want to retrieve here)--

这篇关于获取连接表数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！