分组统计

--删除表
drop table STATUSR.testTable;
--创建表
create table STATUSR.testTable
(
tableid varchar(10) not null,
productName varchar(20) not null,
canShui varchar(20) not null,
xingHa varchar(20) not null,
price1 number(5) not null,
shuLiang number(3) not null,
sumPrice number(10) not null
)
--选择所有数据
SELECT *  FROM STATUSR.testTable where tableid in (3,7);

SELECT *  FROM STATUSR.testTable ;

select distinct productName,canShui,xingHa,price1,shuLiang,sumPrice from STATUSR.testtable;

select distinct productName,canShui,xingHa,price1,sum(shuLiang),sum(sumPrice) from STATUSR.testtable;

delete from STATUSR.testTable;

--全部相加
SELECT productName,canShui,xingHa,price1,shuLiang, sum(sumprice)  FROM STATUSR.testTable  group by productName, ;

--加锁
lock table STATUSR.testTable in exclusive mode;
--插入数据
insert into STATUSR.testTable(tableid,productName,canShui,xingHa,price1,shuLiang,sumPrice)values('1','yst','boy','24',10,1,10);
insert into STATUSR.testTable(tableid,productName,canShui,xingHa,price1,shuLiang,sumPrice)values('2','ltm','boy','25',12,1,10);
insert into STATUSR.testTable(tableid,productName,canShui,xingHa,price1,shuLiang,sumPrice)values('3','yst','boy','24',11,1,11);
insert into STATUSR.testTable(tableid,productName,canShui,xingHa,price1,shuLiang,sumPrice)values('4','xlh','girl','28',12,2,24);
insert into STATUSR.testTable(tableid,productName,canShui,xingHa,price1,shuLiang,sumPrice)values('5','xlw','girl','24',10,3,30);
insert into STATUSR.testTable(tableid,productName,canShui,xingHa,price1,shuLiang,sumPrice)values('6','yst','girl','24',10,2,20);
insert into STATUSR.testTable(tableid,productName,canShui,xingHa,price1,shuLiang,sumPrice)values('7','yst','boy','24',11,1,11);

--按组大小排序
SELECT *  FROM STATUSR.testTable order by productName, sumprice desc;

--组的大小
--按productName分组 然后总额大得排在前面
SELECT  productname,canShui,xingHa,price1,shuLiang,sumprice,sum(sumprice) as sumprice0 
FROM STATUSR.testTable
group by productname, canShui, xingHa, price1, shuLiang, sumprice
order by sumprice0 desc ;

--组的总额
SELECT productname, sum(sumprice) as sumprice0 
FROM STATUSR.testTable
group by productname
order by sumprice0 desc ;