场景模拟 有一个订单表 表中classify_alias 字段分别有线路，导游，酒店，游轮，租车五个类别
使用一条sql查询出这五个类别订单的数量
表中有很多条数据, 每条数据拥有一个状态字段,现在需要统计不同状态的数据有多少条.
1、使用sum (if())
SELECT
SUM(
IF (classify_alias = "tourline",1,0)
) tourline,
SUM(
IF (classify_alias = "guide", 1, 0)
) guide,
SUM(
IF (classify_alias = "hotel", 1, 0)
) hotel,
SUM(
IF (
classify_alias = "cruise", 1, 0 )
) cruise,
SUM(
IF ( classify_alias = "rentcar",1,0)
) rentcar
FROM
v_product_order
WHERE
parent_order_no = 0
AND customer_company_id = 12、case when
2.1count
SELECT
COUNT(
(
CASE
WHEN classify_alias = 'tourline' THEN
1
END
)
) tourline,
COUNT(
(
CASE
WHEN classify_alias = 'guide' THEN
1
END
)
) guide,
COUNT(
(
CASE
WHEN classify_alias = 'hotel' THEN
1
END
)
) hotel,
COUNT(
(
CASE
WHEN classify_alias = 'cruise' THEN
1
END
)
) cruise,
COUNT(
(
CASE
WHEN classify_alias = 'rentcar' THEN
1
END
)
) rentcar
FROM
v_product_order
WHERE
parent_order_no = 0
AND customer_company_id = 12.2sum
SELECT
sum(case classify_alias when 'tourline' then 1 else 0 end)tourline,
sum(case classify_alias when 'guide' then 1 else 0 end)guide,
sum(case classify_alias when 'hotel' then 1 else 0 end)hotel,
sum(case classify_alias when 'cruise' then 1 else 0 end)cruise,
sum(case classify_alias when 'rentcar' then 1 else 0 end)rentcar
FROM
v_product_order
WHERE
parent_order_no = 0
AND customer_company_id = 1
扫一扫
675
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
