mysql - sql查询左连接记录加倍

Question

我有 2 个表，我正在使用 join 从这 2 个表中获取公共(public)记录。

我使用了以下查询，但我的问题是我的记录增加了一倍。

查询如下，你能帮我一下吗？

查询#1:(正确输出)

mysql> SELECT DISTINCT
    xId1 AS xId1,
    xArea AS xArea,
    YEAR (
        STR_TO_DATE(
            `xDate`,
            '%d/%m/%Y'
        )
    ) AS xYear
FROM
    doTable1 AA
LEFT JOIN doTable2 BB ON AA.xId1 = BB.xId2
WHERE
    xArea IN ('XX1A')
AND YEAR (
    STR_TO_DATE(
        `xDate`,
        '%d/%m/%Y'
    )
) = 2016;

+-----------+-------+-------+
| xId       | xArea | xYear |
+-----------+-------+-------+
| 91623545  | XX1A  |  2016 |
| 89748942  | XX1A  |  2016 |
| 115956872 | XX1A  |  2016 |
| 103595808 | XX1A  |  2016 |
| 103623873 | XX1A  |  2016 |
| 103623892 | XX1A  |  2016 |
| 103623872 | XX1A  |  2016 |
| 103623894 | XX1A  |  2016 |
| 112072738 | XX1A  |  2016 |
| 112072751 | XX1A  |  2016 |
| 117818773 | XX1A  |  2016 |
| 117818753 | XX1A  |  2016 |
| 117885105 | XX1A  |  2016 |
| 117960040 | XX1A  |  2016 |
| 117885111 | XX1A  |  2016 |
+-----------+-------+-------+

15 rows in set

查询#2:(输出不正确，因为 2016 年我有 18 条记录 - 存在量翻倍 - 而不是 查询 #1 中的 15 条记录):

mysql> SELECT 
    DISTINCT COUNT(*) AS xQQQ,
    xArea AS xArea,
    YEAR (
        STR_TO_DATE(
            xDate,
            '%d/%m/%Y'
        )
    ) AS xYear
FROM
    doTable1 AA
LEFT JOIN doTable2 BB ON AA.xId1 = BB.xId2
WHERE xArea IN ('XX1A')
GROUP BY
    xArea,
    xYear;
+------+-------+-------+
| xQQQ | xArea | xYear |
+------+-------+-------+
|    4 | XX1A  |  2014 |
|   49 | XX1A  |  2015 |
|   18 | XX1A  |  2016 |
+------+-------+-------+
3 rows in set

Answer 1

修改了您的 SQL。如果你想要不同的计数，必须像这样使用

COUNT(DISTINCT xId1 )

请运行看看。

SELECT 
        COUNT(DISTINCT xId1 ) AS xQQQ,
        xArea AS xArea,
        YEAR (
            STR_TO_DATE(
                `DATA PUBBLICAZIONE`,
                '%d/%m/%Y'
            )
        ) AS xYear
    FROM
        doTable1 AA
    LEFT JOIN doTable2 BB ON AA.xId1 = BB.xId2
    WHERE xArea IN ('XX1A')
    GROUP BY
        xArea,
        xYear;