请看看这个example:
create table c
(a2 int,
b2 int
,c2 int)
insert into b values(1,2,4)
insert into b values(3,6,6)
insert into c values(1,5,7)
insert into c values(3,7,9)update c set b2=b1,c2=c1 from b where b.a1=3 and c.a2=3
select * from c
create table c
(a2 int,
b2 int
,c2 int)
insert into b values(1,2,4)
insert into b values(3,6,6)
insert into c values(1,5,7)
insert into c values(3,7,9)update c set b2=b1,c2=c1 from b where b.a1=3 and c.a2=3
select * from c
GROUP BY 和 WHERE 子句
可以在包含 GROUP BY 子句的查询中使用 WHERE 子句。在进行任何分组之前,将消除不符合 WHERE 子句条件的行。例如:USE pubs
SELECT type, AVG(price)
FROM titles
WHERE advance > $5000
GROUP BY type下面是结果集:type
------------ --------------------------
business 2.99
mod_cook 2.99
popular_comp 21.48
psychology 14.30
trad_cook 17.97 (5 row(s) affected)只有预付款超过 $5,000 的行才包含在查询结果所显示的组中。
"EGPCS_MV_DTL"."MVID"),
sum("EGPCS_MV_DTL"."GOODSQTY"),
sum("EGPCS_MV_DTL"."MVPRICE"),
Max("EGPCS_MV_V"."CREDATE"),
Max("EGPCS_MV_V"."FROMPLACEPOINTID"),
Max("EGPCS_MV_V"."FROMPLACEPOINTNAME"),
Max("EGPCS_MV_V"."TOPLACEPOINTNAME")
from
EGPCS_MV_DTL,
EGPCS_MV_V
where
"EGPCS_MV_V"."CREDATE" between to_date(null) and to_date(null) and
"EGPCS_MV_DTL"."MVID" = "EGPCS_MV_V"."MVID"
group by
"EGPCS_MV_DTL"."MVID"
MAX(ColumnB) AS MaxB,
SUM(ColumnC) AS SumC
FROM TableX
GROUP BY ColumnA该选择语句返回两行,为 ColumnA 中的每个唯一值各返回一行:ColumnA MaxB SumC
----------- ---- -----------
1 ghi 18
2 mno 11 (2 row(s) affected)但是,选择列表中只包含 ColumnB 表达式是不合法的:SELECT ColumnA,
ColumnB,
SUM(ColumnC) AS SumC
FROM TableX
GROUP BY ColumnA
max(B.CREDATE), max(B.FROMPLACEPOINTID),
max(B.FROMPLACEPOINTNAME),max(B.TOPLACEPOINTNAME)
from EGPCS_MV_DTL A,EGPCS_MV_V B
where B.CREDATE between to_date(null) and to_date(null) and A.MVID = B.MVID
group by A.MVIDgroup by A.MVID