我正在尝试将同一表中的3个电子邮件列合并为1个大电子邮件列,我可以使用以下SQL进行此操作:
SELECT email
FROM (
SELECT email
FROM accounts
UNION
SELECT email2
FROM accounts
UNION
SELECT email3
FROM accounts
)accounts WHERE email LIKE '%@%'
但是我也希望只能将来自同一表(帐户组)的不同列=的行返回到特定值。例如,我认为可行:
SELECT email, accountgroup
FROM (
SELECT email, accountgroup
FROM accounts
UNION
SELECT email2, accountgroup
FROM accounts
UNION
SELECT email3, accountgroup
FROM accounts
)accounts
WHERE email LIKE '%@%'
AND accountgroup = 'Vyrav'
第二条语句无法按预期工作,仅返回1行。我认为UNION正在合并帐户组列值相同(将是所有行)的行。
如果我修改第一条语句以使最后一行像这样:
)accounts WHERE email LIKE '%@%' AND accountgroup='Vyrav'
它说在“ where子句”中有一个“未知列” accountgroup”
有人可以建议我在这里做错什么,或者我应该做些什么吗?
在此先感谢您的帮助。
试试这个:在这里工作小提琴
SELECT
EMAIL,
ACCOUNTGROUP
FROM
(SELECT
EMAIL AS EMAIL,
ACCOUNTGROUP
FROM
ACCOUNTS
UNION ALL
SELECT
EMAIL2 AS EMAIL,
ACCOUNTGROUP
FROM
ACCOUNTS
UNION ALL
SELECT
EMAIL3 AS EMAIL,
ACCOUNTGROUP
FROM
ACCOUNTS) ACCOUNT
WHERE
EMAIL LIKE '%@%'
AND ACCOUNTGROUP = 'Vyrav';
结果:
EMAIL ACCOUNTGROUP
------------------------- -------------------------
[email protected] Vyrav
[email protected] Vyrav
[email protected] Vyrav
3 rows selected.
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句