MYSQL:重复表 1 的内容乘以表 2 的内容

Question

我有 2 个表，其中 table1 包含用户名，table2 包含用户 x 做某事的日期。

 table1:               table2:        

|----------|          |-------|----------|
| username |          | User  | date     |
|----------|          |-------|----------|
| user1    |          | user1 | 1.1.2013 |
| user2    |          | user2 | 2.4.2013 |
| user3    |          |-------|----------|
|----------|     

现在我想得到一个列表，显示 table1 中的所有用户乘以 table2 上的行数，还显示日期是否适用于全部或 2 或只有一个用户。

|-------|----------|
| User1 | 1.1.2013 |
| user2 | -        |
| user3 | -        |
| user1 | -        |
| user2 | 2.3.2013 |
| user3 | -        |
|-------|----------|

所以基本上我只是想在从表 2 中读取行时，其他用户是否在同一日期做了一些事情。

如果你有任何想法如何做到这一点，请帮助我:)

---

接下来我需要补充的是几个月 :) 而且应该是这样的

| MM/YYYY |  user  | Kg  ||---------|--------|-----|| 01/2013 | user1  |  0  || 01/2013 | user2  |  0  || 01/2013 | user3  |  5  || 02/2013 | user1  |  2  || 02/2013 | user2  |  4  || 02/2013 | user3  |  7  ||   ...   | ...    | ... || 12/2013 | user1  |  0  || 12/2013 | user2  |  1  || 12/2013 | user3  |  0  ||---------|--------|-----|

我认为对于这个表，我需要在最新更新的查询中使用某种 if 语句。而且我不介意是否有解决方案可以分享，因为我认为我无法很快解决这个问题:)

所以我需要检查，是否有人在 1 月份钓到鱼，如果没有 => 标记为 0如果您有几条鱼，则将平均值或该月的总和标记为有几条鱼的用户。与之前的想法相同，但这允许更好的折线图“段”，因为所有测量点都在同一条垂直线上。

Answer 1

我喜欢这个任务。不要认为这是最佳解决方案，但效果很好:

SELECT un, tmp 
FROM (
    SELECT * 
    FROM (
        SELECT  t1.username as un, 
            t2.user as u, 
            t2.date as d, 
            t2.date as tmp
        FROM table1 as t1, table2 as t2 
        WHERE t1.username = t2.user
        UNION
        SELECT  
            t1.username as un, 
            t2.user as u, 
            t2.date as d, 
            '-' as tmp
        FROM table1 as t1, table2 as t2 
        WHERE t1.username <> t2.user
    ) AS res
    ORDER BY d ASC
) AS tot_res

更新:

SELECT un, tmp 
FROM (
    SELECT * 
    FROM (
        SELECT  t1.username as un, 
            t2.user as u, 
            t2.date as d, 
            t2.date as tmp
        FROM table1 as t1, table2 as t2 
        WHERE t1.username = t2.user
        UNION
        SELECT  
            t1.username as un, 
            t2.user as u, 
            t2.date as d, 
            '-' as tmp
        FROM table1 as t1, table2 as t2 
        WHERE t1.username <> t2.user    
    ) AS res
    GROUP BY res.un, res.d
    ORDER BY d ASC
) AS tot_res