工作中常用SQL 查询语句备忘

浏览数：33 / 时间：2015年06月12日

--当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列。
select  (case when a>b then a else b end ),
        (case when b>c then b esle c end)
        from table_name

--求和查询

create table #tmp(rq varchar(10), shengfu nchar(1))

insert into #tmp values(‘2005-05-09‘,‘胜‘)
insert into #tmp values(‘2005-05-09‘,‘胜‘)
insert into #tmp values(‘2005-05-09‘,‘负‘)
insert into #tmp values(‘2005-05-09‘,‘负‘)
insert into #tmp values(‘2005-05-10‘,‘胜‘)
insert into #tmp values(‘2005-05-10‘,‘负‘)
insert into #tmp values(‘2005-05-10‘,‘负‘)

--方法一
select    rq, 
        sum(case when shengfu=‘胜‘ then 1 else 0 end) as ‘胜‘,
        sum(case when shengfu=‘负‘ then 1 else 0 end) as ‘负‘ 
        from #tmp group by rq
        
--方法二        
select  N.rq, N.勝, M.負 from 
        (select rq, 勝 = count(*) from #tmp where shengfu = ‘胜‘ group by rq)N 
        inner join
        (select rq, 負 = count(*) from #tmp where shengfu = ‘负‘ group by rq)M 
        on N.rq = M.rq
        
drop table #tmp

--取出表中日期(SendTime字段)为当天的所有记录        
select * from tb_name where datediff(dd, SendTime, GETDATE())=0

--查询成绩表
select
    (case when 语文>=80 then ‘优秀‘ 
          when 语文>=60 then ‘及格‘
          else ‘不及格‘) as 语文,
                   
    (case when 英语>=80 then ‘优秀‘
          when 英语>=60 then ‘及格‘
          else ‘不及格‘) as 英语
          
from tb_name

用户临时表（#）只对创建这个表的用户的Session可见，对其他进程是不可见的，当创建它的进程消失时这个临时表就自动删除；全局临时表（##）对整个SQL Server实例都可见，当所有访问它的Session都消失的时候，它也自动删除。

--创建一个简单的学生信息表（Student）
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[Student]
(
    [ID] [int] NOT NULL,
    [Name] [nvarchar](50) NULL,
    [Age] [int] NULL,
    [Grade] [nvarchar](50) NULL,
    [Address] [nvarchar](50) NULL,
    CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED([ID] ASC)
    WITH(    PAD_INDEX  = OFF, 
            STATISTICS_NORECOMPUTE  = OFF, 
            IGNORE_DUP_KEY = OFF,
            ALLOW_ROW_LOCKS  = ON, 
            ALLOW_PAGE_LOCKS  = ON
        ) 
        ON [PRIMARY]
)ON [PRIMARY]