3逐列查询并创建新表
3逐列查询并创建新表
提问于 2019-10-18 07:42:22
我有学生，这些学生每天早晚吃饭。我想打印每个学生在早上和晚上吃的饭的数量。
如果学生早上吃的菜超过一个，我要打印桌子上的数字和食物的ID。
FoodType时间是凌晨1点，晚上2点
StudentId FoodId FoodType
3 1 1
3 2 1
3 3 1
3 4 2
4 3 1
4 1 2
4 2 2
4 4 2
5 4 2
5 1 1
6 1 1
6 2 1
6 3 2
6 4 2样本输出；
StudentId MorningFoodCountOrId EveningFoodCountOrId
3 3 meals 4
4 3 3 meals
5 4 1
6 2 meals 2 meals回答 2
Stack Overflow用户
回答已采纳
发布于 2019-10-18 07:49:24
使用条件聚合。决定是否打印记录数量或其值的逻辑是不直观的，但我要这样说：
select
studentId,
case when sum(case when foodtype = 1 then 1 else 0 end) = 1
then max(case when foodtype = 1 then foodId end)
else sum(case when foodtype = 1 then 1 else 0 end)
end MorningFoodCountOrId
case when sum(case when foodtype = 2 then 1 else 0 end) = 1
then max(case when foodtype = 2 then foodId end)
else sum(case when foodtype = 2 then 1 else 0 end)
end EveningFoodCountOrId
from mytable
group by studentId通过不计算条件和两次，RDMBS应该能够优化查询。
注意:您没有指定正在使用的RDMBS。如果这是MySQL，则可以将条件和缩短一点，如下所示：
select
studentId,
case when sum(foodtype = 1) = 1
then max(case when foodtype = 1 then foodId end)
else sum(foodtype = 1)
MorningFoodCountOrId
case when sum(foodtype = 2) = 1
then max(case when foodtype = 2 then foodId end)
else sum(foodtype = 2)
EveningFoodCountOrId
from mytable
group by studentIdStack Overflow用户
发布于 2019-10-18 07:48:04
下面是您的查询，select sum()和case..将执行以下操作
select t1.studentid
, case when t1.m <= 1 then t2.FoodId else concat(t1.m, ' meals') end MorningFoodCountOrId
, case when t1.e <= 1 then t3.FoodId else concat(t1.e, ' meals') end EveningFoodCountOrId
from(
select studentid
, sum(case when FoodType = 1 then 1 else 0 end) as m
, sum(case when FoodType = 2 then 1 else 0 end) as e
from tableA
group by studentid) t1
left join tableA t2 on t2.studentId = t1.studentId and t1.m = 1 and t2.FoodType = 1
left join tableA t3 on t3.studentId = t1.studentId and t1.e = 1 and t3.FoodType = 2
order by t1.studentid 请参阅[医]小提琴
在postgresql中
, case when t1.m <= 1 then t2.FoodId::text else concat(t1.m, ' meals') end MorningFoodCountOrId
, case when t1.e <= 1 then t3.FoodId::text else concat(t1.e, ' meals') end EveningFoodCountOrId页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/58446141
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号