Question

我试图宣读一份特定声明,将外地价值降至100个,并在最后添加......这样:

数百个自由会变成数百个页: 1 这里是我所储存程序的选定说明。这里的我 kind。选择性的《东方木材协定》第(1)条是我想做的。

SELECT      [TI].[TicketID]
        ,   CAST([TI].[Subject] AS VARCHAR(100)) [Subject]
        ,   [TA].[DueDate]
        ,   CAST(
                    (   SELECT 
                        TOP(1)      SUBSTRING(Description, 0, 100)
                        FROM        Comment 
                        WHERE       TicketID = [TI].[TicketID] 
                        ORDER BY    CommentDate DESC
                    ) AS VARCHAR(100)
                ) AS [Description]
FROM        [dbo].[Ticket]      [TI]
INNER JOIN  [dbo].[Task]        [TA] 
ON          [TI].[TicketID]     = [TA].[TicketID]
INNER JOIN  [dbo].[Task_Status] [TS] 
ON          [TA].[StatusID]     = [TS].[StatusID]
WHERE       [TI].[IsDeleted]    = 0 
AND         [TS].[IsDeleted]    = 0
AND         [TS].[Status]       =  Open 
AND         [TI].[AssigneeView] IS NULL
AND         [TI].[AssignedTo]   = @AssignedTo

Answer 1

工作要做的是:

SELECT CASE 
           WHEN (LEN(Table1.LongText) < 100) THEN Table1.LongText
           ELSE SUBSTRING(Table1.LongText,1, 97) +  ... 
       END AS [Description]
FROM Table1;

Answer 2

我认为,这里的最佳方法是创建(UDF)。

在卢旺达国防军中,你有这样的东西:

IF LEN(@Value)>100
BEGIN
   SET @Value = SUBSTRING(@Value, 0, 100) +  ... 
END
RETURN @Value

在您的<代码>SlectT上,您将:

SELECT [TI].[TicketID], dbo.TruncateString([TI].[Subject]) .....

Answer 3

do you need all fields to have ... or only the ones who are actually bigger than 100 chars. If you need all, you can do something like:

select substring(name,0,97)+ ...  from...

Answer 4

;WITH x (d, tid, rn) AS
(
  SELECT SUBSTRING(Description, 1, 100) + CASE
    WHEN LEN(Description) > 100 THEN  ...  ELSE    END,
    TicketID, rn = ROW_NUMBER() OVER
    (PARTITION BY TicketID ORDER BY CommentDate DESC)
    FROM dbo.Comment
)
SELECT [TI].[TicketID], 
  CAST([TI].[Subject] AS VARCHAR(100)) [Subject], 
  [TA].[DueDate], 
  x.d AS [Description]
    FROM     [dbo].[Ticket] [TI]
             INNER JOIN [dbo].[Task] [TA] ON [TI].[TicketID] = [TA].[TicketID]
             INNER JOIN [dbo].[Task_Status] [TS] ON [TA].[StatusID] = [TS].[StatusID]
    LEFT OUTER JOIN x ON TI.TicketID = x.tid
    AND x.rn = 1 -- oops, forgot this important part
    WHERE    [TI].[IsDeleted] = 0 AND
             [TS].[IsDeleted] = 0 AND
             [TS].[Status] =  Open  AND
             [TI].[AssigneeView] IS NULL AND
             [TI].[AssignedTo] = @AssignedTo;

友情链接