SqlServer2008全套 3 select...into 建新表，分组，排序，函数使用

第六课 数据查询与管理

select * from v_sales_shop where area_name='East'
  and outdate='20141001'
  
  --可以按门店和日期  或者只按日期（门店）分组
  select dbno_bi,SUM(nb)NB from shop_sales where dbno_bi='SE47'
  group by  dbno_bi,outdate
  
  --求平均
  SELECT AA.dbno_bi ,AVG(AA.NB) XX FROM
    (select dbno_bi,SUM(nb)NB from shop_sales where dbno_bi LIKE'SE4%'
  group by  dbno_bi)AA
  GROUP by dbno_bi
  
  --rollup 会多出一列: 算总的平均值
    SELECT AA.dbno_bi ,AVG(AA.NB) XX FROM
    (select dbno_bi,SUM(nb)NB from shop_sales where dbno_bi LIKE'SE4%'
  group by  dbno_bi)AA
  GROUP by rollup(dbno_bi)
  --会对每个门店分组 并且每个求一次总的平均值
  SELECT AA.dbno_bi ,AA.outdate,AVG(AA.NB) XX FROM
    (select dbno_bi,outdate,SUM(nb)NB from shop_sales where dbno_bi LIKE'SE4%'
  group by  dbno_bi,outdate)AA
  GROUP by rollup(dbno_bi,outdate)
  
  
    --cube 也会多出一列: 算总的平均值
    SELECT AA.dbno_bi ,AVG(AA.NB) XX FROM
    (select dbno_bi,SUM(nb)NB from shop_sales where dbno_bi LIKE'SE4%'
  group by  dbno_bi)AA
  GROUP by cube(dbno_bi)
  
  
  
    select outdate,AVG(nb)NB from shop_sales where dbno_bi LIKE'SE4%'
  group by  outdate
  
--------max

select MAX(endprice) from shop_sales where outdate>='20141101'

-----前3名
select top 3  endprice from shop_sales 
order by endprice desc

------having
  SELECT AA.dbno_bi ,AVG(AA.NB) XX FROM
    (select dbno_bi,SUM(nb)NB from shop_sales where dbno_bi LIKE'SE4%'
  group by  dbno_bi)AA
  GROUP by dbno_bi
  having AVG(AA.NB)>30
  order by dbno_bi desc
 
 --插入语句
 
 select  * from shop_sales where dbno_bi='xxx'
 insert into shop_sales values ('xxx','dd','FF','19491001',500,5,'DFFFF','DD-SS')
 insert into shop_sales (dbno_bi,class2)values ('xxx','dd')--只查两个属性的值
 
 ---insert...select
 ---选中表右键--》编写脚本--》create到 这样就创建了个结构一样的表
 insert into shop_sales1 select top 100 * from shop_sales 
 
 --- select...into 会新建一个数据表
select * 
into #X临时表呢
from shop_sales where dbno_bi='SE47'

select dbno_bi,SUM(nb)NB 
into test_top
from shop_sales where dbno_bi LIKE'SE4%'
group by dbno_bi

select * from #X临时表呢

---update
update #X临时表呢 set dbno_bi='SSSS' where nb=-1

--update...from  见截图

update #X临时表呢 set
from shop_sales  where 

---delete 表名就ok 
delete   #X临时表呢 

------TOP
select top 10 * from test_top
--返回10%
select top 10 percent * from test_top

declare @i int
set @i=20
select top (@i) percent * from test_top

declare @i int
set @i=20
select top (@i) percent * from test_top order by  NB desc

--重复的会被显示
select top 10 * from test_top order by  NB desc
select top 10  with ties  * from test_top order by  NB desc


select top 10 * from test_top order by  NB desc
--怎么显示NB不重复的...
select top 10 * from(
 SELECT NB from test_top group BY NB HAVING COUNT(1) = 1
 )AA  order by NB desc
--显示NB不重复
select top 10 NB from test_top group BY NB order by NB desc

update top (6) test_top set dbno_bi='响当当的'

-----------------compute子句 对结果集进行运算
select * from shop_sales1 where outdate='20141102'
compute sum(endprice),AVG(endprice),MAX(endprice),MIN(endprice)


------在where子句使用运算符
select * from shop_sales where
dbno_bi='SE47' AND outdate='20141102'
and endprice between 2000 and 3000

--是5的整数倍
select * from shop_sales where
dbno_bi='SE47' AND outdate='20141102'
and endprice%5=0 

---3 排序 分数和学号排序

---4 分组

--函数的使用