技术盘点之TSQL

SQL,这个初级程序员必不可少器具，不知不觉一路走来，哦，又一个乱糟糟的家伙！和脚本、ASP是一道的！现在ASP.NET、C#、C++、通史也够花我不少时间，再添一菜实在看着没食欲，吃也消不来，慢慢来。眼见目前的情况，这个SQL是必不可少的，唯有现在耐心细数一下记忆与经验，希望足够喂饱新的工作。

记得有句话这样说：Talents come from diligence, and knowledge is gained by accumulation，即天才来自勤奋，智慧来自积累。半路出家，虽没有系统走过SQL，但也有不少作业经验，积起也成“智慧”。

 

（本帖有部分代码来源自过往的工作，标识基本上改掉，基本上属于工作时即兴练习，并无机密可言，现作知识积累，如有您觉得有“版权”问题，请与我联系）

1.    一些话... 1

2.    一个简单的循环例子... 1

3.    一个简单的综合查询... 2

4.    一个按日期生成流水号的例子... 4

5.    统计系统交易情况... 6

6.    统计会员收到的各种物品... 7

7.    统计会员点值使用情况并报告数据的正确性... 7

8.    收回会员赠出去的非法点值... 8

 

1.      一些话

Transact-SQL是标准SQL的MS的扩展实现。T-SQL不是SQL，怎么说呢，SQL是非过程化的数库语言，也就是说SQL没有if、WHIEL、switch等flow control语句。过程代操作由各厂商自己的DBMS实现，比如现在说的MS SQL SERVER里的T-SQL。

TSQL与编译语言相比却更像脚本语言，不显式编译而依赖宿主执行。TSQL脚本特色在语法上表现在掺杂了SQL指令（如SELECT出来的字段值可给变量赋值），这个也体现了TSQL的主要用途—过程化数据库操作而来的。

TSQL的语法特色：

n         变量@

n         系统变量@@

n         声明DECLARE

n         单引字符串，加号连接

n         赋值SET,SELECT

n         语句块BEGIN……END

2.      一个简单的循环例子

实现目标：生成一个密码字符数据待用，用于随机生成密码

实现思路：穷举所有字符填进一张临时表待用。字符不可控制循环，其ASCII码可以

实现难点：

技术要点：

1.       循环控制WHILE……CONTINUE

2.       字符转ASCII码转换函数ASCII()

3.       ASCII码转字符转换函数CHAR()

4.       逻辑操作符IN

DECLARE @min         AS integer
DECLARE @max         AS integer
DECLARE @charCode    AS integer
SET @min = ASCII('1')
SET @charCode = @min
SET @max = ASCII('z')
WHILE @charCode <= @max
  BEGIN

--排除掉一些不用作密码的字符
    IF CHAR(@charCode) IN (':',';','<','=','>','?','[','/',']','^','_','`')
       BEGIN
           SET @charCode = @charCode + 1
           CONTINUE
       END

       PRINT CHAR(@charCode)
       INSERT INTO PasswordChar(CodeChar) VALUES (CHAR(@charCode))
       SET @charCode = @charCode + 1
  END

3.      一个简单的综合查询

实现目标：一个典型的综合查询，由客户点选条件进自定查询

实现思路：根据过程的输入参数的有无，构造过滤条件WHERE子句，最后用系统过程sp_executesql执行。

实现难点：有些数据类型的字段不好作判断，如只有二值的布尔类型，无法决定是否跳过布尔字段过滤（不过还可以NULL啊？此代码有待优化改进）

技术要点：

1.         条件分支IF ELSE

2.         类型转换函数CAST

3.         执行SQL字串系统过程sp_executesql

4.         错误处理@@ERROR

5.         时间过滤CAST(@InTimeUBound AS integer)

/*=================================
  object name: CS_SelectForSearching
  INUPUT:
  OUTPUT: RETURN_VALUE
  DESCRIPTION:简单的综合查询
  RELATED TABLE: CustomerServices
  AUTHOR: Kemin
  DATE: 2005-10-28
  MODIFIED:
===================================*/
ALTER PROCEDURE CS_SelectForSearching(
  @CSId             integer,           --订单ID
  @ServicesName     varchar(50),       --服务名称
  @MemberNo         varchar(15),       --会员号
  @CustomerName     varchar(15),       --会员名字
  @InTimeUBound     datetime,      --下单时间上界
  @InTimeLBound     datetime,      --下单时间下界
  @CustomerTitle        tinyint,           --会员称谓
  @IsInvoice            integer,       --是否开发票,0-flase,1-true,2-不使用该字段作过滤条件
  @IsPayed          integer        --是否已经付款,0-flase,1-true,2-不使用该字段作过滤条件
)
AS
DECLARE @SelectFromSQL nvarchar(1000)
DECLARE @WhereClause nvarchar (1000)
SET @WhereClause  = ' WHERE cd.isAvailable = 1 '    --一个已知的过滤条件是AND的基石
IF @CSId > 0  --文哥一时想不到该怎么做值比较，这个问题有待研究
  SET @WhereClause = @WhereClause + ' AND cd.CustomerServicesId = ' + CAST(@CSId AS nvarchar)
IF @ServicesName <> ''
  SET @WhereClause = @WhereClause + ' AND s.ServicesName LIKE ''%' + @ServicesName + '%'''
IF @MemberNo <> ''
  SET @WhereClause = @WhereClause + ' AND m.MemberNo LIKE ''%' + @MemberNo  + '%'''
IF @CustomerName <> ''
  SET @WhereClause = @WhereClause + ' AND c.CustomerName LIKE ''%' + @CustomerName + '%'''
IF @InTimeUBound <> CAST('1980-6-25' AS datetime)
  SET @WhereClause = @WhereClause + ' AND CAST(cd.InTime AS integer) >= ' + CAST(CAST(@InTimeUBound AS integer) AS nvarchar)
IF @InTimeLBound <> CAST('1980-6-25' AS datetime)
  SET @WhereClause = @WhereClause + ' AND  CAST(cd.InTime AS integer) <= ' + CAST(CAST(@InTimeLBound AS integer) AS nvarchar)
IF @CustomerTitle > 0
  SET @WhereClause = @WhereClause + ' AND c.CustomerTitle = ' + CAST(@CustomerTitle AS nvarchar)
IF @IsInvoice < 2--被迫使用整数类型,0-flase,1-true,2-不使用该字段作过滤条件
  SET @WhereClause = @WhereClause + ' AND cd.IsInvoice = ' + CAST(@IsInvoice AS nvarchar)
IF @IsPayed  < 2 --被迫使用整数类型,0-flase,1-true,2-不使用该字段作过滤条件
  SET @WhereClause = @WhereClause + ' AND cd.IsPayed = ' + CAST(@IsPayed AS nvarchar)
SET @SelectFromSQL  = 'SELECT   cd.CustomerServicesId AS 单号,' +
        's.ServicesName AS 服务内容,' +
        'c.CustomerName AS 客户, ' +
        'cd.Amount AS 数量, ' +
        'cd.Total AS 价格, ' +
        'cd.InTime AS 收件时间, ' +
        'cd.PrePay AS 预付, ' +
        '(CASE WHEN cd.isPayed = 1 then ''没付齐'' else ''已付齐'' end) AS 已经付齐款, ' +
        'c.CustomerServicesNo AS 流水号,  ' +
        'e.EmployeeName AS 员工' +
      ' FROM CustServicesDetail cd ' +
      ' INNER JOIN CustomerServices c ON' +
      '  cd.CustomerServicesId = c.CustomerServicesId' +
      ' INNER JOIN Services s ON' +
      '  cd.ServicesId = s.ServicesId' +
      ' INNER JOIN Employee e ON' +
      '  c.EmployeeId = e.EmployeeId' +
      ' INNER JOIN Customer m ON' +
      '  c.CustomerId = m.CustomerId'

  SET @SelectFromSQL = @SelectFromSQL + @WhereClause
  --PRINT @SelectFromSQL
  EXEC sp_executesql @SelectFromSQL

  IF @@ERROR != 1
    RETURN 1
  ELSE
    RETURN 0

4.      一个按日期生成流水号的例子

实现目标：为订单生成流水号，如20051009A0010，一眼看出日期、店号和序号。

实现思路：创建一个流水序表保存每天的全局序号“变量”，每次开单读取并更新

实现难点：很明显20051009A0010数据只能为字符型，后面的两种数据—店号和序号的构造比较死板。

技术要点：

1. 时间比较函数DATEDIFF查找今天的序号

2. 系统全局变量@@ROWCOUNT

3. 输出参数

4. 过程调用过程EXEC GetNewSerial @NewSerial OUTPUT

5. 多路分支CASE WHEN

6. 系统全局变量@@IDENTITY

 

/*=================================
  object name: GetNewSerial
  INUPUT:none
  OUTPUT: @NewSerial
  DESCRIPTION:生成序列号
  RELATED TABLE: SerialNumber
  AUTHOR: Kemin
  DATE: 2005-10-08
  MODIFIED:
===================================*/
ALTER   PROCEDURE GetNewSerial(
  @NewSerial integer OUT
)
AS
DECLARE @SerialNumberId AS integer
--先看有没有序号或今天的序号
SELECT @SerialNumberId = SerialNumberId, @NewSerial = Serial FROM SerialNumber WHERE DATEDIFF(day, AddTime, GETDATE()) = 0
IF @@ROWCOUNT = 0 BEGIN  --这个@@ROWCOUNT属系统函数，返回受上一语句影响的行数。
--无则创建
  INSERT   INTO SerialNumber(Serial)
    VALUES(1)
  SET @NewSerial = 1

END
ELSE BEGIN
--有则累加
  UPDATE SerialNumber SET Serial = Serial + 1 WHERE SerialNumberId = @SerialNumberId
  SET @NewSerial = @NewSerial + 1
END

  IF @@error != 1
    RETURN 1
  ELSE
    RETURN 0

GO


/*=================================
  object name: CS_GenerateNewOrder
  INUPUT: CS Entity
  OUTPUT: @CustomerServicesId
  DESCRIPTION:开新单，填好假单号，返回只有单号的的空单
  RELATED TABLE: CustomerServices
  AUTHOR: Kemin
  DATE: 2005-10-11
  MODIFIED:
===================================*/
ALTER  PROCEDURE CS_GenerateNewOrder (
  @CustomerServicesId  integer  OUTPUT

  )
AS

DECLARE @NewOrderId AS varchar(50)
DECLARE @NewSerial AS integer
--生成新流水号 形如：'20051009A0010'
DECLARE @DateString AS varchar(8)
DECLARE @OOString AS varchar(8)
SET @DateString = CAST(YEAR(GETDATE()) AS varchar) + CAST(MONTH(GETDATE()) AS varchar) + CAST(DAY(GETDATE()) AS varchar)
EXEC GetNewSerial @NewSerial output

--N笨的方法，暂时想不到。填充序号前的0位
--TSQL的CASE竟然有返回值！！
SET @OOString =
  CASE LEN(@NewSerial)
     WHEN 1 THEN '000'
     WHEN 2 THEN '00'
     WHEN 3 THEN '0'
     ELSE ''
  END

SET @NewOrderId = @DateString + 'B' + @OOString + CAST(@NewSerial AS varchar)

  INSERT INTO CustomerServices(
    CustomerServicesNo,
    CustomerId,
    CustomerName,
    CustomerTitle,
    Pay,
    Tel,
    Total,
    EmployeeId,
    Note,
    isAvailable
   )
  VALUES(
    @NewOrderId,
    0,
    '',
    0,
    0,
    '',
    0,
    0,
    '',
    1
         )
  SET @CustomerServicesId = @@IDENTITY
  IF @@error != 1
    RETURN 1
  ELSE
    RETURN 0
GO

5.      统计系统交易情况

实现目标：报告系统交易全情，包括了交易参与者、交易物件以及交易量

实现思路：。。

实现难点：User与UserTrade是N对二的情报型关系，内联时只能得到一个会员的名字信息。

技术要点：

1.         内联三张表

2.         GROUT BY子句分组统计，注意没有聚合的字段必须进行分组

SELECT GiftName      AS '礼品'

, COUNT(UserTradeId) AS '数量'

, Presenter          AS '赠送人ID'

, u.UserName         AS '被赠送人'
FROM UserTrade AS ut
INNER JOIN User AS u ON ut.Reciever = u.UserId
INNER JOIN Gift AS g ON ut.GiftId = g.GiftId
WHERE Presenter BETWEEN 58555 AND 60000
GROUP BY GiftName, Presenter, u.User

6.      统计会员收到的各种物品

实现目标：统计会员收到的各种物品。

实现思路：

实现难点：。

技术要点：

1.         GROUT BY子句分组统计，注意没有聚合的字段必须进行分组

2.         物品分类统计

 

SELECT u.UserName AS "会员",
  [1朵鲜花] = SUM(CASE GiftId WHEN 1 THEN Amount ELSE 0 END),
  [9朵鲜花] = SUM(CASE GiftId WHEN 2 THEN Amount ELSE 0 END),
  [99朵鲜花] = SUM(CASE GiftId WHEN 3 THEN Amount ELSE 0 END),
  [钻戒] = SUM(CASE GiftId WHEN 4 THEN Amount ELSE 0 END),
  [别墅] = SUM(CASE GiftId WHEN 5 THEN Amount ELSE 0 END)
FROM UserTrade AS ut

INNER JOIN User AS u ON ut.Reciever = u.UserId
WHERE AddTime BETWEEN '2005-05-01' AND '2005-05-02'
GROUP BY u.UserName

7.      统计会员点值使用情况并报告数据的正确性

实现目标：一些数据分开多个表保存，希望统计到一起

实现思路：典型统计

实现难点：一些数据分开多个表保存，需要嵌套

技术要点：

1.         SELECT字段列表的数据聚合和计算

SELECT ut.Presenter      AS "会员ID"
       , u.UserName      AS "会员昵称"
       ,ua.Charge        AS "会员充卡总点数"
       , SUM(g.Point)    AS "赠送出去礼品总点数"
       ,ua.Consume       AS "会员消费总点数"
       ,(
       SELECT SUM(Amount*Point)
       FROM MyGift AS mg
       INNER JOIN Gift AS g ON mg.GiftId = g.GiftId
       WHERE UserId = ut.Presenter
       )                 AS "会员现持礼品总点数"
       ,(
       CASE(ua.Consume-
            (
            SELECT SUM(Amount*Point)
            FROM MyGift AS mg
            INNER JOIN Gift AS g ON mg.GiftId = g.GiftId
            WHERE UserId = ut.Presenter)
            )
            WHEN  SUM(g.Point) THEN '数据正确'ELSE '数据不正确' END
       )                AS "结果"
FROM UserTrade AS ut
     INNER JOIN Gift AS g ON ut.Gift = g.GiftId
     INNER JOIN UserAccount AS ua ON ut.Presenter = ua.User
     INNER JOIN User AS u ON ut.Presenter = u.UserId
GROUP BY ut.Presenter, ua.Consume, u.UserName, ua.Charge

测试

140009 BT  8529700    4402090    8529700    0   数据不正确

 

8.      收回会员赠出去的非法点值

实现目标：交易系统数据异常混乱，完整性与正确性保证工作烦锁。

实现思路：先删除最新交易的记录，触发删除触发器修正点值，再比较点值与目标点值，如此循环下去。

实现难点：涉及的数据较多，考虑要细

技术要点：

1.         业务逻辑错误处理（使用了RAISEERROR，但还不知具体何用）

2.         事务处理

3.         触发器的使用

 

/*=================================
  OBJECT NAME: revoke_Dot
  INUPUT: @MemberId, @Dot_Target
  OUTPUT: RETURN_VALUE
  DESCRIPTION:收回会员赠出去的非法点值，根据会员ID恢复非法操作前的点值Dot_Target
  RELATED TABLE: 
  AUTHOR: Kemin
  DATE: 2005-5-26
  MODIFIED:
===================================*/

CREATE PROCEDURE revoke_Dot (
@MemberId  varchar(15),
@Dot_Target  bigint,
@Msg varchar(255) = '' OUT
)AS

IF @MemberId = '' OR @Dot_Target = '' BEGIN
  SET @Msg = '2.请求数据有误'
  RAISERROR(@Msg, 16, 1)
  RETURN 2
END

DECLARE @DOT_Gift_OUT AS Bigint --赠送礼品总点值数
DECLARE @DOT_Gift_KEPT AS Bigint --现持礼品总点值数
DECLARE @DOT_OUT_All AS Bigint --现消费总点值数 = 赠送礼品总点值数 + 现持礼品总点值数

IF NOT EXISTS(SELECT UserId FROM User WHERE UserId = @MemberId) BEGIN
  SET @Msg = '3.无此用户'
  RAISERROR(@Msg, 16, 1)
       RETURN 3
END
ELSE
  BEGIN
    --1.赠送礼品总点值数
    SELECT @DOT_Gift_OUT = SUM(Point)
      FROM UserTrade AS ut
INNER JOIN Gift AS g
        ON ut.Gift = g.GiftId
     WHERE Presenter = @MemberId
    --2.现持礼品总点值数
    SELECT @DOT_Gift_KEPT = SUM(Amount*Point)
      FROM MyGift AS mg
INNER JOIN Gift AS g
        ON mg.Gift = g.GiftId
    WHERE UserId = @MemberId
    --3.现在会员消费总点值 = 现持礼品总点值数 + 赠送礼品总点值数
    SET @DOT_OUT_ALL = @Dot_Gift_OUT + @DOT_Gift_Kept
    --4.先看看是否能预期收回目标点数，因为有部分点数是会员持有礼品没赠送的不能收
    IF @Dot_Target > @Dot_Gift_OUT BEGIN
      SET @Msg = '7.收回点数大于会员'+ @MemberId +'赠送的点数！'
      RAISERROR(@Msg, 16, 1)
      RETURN 7
      END
    ELSE
    BEGIN TRANSACTION

    --判断消费总额是否大于预期的数
    --如果是，删掉最近的一条赠送记录，循环继续
    WHILE @Dot_Gift_Out > @Dot_Target BEGIN
      DELETE UserTrade
      FROM (SELECT TOP 1 TradeId
              FROM UserTrade
             WHERE Presenter = @MemberId
          ORDER BY AddTime DESC) AS A
      WHERE A. TradeId = UserTrade.TradeId
      --charge_mst.expenditure的值被gift_incept的删除触发器同步数据,请看《Kemin第一触发器》--http://blog.csdn.net/keminlau/archive/2005/05/27/382088.aspx
      SELECT @Dot_Gift_Out = Consume
        FROM UserAccount
       WHERE UserId = @MemberId
    END
    --否则结束recursive
    END

    IF @@ERROR != 0 BEGIN
      ROLLBACK TRANSACTION
      SET @Msg = '6服务器执行错误'
      RAISERROR(@Msg, 16, 1)
      RETURN 6
    END
    COMMIT TRANSACTION
    SET @Msg = '点值收回成功！'
    RETURN 0
GO