精典SQL语句

前段时间因为找工作的原因，不得不被动的深入学习了数据库，尤其是复杂的SQL语句，感觉自己提升了不少，今天把几个精典的例子拿出来跟大家共同分享，希望对大家能有所帮助。

<!-- 删除表中的有重复行的数据(行中的每一列的值都重复)-->
 select distinct * into #temp from  s drop table s
 select distinct * into s from #temp drop table #temp

 <!-- 根据表中的某一列，删除表中该列所有重复的值->
  select identity(int,1,1) as autoID, * into #Tmp from tableName
  select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID
  select * from #Tmp where autoID in(select autoID from #tmp2)

<!--精典例题->
 --表结构：
 Date            SaleBuy        quantity            ProductID
 20071211          S              1000                 1
 20080420          S              2000                 1
 20071211          B              3500                 2
 20080420          B              4800                 1
 20071211          S              2300                 2
 20071211          S              5400                 2
 20071211          B              2400                 2

-所要结果如下：
 Date            SaleBuy        quantity            ProductID
 20071211          1000            0                   1
 20071211         7700           5900                 2
 20080420         2000           4800                 1

--解决方法:
 1:
Select date,
        sum(CASE saleBuy WHEN 's' THEN quantity ELSE 0 END) as saleQuantity,
        sum(CASE saleBuy WHEN 'b' THEN quantity ELSE 0 END) as buyQuantity,
        productId
from sale group by date, productId order by date, productId
 2:
select date,saleBuy,sum(quantity) as qu1,productId as p1 into #temp from sale
group by date,saleBuy,productId
select s.date,s.qu1,qu2=(case when b.qu1 is NULL then 0 else b.qu1 end ),s.p1 from
(select date,saleBuy,qu1,p1 from #temp where saleBuy='s')as s
 left join (select date,saleBuy,qu1,p1 from #temp where saleBuy='b')as b
on s.p1=b.p1 and s.date=b.date

-如何求出同一表中不同列的值相同的数量
select sum(case when a1='1' and b1='2' then 1 else 0 end)+sum(case when a2 ='1' and b1='2' then 1 else 0 end)+sum(case when a3 ='1' and b1='2' then 1 else 0 end) as countz from kaka

 ---如何根据某列的值 按主义排序如‘aceb’,'abce'
 select * from sale order by charindex(name,'中华人民共和国')

-从几个列中找出值最大的那一列的值，并赋值给该表中的其它列
update AthleteResult
set GraspResult=(select (case
 　　when Grasp1 < Grasp2 then Grasp2
 　　when Grasp1<Grasp3 then Grasp3
 　　else Grasp1 end)as Grasp from AthleteResult),
    ErectResult=(select (case
  when Erect1<Erect2 then Erect2
  when Erect1 <Erect3 then Erect3
  else Erect1 end) as Erect from AthleteResult)

  <!-- 随机从表中抽取10条记录->
select top 10 * from [table name] order by newid()

 

完