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('算法定义')