我有这个查询
select ts.name as my_name, ss.step_number, p.specs, p.price,
ssp.class_id from optional_system_step
as ss join system as s on s.system_id=ss.system_id join category_description
as cd on cd.category_id=ss.category_id join optional_system_step_product as
ssp on ss.system_step_id=ssp.system_step_id join product as p on
p.product_id=ssp.product_id join product_description as pd on
pd.product_id=p.product_id join template_step as ts on
(ts.template_id=s.optional_template_id and ts.step_number=ss.step_number)
where s.system_id = '15' order by ss.step_number, ssp.class_id;
它返回这个
admin 1 999.0000 1
admin 1 1349.0000 1
admin 1 1699.0000 1
pay 1 479.0000 2
pay 1 149.0000 2
pay 1 269.0000 3
看起来不错,但问题是我需要按class_id分组,但在价格字段中,我需要添加三个价格,因此例如,我会返回这两行
admin 1 4047.0000 1
pay 1 897.0000 2
所以基本上我想把这三个数字加在一起,并在价格字段
将聚合函数SUM()
与GROUP BY
:一起使用
select ts.name as my_name, ss.step_number, p.specs, SUM(p.price), ssp.class_id
from optional_system_step as ss
join system as s on s.system_id=ss.system_id
join category_description as cd on cd.category_id=ss.category_id
join optional_system_step_product as ssp on ss.system_step_id=ssp.system_step_id
join product as p on p.product_id=ssp.product_id
join product_description as pd on pd.product_id=p.product_id
join template_step as ts on (ts.template_id=s.optional_template_id and ts.step_number=ss.step_number)
where s.system_id = '15'
GROUP BY ts.name, ss.step_number, p.spects, ssp.class_id
order by ss.step_number, ssp.class_id;
可能是SUM还是GROUP BY的SUM?
如果按class_id分组,则上面实际上将返回3行,因为您有1,2和3。
我认为您需要的查询如下,但它假设您可以按ts.name
、ss.step_number
、p.specs
和ssp.class_id
进行分组
SELECT
ts.name AS my_name
, ss.step_number
, p.specs
, SUM( p.price)
, ssp.class_id
FROM
optional_system_step AS ss
JOIN system AS s
ON s.system_id = ss.system_id
JOIN category_description AS cd
ON cd.category_id = ss.category_id
JOIN optional_system_step_product AS ssp
ON ss.system_step_id = ssp.system_step_id
JOIN product AS p
ON p.product_id = ssp.product_id
JOIN product_description AS pd
ON pd.product_id = p.product_id
JOIN template_step AS ts
ON ( ts.template_id = s.optional_template_id
AND ts.step_number = ss.step_number
)
WHERE
s.system_id = '15'
GROUP BY
ts.NAME,
ss.step_number,
p.specs,
ssp.class_id
ORDER BY
ss.step_number
, ssp.class_id ;
查询的输出与SELECT中的列数不匹配,因此我不确定是否缺少任何内容。
但这应该能解决你的目的:
select ts.name as my_name, ss.step_number, p.specs, SUM(p.price) as price, ssp.class_id
from optional_system_step as ss
join system as s on s.system_id=ss.system_id
join category_description as cd on cd.category_id=ss.category_id
join optional_system_step_product as ssp on ss.system_step_id=ssp.system_step_id
join product as p on p.product_id=ssp.product_id
join product_description as pd on pd.product_id=p.product_id
join template_step as ts on (ts.template_id=s.optional_template_id and ts.step_number=ss.step_number)
where s.system_id = '15'
GROUP BY ssp.class_id;
我还想补充一点,您不需要在其他列上使用GROUP BY,因为它们似乎都有相同的值,所以ssp.class_id上的GROUP BY应该可以。
此外,虽然与您的问题没有直接关系,但我认为如果删除category_description
和product_description
联接,您的查询应该仍然可以正常工作,看起来也会更干净一些。我不能证实这一点,因为我不了解你数据库的结构。