这个应该从设计修改,改成两个列,一个数值,一个单位,就很简单了,case when 就可以了,现在需要拆分字符串
select max(cast(replace(a,'A','') as decimal(18,1)) from a where ISNUMERIC(replace(a,'A','')) = 1
create table a(A varchar(20)) insert into a values('0.4mA') insert into a values('0.5mA') insert into a values('1.0A') insert into a values('1.2A') insert into a values('1.6A') goselect a = max(cast(replace(a,'A','') as decimal(18,1))) from a where ISNUMERIC(replace(a,'A','')) = 1drop table a /* a -------------------- 1.6(所影响的行数为 1 行) */
select top 1 列A from tb order by 列A desc,replace(replace(列A,'mA',''),'A','')*1 desc
单位为A的字段值肯定是大于单位为mA的字段值,如何求出列A中的最大值???你的意思是不管是A,还是MA,统一起来求最大值?create table a(A varchar(20)) insert into a values('0.4mA') insert into a values('0.5mA') insert into a values('1.0A') insert into a values('1.2A') insert into a values('1.6A') goselect max(cast(case when charindex('ma',a) > 0 then replace(a , 'ma', '') else replace(a , 'a', '') end as decimal(18,1))) from adrop table a /*
-------------------- 1.6(所影响的行数为 1 行) */
create table a(A varchar(20)) insert into a values('0.4mA') insert into a values('0.5mA') insert into a values('1.0A') insert into a values('1.2A') insert into a values('1.6A') go
select MAX(A)from ( select A= case when CHARINDEX('mA',a.A,0)>0 then CAST(cast(replace(a,'mA','') as decimal(18,5))*0.001 as nvarchar(100)) else cast(replace(a,'A','') as decimal(18,5)) end from a)bdrop table a 不知道还有没有分
是区分A和mA的,如果带A,那么就求出带A中最大的字段值,如果不带A,那么就求出mA中的最大值
create table tb(A varchar(20)) insert into tb values('0.4mA') insert into tb values('0.5mA') insert into tb values('1.0A') insert into tb values('1.2A') insert into tb values('1.6A') goselect top 1 A from tb order by A desc,replace(replace(A,'mA',''),'A','')*1.0 desc /* A -------------------- 1.6A(1 行受影响)
--> 测试数据: [tb2] if object_id('[tb2]') is not null drop table [tb2] create table [tb2] (列A varchar(5)) insert into [tb2] select '0.4mA' union all select '0.5mA' union all select '1.0A' union all select '1.2A' union all select '1.6A'--开始查询 if exists(select 1 from tb2 where ISNUMERIC(replace([列A],'A','')) = 1) select top 1 * from tb2 where ISNUMERIC(replace([列A],'A','')) = 1 order by 列A desc else select top 1 * from tb2 order by 列A desc--结束查询 drop table [tb2]/* 列A ----- 1.6A(1 行受影响)
--> 测试数据: [tb2] if object_id('[tb2]') is not null drop table [tb2] create table [tb2] (列A varchar(5)) insert into [tb2] select '0.4mA' union all select '0.5mA' union all select '1.0A' union all select '1.2A' union all select '1.6A'--开始查询 select top 1 * from tb2 where 1= case when ISNUMERIC(replace([列A],'A',''))=1 then ISNUMERIC(replace([列A],'A','')) else 1 end order by 列A desc--结束查询 drop table [tb2]/* 列A ----- 1.6A(1 行受影响)
create table #a(A varchar(20)) insert into #a values('1.0mA') insert into #a values('1.2mA') insert into #a values('0.1A') insert into #a values('0.2A') insert into #a values('0.6A') goDECLARE @count INT SET @count=0 SELECT @count=COUNT(A) FROM #a WHERE A like '%[^m]A' IF(@count>0) BEGIN SELECT MAX(A) FROM #a WHERE A LIKE '%[^m]A' END ELSE SELECT MAX(A) FROM #a GODROP TABLE #a/*
-------------------- 0.6A(所影响的行数为 1 行) */
SELECT CASE WHEN (SELECT COUNT(A) FROM #a WHERE A LIKE '%[^m]A') >0 THEN (SELECT MAX(A) FROM #a WHERE A LIKE '%[^m]A' ) ELSE (SELECT MAX(A) FROM #a) END AS [MAX] 合======================================================================
这个有错 若是插入一条1.7mA的记录 最大值就不是 1.6A了
貌似where里是不好用case when 还是分2种情况考虑好些
利用Or的特点,如果 1 OR(0或则1)时 即返回结果可以处理A的优先级高于mA,可以采取如下方式处理:1 Code: SELECT MAX(A) FROM ( SELECT A = MAX(A) FROM ( SELECT * FROM #a )A GROUP BY CASE WHEN A LIKE '%[^m]A' THEN 1 ELSE 0 END )B WHERE CASE WHEN A LIKE '%[^m]A' THEN 1 END = 1 OR CASE WHEN A LIKE '%[m]A' THEN 1 END = 1 2 详细测试如下 : 2.0)临时表 CREATE TABLE #a (A varchar(32), id int) 2.1)只有mA的情况: INSERT INTO #a(A) VALUES('0.4mA') INSERT INTO #a(A) VALUES('0.5mA') --结果: A 0.5mA2.2)只有A的情况: INSERT INTO #a(A) VALUES('1.0A') INSERT INTO #a(A) VALUES('1.2A') INSERT INTO #a(A) VALUES('1.6A') --结果: A 1.6A 2.3)有mA和A的情况: DELETE FROM #a WHERE A IN('0.4mA','0.5mA') DELETE FROM #a WHERE A IN('1.0A','1.2A','1.6A') INSERT INTO #a(A) VALUES('0.4mA') INSERT INTO #a(A) VALUES('0.5mA') INSERT INTO #a(A) VALUES('1.0A') INSERT INTO #a(A) VALUES('1.2A') INSERT INTO #a(A) VALUES('1.6A') --结果: A 1.6A
create table test ( name varchar(100) ); insert into test select '0.4mA' union select '0.5mA' union select '1.0A' union select '1.2A' union select '1.6A' select top(1) * from test order by name desc
呵呵,觉得看了11楼的解答,是通过order by来实现的,很简洁,但是是错误的,当加入一条记录,比如:'1.8mA'时,会返回'1.8mA',而不是'1.6A',我修改了一下:declare @table table(vc varchar(20))insert into @table values('0.4mA') insert into @table values('0.5mA')insert into @table values('1.8mA') --增加的记录insert into @table values('1.0A') insert into @table values('1.2A') insert into @table values('1.6A')insert into @table values('90.6A') --增加的记录 insert into @table values('100.6A') --后面必须得转化成数值型, --否则只是按照字符串来排序的话, --90.6A大于100.6Aselect top 1 vc from @table order by (case when charindex('mA',vc)<>0 then 'mA' else 'A' --因为'mA'>'A',所以这里升序排列,'A'的排在'mA'前面 end) asc , --升序排列,'A'排在前面 cast(replace( replace(vc,'mA',''), 'A', '' ) as numeric ) desc --按照值的大小,大的排在前面
既然单位为A的字段值肯定是大于单位为mA的字段值,那就不用考虑mA的字段吧。 select max(列A) from table where 列A not like '%mA%'
insert into a values('0.4mA')
insert into a values('0.5mA')
insert into a values('1.0A')
insert into a values('1.2A')
insert into a values('1.6A')
goselect a = max(cast(replace(a,'A','') as decimal(18,1))) from a where ISNUMERIC(replace(a,'A','')) = 1drop table a /*
a
--------------------
1.6(所影响的行数为 1 行)
*/
insert into a values('0.4mA')
insert into a values('0.5mA')
insert into a values('1.0A')
insert into a values('1.2A')
insert into a values('1.6A')
goselect max(cast(case when charindex('ma',a) > 0 then replace(a , 'ma', '') else replace(a , 'a', '') end as decimal(18,1))) from adrop table a /*
--------------------
1.6(所影响的行数为 1 行)
*/
insert into a values('0.4mA')
insert into a values('0.5mA')
insert into a values('1.0A')
insert into a values('1.2A')
insert into a values('1.6A')
go
select MAX(A)from (
select
A= case when CHARINDEX('mA',a.A,0)>0
then CAST(cast(replace(a,'mA','') as decimal(18,5))*0.001 as nvarchar(100))
else
cast(replace(a,'A','') as decimal(18,5))
end
from a)bdrop table a 不知道还有没有分
是区分A和mA的,如果带A,那么就求出带A中最大的字段值,如果不带A,那么就求出mA中的最大值
insert into tb values('0.4mA')
insert into tb values('0.5mA')
insert into tb values('1.0A')
insert into tb values('1.2A')
insert into tb values('1.6A')
goselect top 1 A from tb order by A desc,replace(replace(A,'mA',''),'A','')*1.0 desc
/*
A
--------------------
1.6A(1 行受影响)
--> 测试数据: [tb2]
if object_id('[tb2]') is not null drop table [tb2]
create table [tb2] (列A varchar(5))
insert into [tb2]
select '0.4mA' union all
select '0.5mA' union all
select '1.0A' union all
select '1.2A' union all
select '1.6A'--开始查询
if exists(select 1 from tb2 where ISNUMERIC(replace([列A],'A','')) = 1)
select top 1 * from tb2 where ISNUMERIC(replace([列A],'A','')) = 1 order by 列A desc
else
select top 1 * from tb2 order by 列A desc--结束查询
drop table [tb2]/*
列A
-----
1.6A(1 行受影响)
--> 测试数据: [tb2]
if object_id('[tb2]') is not null drop table [tb2]
create table [tb2] (列A varchar(5))
insert into [tb2]
select '0.4mA' union all
select '0.5mA' union all
select '1.0A' union all
select '1.2A' union all
select '1.6A'--开始查询
select top 1 * from tb2 where 1=
case when ISNUMERIC(replace([列A],'A',''))=1 then ISNUMERIC(replace([列A],'A',''))
else 1 end
order by 列A desc--结束查询
drop table [tb2]/*
列A
-----
1.6A(1 行受影响)
insert into #a values('1.0mA')
insert into #a values('1.2mA')
insert into #a values('0.1A')
insert into #a values('0.2A')
insert into #a values('0.6A')
goDECLARE @count INT
SET @count=0
SELECT @count=COUNT(A) FROM #a WHERE A like '%[^m]A'
IF(@count>0)
BEGIN
SELECT MAX(A) FROM #a WHERE A LIKE '%[^m]A'
END
ELSE
SELECT MAX(A) FROM #a
GODROP TABLE #a/*
--------------------
0.6A(所影响的行数为 1 行)
*/
WHEN (SELECT COUNT(A) FROM #a WHERE A LIKE '%[^m]A') >0
THEN (SELECT MAX(A) FROM #a WHERE A LIKE '%[^m]A' )
ELSE (SELECT MAX(A) FROM #a)
END AS [MAX]
合======================================================================
还是分2种情况考虑好些
SELECT MAX(A) FROM
(
SELECT A = MAX(A) FROM
(
SELECT *
FROM #a
)A
GROUP BY CASE WHEN A LIKE '%[^m]A' THEN 1 ELSE 0 END
)B
WHERE CASE WHEN A LIKE '%[^m]A' THEN 1 END = 1 OR CASE WHEN A LIKE '%[m]A' THEN 1 END = 1
2 详细测试如下 :
2.0)临时表
CREATE TABLE #a
(A varchar(32),
id int)
2.1)只有mA的情况:
INSERT INTO #a(A) VALUES('0.4mA')
INSERT INTO #a(A) VALUES('0.5mA')
--结果:
A
0.5mA2.2)只有A的情况:
INSERT INTO #a(A) VALUES('1.0A')
INSERT INTO #a(A) VALUES('1.2A')
INSERT INTO #a(A) VALUES('1.6A')
--结果:
A
1.6A
2.3)有mA和A的情况:
DELETE FROM #a WHERE A IN('0.4mA','0.5mA')
DELETE FROM #a WHERE A IN('1.0A','1.2A','1.6A')
INSERT INTO #a(A) VALUES('0.4mA')
INSERT INTO #a(A) VALUES('0.5mA')
INSERT INTO #a(A) VALUES('1.0A')
INSERT INTO #a(A) VALUES('1.2A')
INSERT INTO #a(A) VALUES('1.6A')
--结果:
A
1.6A
create table test
(
name varchar(100)
);
insert into test
select '0.4mA'
union
select '0.5mA'
union
select '1.0A'
union
select '1.2A'
union
select '1.6A'
select top(1) * from test order by name desc
insert into @table values('0.5mA')insert into @table values('1.8mA') --增加的记录insert into @table values('1.0A')
insert into @table values('1.2A')
insert into @table values('1.6A')insert into @table values('90.6A') --增加的记录
insert into @table values('100.6A') --后面必须得转化成数值型,
--否则只是按照字符串来排序的话,
--90.6A大于100.6Aselect top 1 vc
from @table
order by (case when charindex('mA',vc)<>0
then 'mA'
else
'A' --因为'mA'>'A',所以这里升序排列,'A'的排在'mA'前面
end) asc , --升序排列,'A'排在前面
cast(replace(
replace(vc,'mA',''),
'A',
'' )
as numeric
) desc --按照值的大小,大的排在前面
select max(列A) from table where 列A not like '%mA%'
还是要考虑的,不考虑业务,实际可能存储数据如下:1.0A
40000.0mA此时,单位为mA的值反而是大的.