SQLite 和 If() 条件

发布于 2024-12-03 06:45:16 字数 1011 浏览 1 评论 0原文

我有一个正在尝试在 SQLite 中运行的 MySQL 查询。 我发现 IF 条件在 SQLite 中不起作用,应该转换为 CASE。 由于 MySQL 查询非常大,无法进行概述,因此我希望有人可以向我展示应该如何完成它。在许多其他 MySQL 查询之一中,我必须转换为 SQLite。

一旦我看到应该如何在我使用的查询中完成它(因为我熟悉它),我认为我可以为其他人处理它。 这是应该在 SQLite 中运行的 MySQL:

select 
p.products_model, 
pd.products_name, 
m.manufacturers_name, 
p.products_quantity, 
p.products_weight, 
p.products_image, 
p.products_id, 
p.manufacturers_id, 
p.products_price, 
p.products_tax_class_id, 
IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price,
IF(s.status, s.specials_new_products_price, p.products_price) as final_price 
from 
products_description pd, 
products p 
left join manufacturers m on p.manufacturers_id = m.manufacturers_id 
left join specials s on p.products_id = s.products_id, 
products_to_categories p2c 
where 
p.products_status = "1" and 
p.products_id = p2c.products_id and 
pd.products_id = p2c.products_id and 
pd.language_id = "1" and 
p2c.categories_id = "10"

I have a MySQL query that I'm trying to run in SQLite.
I found out that the IF condition isn't working in SQLite, and should be converted to a CASE.
As the MySQL query is pretty big to have an overview, I was hoping someone can show me how it should be done. In one of many other MySQL queries, I must convert to SQLite.

Once I see how it should be done in a query that I use (because I'm familiar with it), I assume I can handle it for the others.
Here is the MySQL that should run in SQLite:

select 
p.products_model, 
pd.products_name, 
m.manufacturers_name, 
p.products_quantity, 
p.products_weight, 
p.products_image, 
p.products_id, 
p.manufacturers_id, 
p.products_price, 
p.products_tax_class_id, 
IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price,
IF(s.status, s.specials_new_products_price, p.products_price) as final_price 
from 
products_description pd, 
products p 
left join manufacturers m on p.manufacturers_id = m.manufacturers_id 
left join specials s on p.products_id = s.products_id, 
products_to_categories p2c 
where 
p.products_status = "1" and 
p.products_id = p2c.products_id and 
pd.products_id = p2c.products_id and 
pd.language_id = "1" and 
p2c.categories_id = "10"

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

恬淡成诗 2024-12-10 06:45:16

更改:

IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price,
IF(s.status, s.specials_new_products_price, p.products_price) as final_price 

CASE
    WHEN s.status <> 0 AND s.status IS NOT NULL
    THEN s.specials_new_products_price
    ELSE NULL
END AS specials_new_products_price,
CASE
    WHEN s.status <> 0 AND s.status IS NOT NULL
    THEN s.specials_new_products_price
    ELSE p.products_price
END AS final_price

Change:

IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price,
IF(s.status, s.specials_new_products_price, p.products_price) as final_price 

to

CASE
    WHEN s.status <> 0 AND s.status IS NOT NULL
    THEN s.specials_new_products_price
    ELSE NULL
END AS specials_new_products_price,
CASE
    WHEN s.status <> 0 AND s.status IS NOT NULL
    THEN s.specials_new_products_price
    ELSE p.products_price
END AS final_price
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文