sqlite 查询数据，sum, 大小数量，和总价

最新推荐文章于 2025-06-28 13:26:08 发布

原创最新推荐文章于 2025-06-28 13:26:08 发布 · 1.2k 阅读

0 ·

CC 4.0 BY-SA版权

sql server 专栏收录该内容

24 篇文章

订阅专栏

本文介绍了一种使用SQL语言来统计特定时间段内食品销售情况的方法。主要包括创建用于存储食品价格及销售信息的数据表，并通过复杂的SQL查询来获取每种食品的销售总量、大小份的数量以及销售额等关键指标。

1.create table fooodPrice(id int not null,foodTypeId integer primary key autoincrement,
foodName char(50) not null,foodPrice0 real not null,foodPrice1 real not null)
;

2. create table saledInfo(id integer primary key autoincrement,foodTypeId int not null,
num int not null,model int not null default '0',createTime datetime not null default (datetime('now','localtime')),
comments text not null default '');

3.//分别统计出大小数量，和总价
select foodName , sum(num) , sum(case when model='0' then num else 0 end) ,sum(case when model='0' then num* s1.foodPrice0 else num* s1.foodPrice1 end) as small from fooodPrice s1 , saledInfo s2 where s1.foodTypeId=s2.foodTypeId group by foodName;

select * from (select foodName,sum(num) as '总数量', createTime, sum(case when model='0' then num else 0 end) as '小份数量',sum(case when model='1' then num else 0 end) as '大份数量',sum(case when model='0' then num* s1.foodPrice0 else num* s1.foodPrice1 end) as '单品种总金额' from fooodPrice s1 , saledInfo s2 where s1.foodTypeId=s2.foodTypeId and createTime>'2021-07-21' and createTime<'2021-07-23' group by foodName ) as t1
group by 单品种总金额 order by createTime desc

select sum(单品种总金额) as '总金额' from (select foodName,sum(num) as '总数量', createTime, sum(case when model='0' then num else 0 end) as '小份数量',sum(case when model='1' then num else 0 end) as '大份数量',sum(case when model='0' then num* s1.foodPrice0 else num* s1.foodPrice1 end) as '单品种总金额' from fooodPrice s1 , saledInfo s2 where s1.foodTypeId=s2.foodTypeId and createTime>'2021-07-21' and createTime<'2021-07-23' group by foodName ) as t1