MySQL JOIN使用子字符串作为计数器

Question

I have two tables as follows:

Table xyz

id    url
--    -------------
1     someweb.com/1
2     example.com
3     abc.com

Table abc

id    url
--    -----------
1     example.com

And I am expecting output as:

id    url            count
--    -----------    -----
1     example.com    2
2     abc            1

And I execute this query:

SELECT DISTINCT
    xyz.url,
    COUNT(xyz.url) as count
from      abc
LEFT JOIN xyz
on        abc.id = substring_index(xyz.url, '/someweb.com/', -1)

as some links are sitting in abc table which is linked as a string of xyz.

Any idea how to do this?

Answer 1

try this

SELECT
    count(*) AS count,
    u.url
FROM
    (
        SELECT
            *
        FROM
            xyz
        UNION ALL
            SELECT
                *
            FROM
                abc
    ) AS u
GROUP BY
    u.url