Mysql 子查询 - 需要在两个类似查询之间建立连接，以便计算邮政编码和邮政编码的出现次数

发布于 2024-11-19 04:09:50 字数 708 浏览 4 评论 0原文

我在解决这个问题时遇到了一些问题。我有两个查询需要放在一起，无论我尝试什么，我总是会遇到 mysql 语法错误。这两个查询如下。

查询 1 - 这是针对加拿大邮政编码的查询

SELECT ta.city, ta.email_address, ta.country_code, COUNT(*)
FROM jos_form_submitteddata_form1 tb
JOIN jos_postalzip_redirect ta ON UPPER(LEFT(tb.FIELD_24, 3)) = LEFT(ta.postal_zip, 3)
WHERE LENGTH(tb.FIELD_24) > 5
GROUP BY ta.city;

查询 2 - 这是针对美国邮政编码的查询

SELECT ta.city, ta.email_address, ta.country_code, COUNT(*)
FROM jos_form_submitteddata_form1 tb
JOIN jos_postalzip_redirect ta ON UPPER(tb.FIELD_24) = LEFT(ta.postal_zip, 5)
WHERE LENGTH(tb.FIELD_24) <= 5
GROUP BY ta.city
HAVING ta.country_code = 'US';

目标是让这两个查询以正确的计数显示在同一个表中

原文

I'm having a bit of an issue figuring this out. I have two queries that I need to put together and no matter what I've tried I always end up with an error in mysql syntax. The two queries are below.

Query 1 - this is for canadian postal codes

SELECT ta.city, ta.email_address, ta.country_code, COUNT(*)
FROM jos_form_submitteddata_form1 tb
JOIN jos_postalzip_redirect ta ON UPPER(LEFT(tb.FIELD_24, 3)) = LEFT(ta.postal_zip, 3)
WHERE LENGTH(tb.FIELD_24) > 5
GROUP BY ta.city;

Query 2 - This if for US zip codes

SELECT ta.city, ta.email_address, ta.country_code, COUNT(*)
FROM jos_form_submitteddata_form1 tb
JOIN jos_postalzip_redirect ta ON UPPER(tb.FIELD_24) = LEFT(ta.postal_zip, 5)
WHERE LENGTH(tb.FIELD_24) <= 5
GROUP BY ta.city
HAVING ta.country_code = 'US';

The goal is to have both of these queries display in the same table with correct counts

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

瘫痪情歌 2024-11-26 04:09:50

尝试使用 UNION 语句。

http://dev.mysql.com/doc/refman/5.0/en /union.html

回复收藏 0 原文

哥，最终变帅啦 2024-11-26 04:09:50

UNION 不能满足您的需要？

SELECT ta.city, ta.email_address, ta.country_code, COUNT(*) 
FROM jos_form_submitteddata_form1 tb 
JOIN jos_postalzip_redirect ta ON UPPER(LEFT(tb.FIELD_24, 3)) = LEFT(ta.postal_zip, 3)
WHERE LENGTH(tb.FIELD_24) > 5 GROUP BY ta.city
UNION
SELECT ta.city, ta.email_address, ta.country_code, COUNT(*) 
FROM jos_form_submitteddata_form1 tb 
JOIN jos_postalzip_redirect ta ON UPPER(tb.FIELD_24) = LEFT(ta.postal_zip, 5) 
WHERE LENGTH(tb.FIELD_24) <= 5 AND ta.country_code = 'US' GROUP BY ta.city

A UNION doesn't do what you need?

SELECT ta.city, ta.email_address, ta.country_code, COUNT(*) 
FROM jos_form_submitteddata_form1 tb 
JOIN jos_postalzip_redirect ta ON UPPER(LEFT(tb.FIELD_24, 3)) = LEFT(ta.postal_zip, 3)
WHERE LENGTH(tb.FIELD_24) > 5 GROUP BY ta.city
UNION
SELECT ta.city, ta.email_address, ta.country_code, COUNT(*) 
FROM jos_form_submitteddata_form1 tb 
JOIN jos_postalzip_redirect ta ON UPPER(tb.FIELD_24) = LEFT(ta.postal_zip, 5) 
WHERE LENGTH(tb.FIELD_24) <= 5 AND ta.country_code = 'US' GROUP BY ta.city

回复收藏 0 原文

~没有更多了~