SQL 常用脚本

查询常用：

---查询20条客户的购买订单记录（带个人信息）

SELECT  top 20                    --- 取前20条数据
ROW_NUMBER() OVER ( ORDER BY a.id ) AS 序号 ,   ---查询时，新增序号列
        RealName AS 真实姓名 ,    ---AS 的意思 是将原列名重新命名为‘真实姓名’
        *                         ---查询两个表所有字段
		
FROM   	dbo.[Order] AS b          ---订单表
        LEFT JOIN  dbo.Member AS a  ON a.Id = b.MemberId   ---左关联 个人信息表，关联条件：客户Id （左关联：有个人信息就带出来，没有就放空）
---where ：带条件查询
WHERE   a.AddTime > '2018-01-01'  ---创建时间> '2019-01-01'
        AND Nation LIKE '%汉%'    ---模糊查询：字段里值带有‘汉’字的
---order by 排序
ORDER BY a.Id ,
        NickName DESC             ---根据ID，和昵称倒序排序 （正序排序 ASC）

	
---------------------------------------------------------------------------------

----查询订单表用户购买次数
SELECT  ReceiveName AS 姓名 ,
        MemberId AS 用户ID ,
        COUNT(1) AS 购买次数   
FROM    dbo.[order]
GROUP BY MemberId ,      ----分组 把订单表按用户的ID，收件人分组
        ReceiveName  	

-----------------------------------------------------------
--- 查询日期：

  select CONVERT(varchar(7), getdate() , 120) + '-1'   
---获取当月1号

 SELECT DATEADD(ms, -3, DATEADD(mm, DATEDIFF(m, 0, getdate()) + 1, 0)) 
 ----获取当月月底 例如2017-08-31号23:59:59

SELECT  left(convert(nvarchar(10),dateadd(m,-1,getdate()),120),8)+'21'   
-----获取上月21日  

-------------------------------------------------------------------

----创建临时表并插入数据
---方法1：
---创建：
    CREATE TABLE #T
    (
      id INT NOT NULL ,
      NAME NVARCHAR(50)
    )

---插入：
     INSERT INTO #T
        ( id, NAME )
 VALUES ( 0, -- id - int
          N'3r23t4te'  -- NAME - nvarchar(50)
          ) 
 INSERT INTO #T
        ( id ,
          NAME
        )
        SELECT  SortNo ,
                CnName
        FROM    dbo.QuestionReportType


---第二种方法：
---创建并插入数据
SELECT SortNo ,
        CnName
 INTO   #T1
 FROM   dbo.QuestionReportType

更新或删除操作：

---删除表里字段DiscountStatus
ALTER TABLE fund.BusinessTicketFormItem DROP COLUMN DiscountStatus

   ---增加表里某个字段
ALTER TABLE ReportDB.dbo.EntryFlowTracking ADD TotalTime DECIMAL(10,2)

---字段改名（DiscountStatus改为DiscountState）
exec sp_rename  N'fund.LetterOfCreditFormItem.DiscountStatus','DiscountState','COLUMN'

---修改替换某字段的值
UPDATE fund.BusinessTicketFormItem SET DiscountState = REPLACE(DiscountState,'50','2') WHERE DiscountState LIKE '50'

---删除表里所有的数据
DELETE FROM fund.DiscountReceiptForm  where 1=1

---修改字段属性   （score原本是decimal（18,4）类型）
ALTER TABLE qn.QUESTION_ANSWER ALTER COLUMN SCORE DECIMAL(18,2)


---插入数据
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

---修改某字段的值

UPDATE REportDb.qn.OPTION_COPY SET SCORE = 0 WHERE   CODE IN('c676fc52-c366-4977-b6a7-91a3061208d0')

---模糊查询

Select * from Test where Cn_Name LIKE '%1%'（只要带字符为1的都能查到）

‘%1’    以1结尾的数据能查到； ‘1%’ 以1开头的能查到

^  剔除字符，不包含1的数据  ； ‘1_ ‘ 以1开头并且1后面只带一个字符的数据 


--- IN用法

SELECT * FROM SubjectDB.QN.[OPTION] 
WHERE TEMPLATE_CODE IN (
'9e7c4a5e-e906-4541-ba03-7017a60e4f6f',
'da65dda1-5607-4e6a-a4f8-8258d854e3fd',
'c59747ac-9077-4300-9976-5844538d1e11',
'8eadc369-5d08-4225-b9f2-d597f50e21d1',
'046e9ab4-bd25-4ff4-a0d1-a727deb8d29a'
)

--- 计算天数
select datediff(dd,'2015-07-01','2016-01-01')


----数据库脚本编规范

BEGIN TRAN

USE ReportDB

Insert into ...

ROLLBack TRAN ---回滚

COMMIT TRAN ---提交


---去重 查询
     SELECT DISTINCT 
            E_Code ,
            E_LeaveDate
   FROM   dbo.DimissionInformation

---去重 创建序号码，获取序号码都为1的数据
---语法：row_number() OVER (PARTITION BY COL1 ORDER BY COL2)
---表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组---内连续的唯一的)
WITH    T AS ( SELECT   ROW_NUMBER() OVER ( PARTITION BY LOGON_NAME ORDER BY ALL_PATH_NAME ) AS roenumber ,
                        *
               FROM     SubjectDB.dbo.User_Syn
               WHERE    LOGON_NAME IN ( 'liming', 'fanhy' )
             )
    SELECT  *
    FROM    T
    WHERE   roenumber = 1

 

 

 

行转列

例子：成绩表有姓名，月份，成绩，要生成左边的效果

CREATE TABLE StudentScore(NAME NVARCHAR(10),months NVARCHAR(10),score INT)
INSERT INTO  StudentScore VALUES('张三','一月',85)
INSERT INTO  StudentScore VALUES('张三','二月',97)
INSERT INTO  StudentScore VALUES('李四','一月',77)
INSERT INTO  StudentScore VALUES('李四','二月',86)
INSERT INTO  StudentScore VALUES('王五','一月',86)
INSERT INTO  StudentScore VALUES('王五','二月',88)
INSERT INTO  StudentScore VALUES('李白','二月',90)

-- 静态SQL,指月份只有一月和二月

--方法一：
SELECT  NAME AS 姓名,
 MAX(CASE months WHEN '一月' THEN score ELSE 0 END) 一月,
 MAX(CASE months WHEN '二月' THEN score ELSE 0 END) 二月
FROM StudentScore
GROUP BY name
   
    
--方法二(SQL Server2005 以上版本支持)：
select * from StudentScore 
pivot (
max(score) /*行转列后 列的值*/
for months /*需要行转列的列*/ in (一月,二月) ) /*列的值*/
b



--动态SQL，指月份不止一月和二月
--方法一:
declare @sql varchar(8000)
set @sql = 'select name '
select @sql = @sql + ' , max(case months when ''' + months + ''' then score else 0 end) [' + months + ']'
from (select distinct months from StudentScore) as a
set @sql = @sql + ' from StudentScore group by name'
exec(@sql) 


--方法二(SQL Server2005 以上版本支持)：
declare @sqla varchar(8000)
select @sqla = isnull(@sqla + ',' , '') + months from StudentScore group by months
exec ('select * from StudentScore pivot (max(score) for months in (' + @sqla + ')) b')