MySQL查询异常求助：点击表与转化表关联查询问题

Hey there, let’s work through your MySQL query problem together. First up—you didn’t share the actual query that’s failing, which makes it a bit tricky to nail down the exact issue right away. But based on the table structures and sample data you provided, I can walk through the most common pitfalls and fixes that might be causing your trouble.

Table Structures & Sample Data #

First, let’s clarify the tables you’re working with (formatted for readability):

Conversion Table #

id | ref id | registered | tempo     | prod_cod
---|--------|------------|-----------|---------
1  | 1      | 04/05/2018 | 15385950  | oggn-1
2  | 1      | 05/05/2018 | 15385950  | oggn-1
3  | 1      | 06/05/2018 | 15385950  | oggn-1

Clicks Table #

id | ref id | registered | tempo     | prod_cod | ip         | user_agent
---|--------|------------|-----------|----------|------------|------------
1  | 1      | 05/05/2018 | 15385950  | oggn-1   | 192.168.1  | Mozilla....
2  | 1      | 06/05/2018 | 1...      |          |            |

Common Issues & Fixes #

1. Missing/Malformed JOIN Conditions #

If you’re joining these two tables, the #1 mistake is skipping a proper ON clause or using incorrect matching columns. For example, if you want to link clicks to conversions by reference ID, product code, and date, your query should look like this:

SELECT 
  c.id AS click_id,
  conv.id AS conversion_id,
  c.registered AS click_date,
  conv.registered AS conversion_date
FROM Clicks c
LEFT JOIN Conversion conv
  ON c.`ref id` = conv.`ref id`
  AND c.prod_cod = conv.prod_cod
  AND STR_TO_DATE(c.registered, '%d/%m/%Y') = STR_TO_DATE(conv.registered, '%d/%m/%Y');

Important note: Your registered column uses dd/mm/yyyy format. Comparing these as strings will give wrong results (MySQL does lexicographical order for strings). Always convert them to proper DATE types with STR_TO_DATE().

2. Unquoted Column Names with Spaces #

Your tables have a column named ref id (with a space). In MySQL, you must wrap columns with spaces in backticks ` every time you reference them. Forgetting this will throw a syntax error immediately.

3. Incomplete/Invalid Data #

Your second Clicks row has truncated tempo data (1...) and missing values for prod_cod, ip, and user_agent. If your query filters on these columns (e.g., WHERE prod_cod IS NOT NULL), this row could cause unexpected results or errors. Either clean up the data, or adjust your query to handle NULLs explicitly.

4. Data Type Mismatches #

Check the data type of your tempo column. If it’s supposed to be numeric (INT/BIGINT) but has non-numeric values like 1..., any arithmetic operations or comparisons on it will fail. Fix the data, or cast it to a compatible type with CAST() or CONVERT().

Next Steps #

To get a precise fix, share the exact query you’re running and the full error message MySQL returns (e.g., syntax error, no rows returned, unexpected results). That will let us zero in on the exact problem.

内容的提问来源于stack exchange，提问作者Giuseppe Gaeta