一些不错的sql语句

1、说明：复制表(只复制结构,源表名：a   新表名：b)   (Access可用)
法一：select   *   into   b   from   a   where   1 <> 1
法二：select   top   0   *   into   b   from   a

2、说明：拷贝表(拷贝数据,源表名：a   目标表名：b)   (Access可用)
insert   into   b(a,   b,   c)   select   d,e,f   from   b;

3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径)   (Access可用)
insert   into   b(a,   b,   c)   select   d,e,f   from   b   in   ‘具体数据库’   where   条件
例子：..from   b   in   ' "&Server.MapPath( ". ")& "\data.mdb "   & " '   where..

4、说明：子查询(表名1：a   表名2：b)
select   a,b,c   from   a   where   a   IN   (select   d   from   b   )   或者:   select   a,b,c   from   a   where   a   IN   (1,2,3)

5、说明：显示文章、提交人和最后回复时间
select   a.title,a.username,b.adddate   from   table   a,(select   max(adddate)   adddate   from   table   where   table.title=a.title)   b

6、说明：外连接查询(表名1：a   表名2：b)
select   a.a,   a.b,   a.c,   b.c,   b.d,   b.f   from   a   LEFT   OUT   JOIN   b   ON   a.a   =   b.c

7、说明：在线视图查询(表名1：a   )
select   *   from   (SELECT   a,b,c   FROM   a)   T   where   t.a   >   1;

8、说明：between的用法,between限制查询数据范围时包括了边界值,not   between不包括
select   *   from   table1   where   time   between   time1   and   time2
select   a,b,c,   from   table1   where   a   not   between   数值1   and   数值2

9、说明：in   的使用方法
select   *   from   table1   where   a   [not]   in   (‘值1’,’值2’,’值4’,’值6’)

10、说明：两张关联表，删除主表中已经在副表中没有的信息  
delete   from   table1   where   not   exists   (   select   *   from   table2   where   table1.field1=table2.field1   )

11、说明：四表联查问题：
select   *   from   a   left   inner   join   b   on   a.a=b.b   right   inner   join   c   on   a.a=c.c   inner   join   d   on   a.a=d.d   where   .....

12、说明：日程安排提前五分钟提醒  
SQL:   select   *   from   日程安排   where   datediff( 'minute ',f开始时间,getdate())> 5

13、说明：一条sql   语句搞定数据库分页
select   top   10   b.*   from   (select   top   20   主键字段,排序字段   from   表名   order   by   排序字段   desc)   a,表名   b   where   b.主键字段   =   a.主键字段   order   by   a.排序字段

14、说明：前10条记录
select   top   10   *   form   table1   where   范围

15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
select   a,b,c   from   tablename   ta   where   a=(select   max(a)   from   tablename   tb   where   tb.b=ta.b)

16、说明：包括所有在   TableA   中但不在   TableB和TableC   中的行并消除所有重复行而派生出一个结果表
(select   a   from   tableA   )   except   (select   a   from   tableB)   except   (select   a   from   tableC)

17、说明：随机取出10条数据
select   top   10   *   from   tablename   order   by   newid()

18、说明：随机选择记录
select   newid()

19、说明：删除重复记录
Delete   from   tablename   where   id   not   in   (select   max(id)   from   tablename   group   by   col1,col2,...)

20、说明：列出数据库里所有的表名
select   name   from   sysobjects   where   type= 'U '  

21、说明：列出表里的所有的
select   name   from   syscolumns   where   id=object_id( 'TableName ')

22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select   中的case。
select   type,sum(case   vender   when   'A '   then   pcs   else   0   end),sum(case   vender   when   'C '   then   pcs   else   0   end),sum(case   vender   when   'B '   then   pcs   else   0   end)   FROM   tablename   group   by   type
显示结果：
type   vender   pcs
电脑   A   1
电脑   A   1
光盘   B   2
光盘   A   2
手机   B   3
手机   C   3

23、说明：初始化表table1
TRUNCATE   TABLE   table1

24、说明：选择从10到15的记录
select   top   5   *   from   (select   top   15   *   from   table   order   by   id   asc)   table_别名   order   by   id   desc

1.日期算當月天數:
      select   Day(dateadd(day,-1,convert(datetime,convert(char(07),dateadd(m,1,getdate()),120)+ '-01 ')))
      select   32-Day(getdate()+(32-Day(getdate())))

2.簡單的分割字串:
      用 '   union   all   select   'replace '分割符 '

--1、查找员工的编号、姓名、部门和出生日期，如果出生日期为空值，
--显示日期不详,并按部门排序输出,日期格式为yyyy-mm-dd。
select   emp_no   ,emp_name   ,dept   ,
              isnull(convert(char(10),birthday,120), '日期不详 ')   birthday
from   employee
order   by   dept

--2、查找与喻自强在同一个单位的员工姓名、性别、部门和职称
select   emp_no,emp_name,dept,title
from   employee
where   emp_name <> '喻自强 '   and   dept   in
      (select   dept   from   employee
        where   emp_name= '喻自强 ')

--3、按部门进行汇总，统计每个部门的总工资
select   dept,sum(salary)
from   employee
group   by   dept

--4、查找商品名称为14寸显示器商品的销售情况，
--显示该商品的编号、销售数量、单价和金额
select   a.prod_id,qty,unit_price,unit_price*qty   totprice
from   sale_item   a,product   b
where   a.prod_id=b.prod_id   and   prod_name= '14寸显示器 '

--5、在销售明细表中按产品编号进行汇总，统计每种产品的销售数量和金额
select   prod_id,sum(qty)   totqty,sum(qty*unit_price)   totprice
from   sale_item
group   by   prod_id

--6、使用convert函数按客户编号统计每个客户1996年的订单总金额
select   cust_id,sum(tot_amt)   totprice
from   sales
where   convert(char(4),order_date,120)= '1996 '
group   by   cust_id

--7、查找有销售记录的客户编号、名称和订单总额
select   a.cust_id,cust_name,sum(tot_amt)   totprice
from   customer   a,sales   b
where   a.cust_id=b.cust_id
group   by   a.cust_id,cust_name

--8、查找在1997年中有销售记录的客户编号、名称和订单总额
select   a.cust_id,cust_name,sum(tot_amt)   totprice
from   customer   a,sales   b
where   a.cust_id=b.cust_id   and   convert(char(4),order_date,120)= '1997 '
group   by   a.cust_id,cust_name

--9、查找一次销售最大的销售记录
select   order_no,cust_id,sale_id,tot_amt
from   sales
where   tot_amt=
      (select   max(tot_amt)
        from   sales)

--10、查找至少有3次销售的业务员名单和销售日期
select   emp_name,order_date
from   employee   a,sales   b  
where   emp_no=sale_id   and   a.emp_no   in
    (select   sale_id
      from   sales
      group   by   sale_id
      having   count(*)> =3)
order   by   emp_name

--11、用存在量词查找没有订货记录的客户名称
select   cust_name
from   customer   a
where   not   exists
      (select   *
        from   sales   b
        where   a.cust_id=b.cust_id)

--12、使用左外连接查找每个客户的客户编号、名称、订货日期、订单金额
--订货日期不要显示时间，日期格式为yyyy-mm-dd
--按客户编号排序，同一客户再按订单降序排序输出
select   a.cust_id,cust_name,convert(char(10),order_date,120),tot_amt
from   customer   a   left   outer   join   sales   b   on   a.cust_id=b.cust_id
order   by   a.cust_id,tot_amt   desc

--13、查找16M   DRAM的销售情况，要求显示相应的销售员的姓名、
--性别，销售日期、销售数量和金额，其中性别用男、女表示
select   emp_name   姓名,   性别=   case   a.sex     when   'm '   then   '男 '
                                                                              when   'f '   then   '女 '  
                                                                              else   '未 '
                                                                              end,
                销售日期=   isnull(convert(char(10),c.order_date,120), '日期不详 '),
                qty   数量,   qty*unit_price   as   金额
from   employee   a,   sales   b,   sale_item   c,product   d
where   d.prod_name= '16M   DRAM '   and   d.pro_id=c.prod_id   and
            a.emp_no=b.sale_id   and   b.order_no=c.order_no

--14、查找每个人的销售记录，要求显示销售员的编号、姓名、性别、
--产品名称、数量、单价、金额和销售日期
select   emp_no   编号,emp_name   姓名,   性别=   case   a.sex   when   'm '   then   '男 '
                                                                              when   'f '   then   '女 '  
                                                                              else   '未 '
                                                                              end,
            prod_name   产品名称,销售日期=   isnull(convert(char(10),c.order_date,120), '日期不详 '),
            qty   数量,   qty*unit_price   as   金额
from   employee   a   left   outer   join   sales   b   on   a.emp_no=b.sale_id   ,   sale_item   c,product   d
where   d.pro_id=c.prod_id   and   b.order_no=c.order_no

--15、查找销售金额最大的客户名称和总货款
select   cust_name,d.cust_sum
from       customer   a,
              (select   cust_id,cust_sum
                from   (select   cust_id,   sum(tot_amt)   as   cust_sum
                            from   sales
                            group   by   cust_id   )   b
                where   b.cust_sum   =  
                              (   select   max(cust_sum)
                                  from   (select   cust_id,   sum(tot_amt)   as   cust_sum
                                              from   sales
                                              group   by   cust_id   )   c   )
                )   d
where   a.cust_id=d.cust_id  

--16、查找销售总额少于1000元的销售员编号、姓名和销售额
select   emp_no,emp_name,d.sale_sum
from       employee   a,
              (select   sale_id,sale_sum
                from   (select   sale_id,   sum(tot_amt)   as   sale_sum
                            from   sales
                            group   by   sale_id   )   b
                where   b.sale_sum   <1000                              
                )   d
where   a.emp_no=d.sale_id  

--17、查找至少销售了3种商品的客户编号、客户名称、商品编号、商品名称、数量和金额
select   a.cust_id,cust_name,b.prod_id,prod_name,d.qty,d.qty*d.unit_price
from   customer   a,   product   b,   sales   c,   sale_item   d
where   a.cust_id=c.cust_id   and   d.prod_id=b.prod_id   and  
            c.order_no=d.order_no   and   a.cust_id   in   (
            select   cust_id
            from     (select   cust_id,count(distinct   prod_id)   prodid
                          from   (select   cust_id,prod_id
                                      from   sales   e,sale_item   f
                                      where   e.order_no=f.order_no)   g
                          group   by   cust_id
                          having   count(distinct   prod_id)> =3)   h   )

--18、查找至少与世界技术开发公司销售相同的客户编号、名称和商品编号、商品名称、数量和金额
select   a.cust_id,cust_name,d.prod_id,prod_name,qty,qty*unit_price
from   customer   a,   product   b,   sales   c,   sale_item   d
where   a.cust_id=c.cust_id   and   d.prod_id=b.prod_id   and  
            c.order_no=d.order_no     and   not   exists
    (select   f.*
      from   customer   x   ,sales   e,   sale_item   f
      where   cust_name= '世界技术开发公司 '   and   x.cust_id=e.cust_id   and
                  e.order_no=f.order_no   and   not   exists
                      (   select   g.*
                          from   sale_item   g,   sales     h
                          where   g.prod_id   =   f.prod_id   and   g.order_no=h.order_no   and
                                      h.cust_id=a.cust_id)
        )
     
19、查找表中所有姓刘的职工的工号，部门，薪水
select   emp_no,emp_name,dept,salary
from   employee
where   emp_name   like   '刘% '

20、查找所有定单金额高于20000的所有客户编号
select   cust_id
from   sales
where   tot_amt> 20000

21、统计表中员工的薪水在40000-60000之间的人数
select   count(*)as   人数
from   employee
where   salary   between   40000   and   60000

22、查询表中的同一部门的职工的平均工资，但只查询＂住址＂是＂上海市＂的员工
select   avg(salary)   avg_sal,dept  
from   employee  
where   addr   like   '上海市% '
group   by   dept

23、将表中住址为 "上海市 "的员工住址改为 "北京市 "
update   employee    
set   addr   like   '北京市 '
where   addr   like   '上海市 '

24、查找业务部或会计部的女员工的基本信息。
select   emp_no,emp_name,dept
from   employee  
where   sex= 'F 'and   dept   in   ( '业务 ', '会计 ')

25、显示每种产品的销售金额总和，并依销售金额由大到小输出。
select   prod_id   ,sum(qty*unit_price)
from   sale_item  
group   by   prod_id
order   by   sum(qty*unit_price)   desc