我有一个从JSON文档获取数据的查询,检查返回了哪些值,然后返回该计数。
SELECT
(CASE WHEN cat_name IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN dog_name IS NOT NULL THEN 1 ELSE 0 END
) AS cat_dog_total
from (select JSON_EXTRACT_SCALAR(data, '$.cat.name') as cat_name
JSON_EXTRACT_SCALAR(data, '$.dog.name') as dog_name
from table
)作为此查询的一部分,我还希望返回cat_name、dog_name并添加一个WHERE pet_store = 'london'
我不确定我应该在哪里添加其他参数,或者我是否应该连接子查询?
发布于 2020-07-02 01:23:34
SELECT cat_name, dog_name,
(CASE WHEN cat_name IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN dog_name IS NOT NULL THEN 1 ELSE 0 END
) AS cat_dog_total
from (select JSON_EXTRACT_SCALAR(data, '$.cat.name') as cat_name
JSON_EXTRACT_SCALAR(data, '$.dog.name') as dog_name
from table
WHERE pet_store = 'london'
)https://stackoverflow.com/questions/62682180
复制相似问题