SQL Server -- 随笔

-- 判断是否存在 LimeNextMonthBirthday 表

IF EXISTS  (SELECT  1 FROM dbo.SysObjects WHERE ID = object_id(N'[LimeNextMonthBirthday]') AND OBJECTPROPERTY(ID, 'IsTable') = 1)

PRINT '存在'

ELSE

PRINT'不存在'

-- 如果存在 则删除 没有 则打印 不存在

IF EXISTS  (SELECT  1 FROM dbo.SysObjects WHERE ID = object_id(N'[LimeNextMonthBirthday]') AND OBJECTPROPERTY(ID, 'IsTable') = 1)

DROP TABLE LimeNextMonthBirthday

ELSE

PRINT 'no exists';

-- 创建表 Id 为自增 增量为1

CREATE TABLE LimeNextMonthBirthday (

    Id INT NOT NULL IDENTITY(1,1),

    Oid VARCHAR (40),

    ImsCustomerClerk VARCHAR (40),

    ImsCustomerCode VARCHAR (40),

    ImsCustomerShort VARCHAR (40),

    ImsEmployeeContractType INT,

    ImsEmployeeCode VARCHAR (40),

    ImsEmployeeName VARCHAR (40),

    ImsEmployeeCid VARCHAR (20),

    ImsEmployeeBirthday DATE,

    ImsEntryDate DATE,

    ImsEmployeeStatus INT,

    ImsQuitDate DATE,

    ImsCustomerName VARCHAR (200),

    service_product VARCHAR (100)

);

-- 分页 带输入参数的存储过程

if (object_id('page_with_selectMax', 'P') is not null)

    drop proc page_with_selectMax

go

CREATE PROCEDURE page_with_selectMax(@pageNum INT)

AS

SELECT li.*

FROM(

        SELECT

            row_number() OVER (ORDER BY o.ImsEmployeeCid ASC) AS rownumber ,o.*

        FROM

            (SQL) AS o

) AS li

        WHERE

            li.rownumber > (@pageNum - 1) * 10 AND li.rownumber <= @pageNum * 10;

GO

EXEC page_with_selectMax 1

-- Hive2中实现 GROUP_CONCAT() 函数

SELECT

    imscityorgid,

    concat_ws (

        " ",

        collect_set (imsserviceitemtypename)

    )

FROM

    imsserviceitemtype

GROUP BY

    imscityorgid;

-- 查找 指定字段 所属表

SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='要查的字段名'

-- 呵呵呵

select count(1) from syscolumns where id = object_id('算法定义')

啦啦啦

啦啦啦

啦啦啦