时间戳

首页 > 解决方案 > SQLite 查询中选择语句的别名

sqlite - SQLite 查询中选择语句的别名

问题描述

有什么方法可以使用别名来避免select在此查询中重复嵌套语句？

select distinct
  ingot_sales.date_time as date,
  ingot_sales.name as buyer,
  ingot_sales.metal as metal,
  (select price from ingot_list where ingot_sales.metal = ingot_list.metal) as unit_price,
  ingot_sales.quantity as quantity,
  (select price from ingot_list where ingot_sales.metal = ingot_list.metal) * quantity as total_price,
  ingot_sales.paid as paid
from ingot_sales, ingot_list;

我尝试使用unit_price代替第二个 nested select，但不断得到重复的行，每个行具有不同的计算值。

标签： sqlite

解决方案

您可以加入表，这样您就不需要相关的子查询：

select distinct
  s.date_time as date,
  s.name as buyer,
  s.metal as metal,
  l.price as unit_price,
  s.quantity as quantity,
  l.price * s.quantity as total_price,
  s.paid as paid
from ingot_sales s left join ingot_list l
on s.metal = l.metal

推荐阅读