Mysql查询从dns域名中提取tld

Question

在本练习中，我想根据下表从 TLD(顶级域)中提取域名。

Table name: dns
+---------------------------+
| dnsdomain                 |
+---------------------------+
| ns2.hosting.indo.net.id.  |
| ns1.onepanel.indo.net.id. |
| ns-1591.awsdns-06.co.uk.  |
| mail189.atl21.rsgsv.net.  |
| gli.websitewelcome.com.   |
| ns2.metrolink.pl.         |
| ns1.metrolink.pl.         |
| ns-1591.awsdns-06.co.uk.  |
| NS3.METRORED.HN.          |
| NS.METRORED.HN.           |
| ns2.hosting.indo.net.id.  |
| ns1.onepanel.indo.net.id. |
| www.csis.ul.ie.           |
+---------------------------+
and 
Table name: tld
+----------+
| tld      |
+----------+
| .net.id. |
| .co.uk.  |
| .net.    |
| .com.    |
| .pl.     |
| .uk.     |
| .hn.     |
| .id.     |
| .ie.     |
+----------+

我想打印出 dnstomain 及其相关的顶级域名。我执行以下 mysql 查询:

select test.dnsdomain , tld.tld from test join tld where locate(tld.tld, test.dnsdomain, length(test.dnsdomain) - length (tld.tld) )!= 0; 

得到下表:

+---------------------------+----------+
| dnsdomain                 | tld      |
+---------------------------+----------+
| ns2.hosting.indo.net.id.  | .net.id. |
| ns1.onepanel.indo.net.id. | .net.id. |
| ns-1591.awsdns-06.co.uk.  | .co.uk.  |
| mail189.atl21.rsgsv.net.  | .net.    |
| gli.websitewelcome.com.   | .com.    |
| ns2.metrolink.pl.         | .pl.     |
| ns1.metrolink.pl.         | .pl.     |
| ns-1591.awsdns-06.co.uk.  | .uk.     |
| NS3.METRORED.HN.          | .hn.     |
| NS.METRORED.HN.           | .hn.     |
| ns2.hosting.indo.net.id.  | .id.     |
| ns1.onepanel.indo.net.id. | .id.     |
| www.csis.ul.ie.           | .ie.     |
+---------------------------+----------+

我的查询的问题是，对于表“test”中的每条记录，它不会检查表“tld”中的所有 tld，这就是我看到类似内容的原因:

| ns-1591.awsdns-06.co.uk.  | .uk.     |

预期的结果是这样的:

| ns-1591.awsdns-06.co.uk.  | .co.uk.  |

我做错了什么？

Answer 1

试试 Group By 函数。此语句适用于 mysql :

select test.dnsdomain , tld.tld ,
max(length(tld.tld)) as x
from test 
join tld 
where locate(tld.tld, test.dnsdomain, length(test.dnsdomain) - length (tld.tld) )!= 0; 
group by test.tnsdomain

或

 select test.dnsdomain , max(tld.tld) as tld
    from test 
    join tld 
    where locate(tld.tld, test.dnsdomain, length(test.dnsdomain) - length (tld.tld) )!= 0; 
    group by test.tnsdomain