代码之家 › 专栏 › 技术社区 › Shimmy Weitzhandler 500 - Internal Server Error

获取组中的行索引?

sql tsql

Shimmy Weitzhandler 500 - Internal Server Error · 技术社区 · 14 年前

我有两张桌子:

Unit:
UnitId int PK
Title varchar

UnitOption:
UnitOptionId int PK
UnitId int FK
Title varchar

Quote:
QuoteId int PK
UnitOptionId int FK
Title varchar

我想创建一个标量UDF,它接受一个QuoteID参数并返回一个包含以下描述(pseudu)的varchar:

Quote.Title + '-' + Unit.Title + '-' + Unit.UnitId + 
/* Here is where my question is:
If there are more than 1 UnitOption under this Unit, then
  return '-' + the UnitOption number under this Unit
  (i.e.) if under this Unit, there are 3 UnitOption with IDs 13, 17, 55
  under the unit, and the current Quote.UnitOptionId is the 17 one,
  it should return 2.
  Which means I want to retrieve an ID of this row in the group.
Else
  return ''
*/

3 回复 | 直到 14 年前

Daniel Renshaw 14 年前

如果您使用的是SQL 2005或更高版本,并且我已经正确地解释了您的问题,那么您应该能够将以下内容调整到您的函数中。

WITH [UnitExt] AS
(
    SELECT
        [Unit].[UnitId],
        [Unit].[Title],
        COUNT(*) AS [Count]
    FROM [Unit]
        INNER JOIN [UnitOption] ON [UnitOption].[UnitId] = [Unit].[UnitId]
    GROUP BY
        [Unit].[UnitId],
        [Unit].[Title]
)

SELECT
    [Quote].[Title] + '-' + [UnitExt].[Title] + '-' + [UnitExt].[UnitId] + 
        CASE
          WHEN [UnitExt].[Count] > 1 THEN '-' +
              CAST([UnitOption].[UnitOptionId] AS varchar(max))
          ELSE ''
      END
FROM [Quote]
    INNER JOIN [UnitOption] ON [UnitOption].[UnitOptionId] =
        [Quote].[UnitOptionId]
    INNER JOIN [UnitExt] ON [UnitExt].[UnitId] = [UnitOption].[UnitId]
WHERE [Quote].[QuoteId] = @QuoteId

erikkallen 14 年前

像这样的事情应该可以做到。

SELECT DISTINCT Quote.Title +
       ' - ' + Unit.Title +
       ' - ' + Unit.UnitId +
       CASE
           WHEN COUNT(*) OVER(PARTITION BY Quote.Id) > 0
       THEN
           ' - ' + CAST(ROW_NUMBER() OVER (PARTITION BY Quote.Id ORDER BY Quote.UnitOptionId) AS varchar)
       ELSE
           ''
       END
  FROM Quote
  JOIN UnitOption ON UnitOption.Id = Quote.UnitOptionId
  JOIN Unit ON Unit.Id = UnitOption.UnitId
 WHERE Quote.Id = @QuoteId

Shimmy Weitzhandler 500 - Internal Server Error 14 年前

CREATE FUNCTION ufnGetDescription 
  (@QuoteID INT) 
RETURNS VARCHAR(MAX) 
AS 
  BEGIN 
  DECLARE @RetVal VARCHAR(MAX); 

  WITH CurRow 
  AS (SELECT quote.title + '- ' + unit.title AS start, 
    u.unitid, 
    quoteid, 
    uo.unitoptionid 
  FROM quote 
    INNER JOIN unitoption uo 
    ON quote.unitoptionid = uo.unitoptionid 
    INNER JOIN unit 
    ON uo.unitid = unit.unitid 
  WHERE quote.quoteid = @QuoteID), 
  AllUnits 
  AS (SELECT u.unitid, 
    uo.unitoptionid, 
    Row_number() 
    OVER(PARTITION BY u.unitid ORDER BY uo.unitoptionid) AS NUMBER, 
    Count(* ) 
    OVER(PARTITION BY u.unitid )  AS cntUnits 
  FROM unit 
    INNER JOIN unionoption uo 
    ON unit.unitid = uo.unitid 
  WHERE u.unitid IN (SELECT unitid 
      FROM CurRow)) 
  SELECT @RetVal = CASE 
    WHEN a.cntUnits = 1 THEN '' 
    ELSE r.start + '-' + Cast(NUMBER AS VARCHAR(max)) 
    END 
  FROM AllUnits a 
  INNER JOIN CurRow r 
  ON a.unitoptionid = r.unitoptionid 

  RETURN @RetVal 
END