当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - Using MAX in a SELECT statement with logic

问题描述:

In a nutshell, I need to get the latest revision of a part. The criteria for determining the latest revision is;

  • If all the parts have an alpha revision, then the latest revision based upon the MAX alphabet
  • If all the parts have a numeric revision, then the latest revision based upon the MAX numeric
  • If some of the parts have letter and some have numbers and letters, then the latest revision would be the MAX alphabet
  • If some of the parts have letters, others have numbers, then the latest would the based upon the MAX of the numbers, ignoring the letters

This might seems a bit hazy but should hopefully be clearer with the code below.

DECLARE @PARTS TABLE

(

PART nvarchar(100),

PART_GENERIC nvarchar(100)

)

INSERT INTO @PARTS (PART, PART_GENERIC) VALUES ('B4294A', 'B4294')

INSERT INTO @PARTS (PART, PART_GENERIC) VALUES ('B4294B', 'B4294')

INSERT INTO @PARTS (PART, PART_GENERIC) VALUES ('B4294C', 'B4294')

INSERT INTO @PARTS (PART, PART_GENERIC) VALUES ('B4323001', 'B4323')

INSERT INTO @PARTS (PART, PART_GENERIC) VALUES ('B4323002', 'B4323')

INSERT INTO @PARTS (PART, PART_GENERIC) VALUES ('B4323003', 'B4323')

INSERT INTO @PARTS (PART, PART_GENERIC) VALUES ('N9648400A', 'N96484')

INSERT INTO @PARTS (PART, PART_GENERIC) VALUES ('N96484A', 'N96484')

INSERT INTO @PARTS (PART, PART_GENERIC) VALUES ('A11089100', 'A11089')

INSERT INTO @PARTS (PART, PART_GENERIC) VALUES ('A11089A', 'A11089')

SELECT PART_GENERIC, MAX(PART) FROM @PARTS GROUP BY PART_GENERIC

In the case of rules 1 and 2 above, using the MAX function will get the desired results. The tricky part is the other 2 rules.

The parts are grouped by their generic value above, so I'm looking for the latest revision of each generic part.

I've managed to do this already but it was in a user defined function and is horrendously slow when used in a SELECT statement. I'd therefore like to handle this logic and output a table containing the generic value and the latest revision of the part.

The SELECT statement above will output;

A11089 A11089A

B4294 B4294C

B4323 B4323003

N96484 N96484A

when I need it to output;

A11089 **A11089100**

B4294 B4294C

B4323 B4323003

N96484 N96484A

One final point, if the revision is numeric, it's the last 3 characters of the part (e.g. 003, I know that's not strictly speaking a number). If it's a letter, its typically just the last character, e.g. A

网友答案:

You can try this--it's about the fastest I can make it work:

select
    part_generic,
    isnull(
        max(case when minorRevisionNumeric = 1 then part else null end),
        max(case when minorRevisionNumeric = 0 then part else null end)
    ) as revision
from
    (select
        part,
        part_generic,
        isnumeric(replace(part, part_generic, '')) as minorRevisionNumeric
    from
        @parts
    ) y
group by
    part_generic

I put the isnumeric and replace so you're only doing it once per row, and then use that in the calc for the revision number. It works with the sample data you've provided, but if it's not exactly the right logic for what you're looking for, we can always tweak from there.

For posterity, this syntax will only work on SQL Server.

分享给朋友:
您可能感兴趣的文章:
随机阅读: