执行顺序,先where,再group by,再order by。
group by 后的字段要么接聚合函数如SUM、AVG、COUNT、MAX、 SUM、AVG,要么接查询的字段。
比如:
SELECT truck_order AS 车次,
count (c.coal_name) AS 车数,
c.coal_name AS 煤种,
s.Mine AS 发矿,
tr.team_name AS 承运单位,
a.ACCEDEPT AS 收货单位,
r.rev_place AS 货场,
ZDFlage,
SUM (l.start_weg) AS 矿发,
SUM (l.gross_weg) AS 毛重,
SUM (l.tare_weg ) AS 皮重,
SUM (l.net_weg) AS 净重
FROM ngt_n_coal_receive r
LEFT JOIN ngt_n_coal_type c ON r.coal_type=c.sys_id_key
LEFT JOIN WZ_MIS_NEW.dbo.AcceDept a ON r.coal_shipper=a.ACCEID
LEFT JOIN WZ_MIS_NEW.dbo.SendMine s ON r.coal_maker=s.MineId
LEFT JOIN ngt_n_truck_team tr ON r.trans_unit=tr.sys_id_key
LEFT JOIN ngt_n_coal_rev_list l ON r.sys_id_key=l.sys_id_key where l.in_time>='2016-03-06' AND l.in_time<'2016-03-07'
GROUP BY truck_order,c.coal_name,r.rev_place,a.ACCEDEPT,s.Mine,tr.team_name,ZDFlage
要查询的字段要放到group by后面