mysql case语句

jv2fixgn 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(462)

我想在视图“v\u file\u details”中添加一个“status”列，根据“v\u file\u details.records\u waiting\u count”和“v\u file\u details.records\u correct\u count”列中的数据，该列将有一个值。但我不明白如何在视图中使用case？

create view v_file_details as
select 
fi.file_importer_id,
fi.file_name,
convert(time(0), fi.file_date) import_time,
fi.error_type,
((SELECT COUNT(*) From reservation Where file_importer_id = fi.file_importer_id AND status in ('S', 'S1')) + 
(SELECT COUNT(*) From error_file_record Where file_importer_id = fi.file_importer_id) +
(SELECT COUNT(*) From startup Where file_importer_id = fi.file_importer_id AND status in ('T', '6'))) as records_correct_count,
((SELECT COUNT(*) From location Where file_importer_id = fi.file_importer_id AND status = 'Y') + 
(SELECT COUNT(*) From reservation Where file_importer_id = fi.file_importer_id AND status = 'N') + 
(SELECT COUNT(*) From startup Where file_importer_id = fi.file_importer_id AND status in ('X', 'X1'))) as records_waiting_count,
case 'status' when records_waiting_count > 0 THEN 'Waiting' ELSE 'ACCEPTED', -- it is the problem, I don't know how to use CASE here
fi.new_file_name,

from file_importer fi
left join startup ts on fi.file_importer_id = ts.file_importer_id 
where ts.file_importer_id is not null

union all

select 
fi.file_importer_id,
fi.file_name,
convert(time(0), fi.file_date) import_time,
fi.error_type,
((SELECT COUNT(*) From reservation Where file_importer_id = fi.file_importer_id AND status in ('S', 'S1')) + 
(SELECT COUNT(*) From error_file_record Where file_importer_id = fi.file_importer_id) +
(SELECT COUNT(*) From startup Where file_importer_id = fi.file_importer_id AND status in ('T', '6'))) as records_correct_count,
((SELECT COUNT(*) From location Where file_importer_id = fi.file_importer_id AND status = 'Y') + 
(SELECT COUNT(*) From reservation Where file_importer_id = fi.file_importer_id AND status = 'N') + 
(SELECT COUNT(*) From startup Where file_importer_id = fi.file_importer_id AND status in ('X', 'X1'))) as records_waiting_count,
case 'status' when records_waiting_count > 0 THEN 'Waiting' ELSE 'ACCEPTED', --here the same
fi.new_file_name,

from file_importer fi
left join reservation res on fi.file_importer_id = res.file_importer_id 
where res.file_importer_id is not null

GO

sql mysql sql-view case union-all

来源：https://stackoverflow.com/questions/52608092/mysql-case-statement-in-a-view-with-union-all

1条答案

按热度按时间

p1iqtdky1#

不能在中使用别名 SELECT ，所以你不能参考 records_waiting_count ，则需要使用它所引用的表达式。另外，您已经有一个名为 status ，所以不能调用 CASE 我们称之为 waiting_status 相反）。所以你的 CASE 表达式应如下所示：

CASE WHEN ((SELECT COUNT(*) From location Where file_importer_id = fi.file_importer_id AND status = 'Y') + 
(SELECT COUNT(*) From reservation Where file_importer_id = fi.file_importer_id AND status = 'N') + 
(SELECT COUNT(*) From startup Where file_importer_id = fi.file_importer_id AND status in ('X', 'X1'))) > 0 THEN 'Waiting' ELSE 'Accepted' END AS waiting_status

赞(0）回复(0）举报 2021-06-20

我来回答

mysql case语句

1条答案

相关问题

热门标签

最新问答