我正在执行UNION
,同时从两个不同的表(adv和pub(获取相同类型的信息(company_name、增值税和电子邮件(。SQL 查询如下所示:
SELECT TRIM(UPPER(company_name)), vat, company_owner_email FROM (
(SELECT company_name, vat, admin.email as company_owner_email FROM pub
LEFT JOIN admin ON pub.manager = admin.id
WHERE company_name > '')
UNION
(SELECT company_name, vat, admin.email as company_owner_email FROM adv
LEFT JOIN admin ON adv.manager = admin.id
WHERE company_name > '')
) company
GROUP BY company_name
ORDER BY company_name;
但是,当我运行此查询时,我得到了一些具有相同company_name的行,当我尝试复制它们以了解为什么它们没有被过滤时,我面临以下结果。
'1DNAF SARL', '123456', NULL
'1DNAF SARL ', '', NULL
即使我正在使用
TRIM()
函数,它也以某种方式不起作用,因此,GROUP BY
不认为这些记录相同,也不会消除其中一个。我应该如何解决这个问题?
顺便说一句,当我尝试单独 TRIM(( company_name 字段时,它在两个字段中都有效,但连接结果无法正常工作。
尝试使用Group by TRIM(UPPER(company_name))
最好在 SELECT(如何显示(和 GROUP(如何分组(中以不同的方式规范公司名称:
考虑到可能存在中间有2 个空格或字符串末尾有 TAB 空格制表符的情况。所以我宁愿使用 replace 而不是 TRIM((。
SELECT UPPER(company_name), vat, company_owner_email FROM (
(SELECT company_name, vat, admin.email as company_owner_email FROM pub
LEFT JOIN admin ON pub.manager = admin.id
WHERE company_name > '')
UNION
(SELECT company_name, vat, admin.email as company_owner_email FROM adv
LEFT JOIN admin ON adv.manager = admin.id
WHERE company_name > '')
) company
GROUP BY replace(replace(replace(replace(company_name,'t',''), ' ', ''), '.', ''), ',','')
ORDER BY company_name;
这是因为在分组后应用了 TRIM 和 UPPER。 尝试将函数移动到较低级别:
SELECT company_name, vat, company_owner_email FROM (
(SELECT TRIM(UPPER(company_name)) as company_name, vat, admin.email as company_owner_email FROM pub
LEFT JOIN admin ON pub.manager = admin.id
WHERE company_name > '')
UNION
(SELECT TRIM(UPPER(company_name)) as company_name, vat, admin.email as company_owner_email FROM adv
LEFT JOIN admin ON adv.manager = admin.id
WHERE company_name > '')
) company
GROUP BY company_name
ORDER BY company_name;
不过要小心结果。如果两个表中的增值税值不同,MySQL 不会抛出错误!它只会给你一个或另一个。
试试这个:
SELECT TRIM(CHAR(9) FROM TRIM(UPPER(company_name))) as cpm, vat, company_owner_email FROM (
(SELECT company_name, vat, admin.email as company_owner_email FROM pub
LEFT JOIN admin ON pub.manager = admin.id
WHERE company_name > '')
UNION
(SELECT company_name, vat, admin.email as company_owner_email FROM adv
LEFT JOIN admin ON adv.manager = admin.id
WHERE company_name > '')
) company
GROUP BY TRIM(CHAR(9) FROM TRIM(UPPER(company_name)))
ORDER BY cpm;
这将在修剪空格后修剪掉制表符。很可能您在列中有制表符以及空格。
SELECT TRIM(UPPER(company_name)), vat, company_owner_email FROM (
(SELECT company_name, vat, admin.email as company_owner_email FROM pub
LEFT JOIN admin ON pub.manager = admin.id
WHERE company_name > '')
UNION
(SELECT company_name, vat, admin.email as company_owner_email FROM adv
LEFT JOIN admin ON adv.manager = admin.id
WHERE company_name > '')
) company
GROUP BY UPPER(replace(company_name,' ',''))
ORDER BY company_name;
我尝试过下面的查询。
select replace('1DNAF SARL ',' ','') ='1DNAFSARL';
它正在返回1
.这意味着它将两个记录视为相同。
尝试上面的查询。
试试这个:
SELECT TRIM(UPPER(company_name)), vat, company_owner_email FROM (
(SELECT company_name, vat, admin.email as company_owner_email FROM pub
LEFT JOIN admin ON pub.manager = admin.id
WHERE company_name > '')
UNION
(SELECT company_name, vat, admin.email as company_owner_email FROM adv
LEFT JOIN admin ON adv.manager = admin.id
WHERE company_name > '')
) company
GROUP BY TRIM(UPPER(company_name))
ORDER BY TRIM(UPPER(company_name));