首页 > 代码库 > 工作中常用SQL 查询语句备忘

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

2024-07-11 09:07:44   225人阅读

--当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 ONGO SET QUOTED_IDENTIFIER ONGO 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]
View Code

 


联系
我们