blocks|key|3762747|text|用以下一种取代你的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3762748|group+by+t1.`has_company_phone`,+t2.`phone`
HAVING+CASE+
++++WHEN+t1.`has_company_phone`+=+1++THEN+t1.`company_phone`
++++ELSE+t2.`phone`
END+is+not+null|code-block|syntax|javascript|3762749|小心别错过这个小组。|3762750|或者，您可以在where中使用该条件而不使用group。|3762751|where+CASE+
++++WHEN+t1.`has_company_phone`+=+1++THEN+t1.`company_phone`
++++ELSE+t2.`phone`
END+is+not+null|3762752|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|S|8|@]|9|@]|A|$E|F]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

replace you having by the following one:

<pre><code>group by t1.`has_company_phone`, t2.`phone`
HAVING CASE 
 WHEN t1.`has_company_phone` = 1 THEN t1.`company_phone`
 ELSE t2.`phone`
END is not null
</code></pre>

and be careful to not miss the group by.

or, you can use the condition in the where without group by.

<pre><code>where CASE 
 WHEN t1.`has_company_phone` = 1 THEN t1.`company_phone`
 ELSE t2.`phone`
END is not null
</code></pre>

blocks|key|3762779|text|在MySQL中，will子句总是从select列表中搜索所需的项。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3762780|一旦在“选择”列表中找到，将使用找到的项。|3762781|如果找不到，则抛出错误。|3762782|更新：|3762783|实际上，在您的SQL中，应该用WHERE替换WHERE，因为它与WHERE相同。用什么地方，混乱就会消失。WHERE子句将始终使用实表列，而不是别名。|3762784|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|N|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|O|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

In MySQL, HAVING clause will always search the needed item from the select list. 

Once found in the select list, the found item will be used. 

If Not found, error is thrown.

<h1>Update:</h1>

Actually, in ur SQL, you should replace HAVING with WHERE, since it is the same as WHERE.
Using WHERE, the confusion will be gone. WHERE clause will always use real table column, not the alias.

blocks|key|763727|text|在这种情况下，我接受了影子的建议，并建立了一个测试，看看会发生什么。我用一个空电话填充了t2，并在t1中放了一个电话。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|763728|评估别名是因为来自t1的记录包含在我的结果中。|763729|然后，我反转了值，使t1.company_phone为NULL，t2.phone有一个值，并重新运行查询。没有评估t2.phone字段名，因为在反转数据后没有显示结果。|763730|Boody的建议可能是获得所需功能的更明确的方式，但在我的例子中，我有大约20个字段将被合并到多个案例和4个表中，所以查询会变得非常混乱。|763731|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

In this case, I took Shadow's advice and constructed a test to see what would happen. I filled t2 with a NULL phone and put a phone in t1.

The alias is evaluated because the record from t1 was included in my results. 

I then reversed the values so t1.company_phone was NULL and t2.phone had a value, and reran query. The t2.phone field name was not evaluated as no results showed up after reversing the data.

Boody's advice is probably a more explicit way of getting the functionality desired, but in my case I have about 20fields that will be merged over multiple cases and 4 tables, so the query would get really messy being that explicit.

blocks|key|3762825|text|对于工会，您只需执行以下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3762826|select+distinct+id,++case+when+phone1+=+''+then+phone2+else+phone1+end+as+phone+from
(select+matched_company_id+as+id,+company_phone+as+phone1,+''+as+phone2+from+`input_table1`
union+
select+id+as+id,+''+as+phone1,+phone+as+phone2+from+`input_table2`)+as+t
where+phone+<>+''|code-block|syntax|javascript|3762827|然后，您可以类似地添加任何其他表或字段。|3762828|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

with union, you will simply do the following:

<pre><code>select distinct id, case when phone1 = '' then phone2 else phone1 end as phone from
(select matched_company_id as id, company_phone as phone1, '' as phone2 from `input_table1`
union 
select id as id, '' as phone1, phone as phone2 from `input_table2`) as t
where phone &lt;&gt; ''
</code></pre>

then you can add any other table or field similarly.

I have a mysql query that is joining phone numbers from multiple tables that I can not edit and I need to create an output with the same field name as one of the tables. I'm using a CASE statement to do this. Here is a simple version of my query:

<pre><code>SELECT t1.`id` as id,
CASE 
 WHEN t1.`has_company_phone` = 1 THEN t1.`company_phone`
 ELSE t2.`phone`
END AS `phone`
FROM `input_table1` t1, 
LEFT OUTER JOIN `input_table2` t2
 ON (t2.`id` = t1.`matched_company_id`)
HAVING phone IS NOT NULL;
</code></pre>

EDIT:
In this query, in the HAVING clause is t2.phone being evaluated, the alias phone, or both? My goal is to have the alias evaluated in this having clause.

EG: t1.company_phone exists, but t2.phone IS NULL. I want to include this record using the t1.company_phone and have the alias name be <code>phone</code>. If I change the HAVING to a WHERE clause, this record will not show up.

MySQL Alias and Field have same name - Which is evaluated in Having clause?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一个mysql查询，它连接多个无法编辑的表的电话号码，我需要创建一个与其中一个表具有相同字段名的输出。我用一个案例陈述来做这件事。下面是我的查询的一个简单版本：SELECT t1.`id` as id,CASE     WHEN t1.`has_company_phone` = 1  THEN t1.`compan...

问MySQL别名和字段具有相同的名称-在Which子句中计算的名称？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL别名和字段具有相同的名称-在Which子句中计算的名称？EN