英文:
Using boolean expression outside of a where/having clause
问题
SQL Server returns the result rowlett@bikes.shop for the expression email=rowlett@bikes.shop because it is treating the expression as a comparison, not a mathematical operation. In SQL, when you use the = operator in a SELECT statement, it is used for comparison, not for mathematical equality.
So, when you write email=rowlett@bikes.shop, SQL Server interprets it as "Is the email column equal to the string 'rowlett@bikes.shop'?" Since it's a comparison, it returns the value of the email column for each row where the condition is true, which in this case is just 'rowlett@bikes.shop'.
SQL Server doesn't convert it to something like 1 because it doesn't have a boolean type, and it treats the result as a string literal that matches the condition you specified.
英文:
I have the following data:
store_id store_name phone email street city state zip_code1 Santa Cruz Bikes (831) 476-4321 santacruz@bikes.shop 3700 Portola Drive Santa Cruz CA 950602 Baldwin Bikes (516) 379-8888 baldwin@bikes.shop 4200 Chestnut Lane Baldwin NY 114323 Rowlett Bikes (972) 530-5555 rowlett@bikes.shop 8000 Fairway Avenue Rowlett TX 75088
If I write a query such as:
select * from sales.stores where email='rowlett@bikes.shop'-- store_id store_name phone email street city state zip_code-- 3 Rowlett Bikes (972) 530-5555 rowlett@bikes.shop 8000 Fairway Avenue Rowlett TX 75088
I get the expected result with one row. However, if I use the exact same expression in the select list, I get what seems like gibberish:
SELECT email='rowlett@bikes.shop' from sales.stores where email='rowlett@bikes.shop'-- rowlett@bikes.shop
Why does SQL Server return the result rowlett@bikes.shop for the expression email=rowlett@bikes.shop ? I understand it doesn't have the boolean type, but I'd think it would be converted to something like 1. Why does that occur?
答案1
得分: 2
根据@Martin Smith在评论中指出的，无论您使用：
SELECT email = 'rowlett@bikes.shop' ...
还是
SELECT 'rowlett@bikes.shop' AS email ...
您都会得到相同的查询结果 - 即，在您的查询结果集中将会有一个名为'email'的列，每一行的值都是'rowlett@bikes.shop'。
|email |column2|column3|-------------------- ------- -------|'rowlett@bikes.shop'|value1 |another value1||'rowlett@bikes.shop'|value2 |another value2|等等。
如果您想进一步回答您的问题（假设您实际上希望在查询结果中返回一个真/假值），那么您可以使用CASE表达式来实现 - 例如：
SELECTCONVERT(bit,CASEWHEN email = 'rowlett@bikes.shop'THEN 1ELSE 0END)FROM sales.stores;
并扩展到查看一些潜在的结果：
SELECTemail,CONVERT(bit,CASEWHEN email = 'rowlett@bikes.shop'THEN 1ELSE 0END) AS EmailCheckFROM sales.stores;
您将会看到类似以下的结果：
|email |EmailCheck ||'rowlett@bikes.shop' |1 ||'someone@xyz.com' |0 ||'someoneelse@xyz.com'|0 |
英文:
As @Martin Smith pointed out in the comments, whether you use:
SELECT email = 'rowlett@bikes.shop' ...
or
SELECT 'rowlett@bikes.shop' AS email ...
you'll get the same query result - that is, you'll get a column named 'email' in your query resultset, with 'rowlett@bikes.shop' as the value for each row.
|email |column2|column3|-------------------- ------- -------|'rowlett@bikes.shop'|value1 |another value1||'rowlett@bikes.shop'|value2 |another value2|etc.
To go one step further beyond your question (making the assumption that you actually do want to have a true/false value returned in your query results), then you can use a CASE expression to achieve that - eg.
SELECTCONVERT(bit,CASEWHEN email = 'rowlett@bikes.shop'THEN 1ELSE 0END)FROM sales.stores;
And expanding that to see some potential results:
SELECTemail,CONVERT(bit,CASEWHEN email = 'rowlett@bikes.shop'THEN 1ELSE 0END) AS EmailCheckFROM sales.stores;
you'd see something like:
|email |EmailCheck ||'rowlett@bikes.shop' |1 ||'someone@xyz.com' |0 ||'someoneelse@xyz.com'|0 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论