mysql Sql中的case未按预期运行

vwhgwdsa 于 2023-01-16 发布在 Mysql

关注(0)|答案(2)|浏览(127)

如果我期望SQL理解它应该检查每一行以查看S.PersonID是否为空，这是错误吗？

select OrderQty as 'OrderQty', 
case  
    when (select S.PersonID from Sales.Customer as S) is null then 1 
    else 2 
end as 'CaseResult' 
from sales.SalesOrderDetail

错误：子查询返回了多个值。
如果它是错误的，那么为什么它理解它应该在下面的例子中逐个检查每一行？

select Name,
case ProductCategoryId
   when 1 then 'red'
   when 2 then 'black'
   else 'white
end as 'categoryname'
from production.productcategory

我如何编写第一个代码，使它像第二个代码一样工作？

mysql

来源：https://stackoverflow.com/questions/75113486/case-in-sql-doesnt-run-as-i-expect

2条答案

按热度按时间

fslejnso1#

不能在CASE中使用子查询。此外，也不能在SalesOrderDetail和Customer之间进行连接

select OrderQty as 'OrderQty', 
case  
    when s.Person is null then 1 
    else 2 
end as 'CaseResult' 
from sales.SalesOrderDetail sod
left outer join sales.Customer s on sod.PersonId = s.PersonId

赞(0）回复(0）举报 2023-01-16

eqqqjvef2#

您需要一个相关子查询，以使其正确运行

CREATE TABLe Customer(PersonID Int)

INSERT INTO Customer VALUEs (1)

CREATE tABLe SalesOrderDetail ( OrderQty int, CustomerID int)

INSERT INTO SalesOrderDetail VALUES(2,1),(3,2)

Records: 2  Duplicates: 0  Warnings: 0

select OrderQty as 'OrderQty', 
case  
    when (select C.PersonID from Customer as C WHERE C.PersonID = S.CustomerID ) is null then 1 
    else 2 
end as 'CaseResult' 
from SalesOrderDetail S

| 订单数量|病例结果|
| - ------|- ------|
| 第二章|第二章|
| 三个|1个|
fiddle
如果查询包含多行作为结果集，则需要向子查询添加LIMIT

CREATE TABLe Customer(PersonID Int)

INSERT INTO Customer VALUEs (1)

CREATE tABLe SalesOrderDetail ( OrderQty int, CustomerID int)

INSERT INTO SalesOrderDetail VALUES(2,1),(3,2)

Records: 2  Duplicates: 0  Warnings: 0

select OrderQty as 'OrderQty', 
case  
    when (select C.PersonID from Customer as C WHERE C.PersonID = S.CustomerID ORDER BY C.PersonID LIMIT 1) is null then 1 
    else 2 
end as 'CaseResult' 
from SalesOrderDetail S

| 订单数量|病例结果|
| - ------|- ------|
| 第二章|第二章|
| 三个|1个|
fiddle

赞(0）回复(0）举报 2023-01-16

我来回答

mysql Sql中的case未按预期运行

2条答案

相关问题

热门标签

最新问答