mysql - 如何以 DRY 方式编写 MYSQL 查询

Question

我有一个这样的查询，它查看大量不同的 URL，并按主机名对它们进行分组。它非常丑陋，但似乎足够快，可以使用。

我怎样才能写得更整洁一些？我正在从一系列社交媒体网站生成查询，因此那里可能会有更多网站。

SELECT substring(r.name, 8, locate("/",substring(r.name FROM 8))-1) AS referer_domain,
       count(USER) AS hits,
       r.id
FROM core c,
     referer r
WHERE c.site_url = 12
    AND r.name LIKE '%/%'
    AND c.referer = r.id
    AND (substring(r.name, 8, locate("/",substring(r.name FROM 8))-1) = "www.delicious.com"
    OR substring(r.name, 8, locate("/",substring(r.name FROM 8))-1) = "www.facebook.com"
    OR substring(r.name, 8, locate("/",substring(r.name FROM 8))-1) = "m.facebook.com"
    OR substring(r.name, 8, locate("/",substring(r.name FROM 8))-1) = "www.reddit.com"
    OR substring(r.name, 8, locate("/",substring(r.name FROM 8))-1) = "twitter.com"
    OR substring(r.name, 8, locate("/",substring(r.name FROM 8))-1) = "news.ycombinator.com"
GROUP BY substring(r.name, 8, locate("/",substring(r.name FROM 8))-1)
ORDER BY hits DESC

Answer 1

在您的例子中，您已经创建了一个输出列 referer_domain，您可以为 GROUP BY 引用它。

为了在 WHERE 子句中使用它，尽管您需要一个 View 。

CREATE VIEW ref_domain_view AS SELECT *,substring(name, 8, locate("/",substring(name FROM 8))-1) as referer_domain FROM referer;

SELECT r.referer_domain,
   count(USER) AS hits,
   r.id
FROM core c,
 ref_domain_view r
WHERE c.site_url = 12
AND r.name LIKE '%/%'
AND c.referer = r.id
AND referer_domain = "www.delicious.com"
OR referer_domain = "www.facebook.com"
OR referer_domain = "m.facebook.com"
OR referer_domain = "www.reddit.com"
OR referer_domain = "twitter.com"
OR referer_domain = "news.ycombinator.com"
GROUP BY referer_domain
ORDER BY hits DESC