开发者

MySQL, merging 2 or more tables before execute SELECT DISTINCT query?

I want to calculate how many unique logins from 2 (or probably more tables).

I tried this:

SELECT count(distinct(l1.user_id)) 
FROM `log_1` l1 
LEFT JOIN `log_2` l2 
ON l1.userid = l2.userid;

But it gives me result of l1. If I didn't put l1 on li.userid (distinct), it said "ambiguous".

How do I combine the table, and then select unique login of the combined table?

EDIT:

Tested: I test t开发者_StackOverflow社区he count(distinct(l1.userid)) and count(distinct(l2.userid)). It gives me different result


If you are using LEFT JOIN then you will get at least one row in the combined result for each row in l1, so the join is entirely unnecessary if you just want a distinct count. This would give you the same result as your query:

SELECT count(distinct(l1.user_id)) 
FROM `log_1` l1 

Perhaps you want an INNER JOIN or UNION instead? A UNION will count a user if they appear in either table. An INNER JOIN will count them only if they appear in both tables. Here's an example of the UNION:

SELECT count(*) FROM (
    SELECT distinct(user_id) FROM `log_1`
    UNION
    SELECT distinct(user_id) FROM `log_2`
) T1
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜