求助，sql2005中一个困扰我很久的查询~
例如 表table_1：
ID GYLX
1 3j-4b-5x
2 4b-3j-7zp
3 12zp
4 0g
5
GYLX列中允许有空值
我的结果是将 GYLX列中最后一个包含数字0的查出来
或者最后一个包含数字7的结果查询出来
求大侠帮忙查询
[解决办法]
create table table_1(ID int, GYLX varchar(100))
insert into table_1
select 1 ,'3j-4b-5x' union all
select 2 ,'4b-3j-7zp' union all
select 3 ,'12zp' union all
select 4 ,'0g' union all
select 5 ,''
go
select ID,GYLX
from
(
select *,
ROW_NUMBER() over(partition by case when GYLX like '%0%' then 0
when GYLX like '%7%' then 7
end order by id desc) rownum
from table_1
where GYLX like '%0%' or GYLX like '%7%'
)t
where rownum = 1
/*
IDGYLX
40g
24b-3j-7zp
*/
--create function [dbo].[m_getnumberV2.0]
--(
-- @mysql_one nvarchar(200)
--)
--returns varchar(200)
--begin
-- declare @mysql_two varchar(200)
-- declare @sql_one int
-- declare @sql_two int
-- select @sql_one= patindex('%[0-9.]%',@mysql_one)
-- select @sql_two=
-- patindex('%[^0-9.]%',
-- substring(@mysql_one,patindex('%[0-9.]%',@mysql_one),len(@mysql_one)-patindex('%[0-9.]%',@mysql_one)+1))
-- if @sql_two=0
-- begin
-- select @mysql_two= substring (@mysql_one,@sql_one,len(@mysql_one)+1-@sql_one)
-- end
-- else
-- begin
-- select @mysql_two=substring (@mysql_one,@sql_one,@sql_two-1)
-- end
-- return @mysql_two;
--end
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-23 11:13:23
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[table_1]
if object_id('[table_1]') is not null drop table [table_1]
go
create table [table_1]([ID] int,[GYLX] varchar(9))
insert [table_1]
select 1,'3j-4b-5x' union all
select 2,'4b-3j-7zp' union all
select 3,'12zp' union all
select 4,'0g' union all
select 5,null
--------------开始查询--------------------------
select *
from [table_1]
WHERE [dbo].[m_getnumberV2.0](REVERSE([GYLX])) IN (0,7)
----------------结果----------------------------
/*
ID GYLX
----------- ---------
2 4b-3j-7zp
4 0g
*/
create table pard(ID int,GYLX varchar(20))
insert into pard
select 6,'0G-11DX' union all
select 7,'3J-0G' union all
select 8,'3J-0G' union all
select 9,'3J-0G-4ZB'
-- GYLX列中最后一个包含数字0的
select *
from pard
where substring(reverse(GYLX),
patindex('%[0-9]%',reverse(GYLX)),
1)='0'
/*
ID GYLX
----------- --------------------
7 3J-0G
8 3J-0G
(2 row(s) affected)
*/
-- GYLX列中最后一个包含数字4的
select *
from pard
where substring(reverse(GYLX),
patindex('%[0-9]%',reverse(GYLX)),
1)='4'
/*
ID GYLX
----------- --------------------
9 3J-0G-4ZB
(1 row(s) affected)
*/
