-- 测试数据(仅列出有代表意义的部分数据)
Select * into ##Y from (
Select 1 as Y_ID,'100D24F' as Y_Desc
Union All Select 2 as Y_ID,'100NM/2'
Union All Select 3 as Y_ID,'105S/2'
Union All Select 4 as Y_ID,'10H'
Union All Select 5 as Y_ID,'110D'
Union All Select 6 as Y_ID,'12012F'
Union All Select 7 as Y_ID,'120D/1'
Union All Select 8 as Y_ID,'120N/2'
Union All Select 9 as Y_ID,'120NM/2'
Union All Select 10 as Y_ID,'140D48F'
Union All Select 11 as Y_ID,'15048F'
Union All Select 12 as Y_ID,'150D30F'
Union All Select 13 as Y_ID,'2070D24F'
Union All Select 14 as Y_ID,'20D1F'
Union All Select 15 as Y_ID,'20D75F'
Union All Select 16 as Y_ID,'32S/1+60S/2'
Union All Select 17 as Y_ID,'40D+70D未知'
Union All Select 18 as Y_ID,'50M+122N'
) tbSelect * into ##F from (
Select 'A' AS F,1 AS Y_ID
Union All Select 'A',17
Union All Select 'A',9
Union All Select 'A',18
) tb-- ##Y表中,Y_Desc字段中的
-- 另外加号代表两个或多个材料成份的组合
-- 数字加英文字母加斜杠代表一种材料的缩写。
-- 比如105S/2,意思就是105 支 2 股
-- 2070D24F的意思是2070 D支 24 F股
-- 而110D就可以当是110 D支 1 股-- 现在要求算出最大支数、股数的材料,比如有个物件A的材料如下
Select a.F,b.Y_Desc from ##F a inner join ##Y b on a.Y_ID=b.Y_ID
/*
F Y_Desc
---- -----------
A 100D24F
A 120NM/2
A 40D+70D未知
A 50M+122N(所影响的行数为 4 行)
/*-- 计算方式是首先拆分复合材料的,比如50M+122N拆分成两个,然后先看最大支,再看最大股
-- 那么物件A的最大的材料应该是50M+122N,因为它的122支是最高的
-- 反之,最小材料是40D+70D未知-- 请问如何用SQL自动算出?要求最好能一条语句算出,谢谢!
(
Select a.F,b.Y_Desc,
D=ltrim(substring(b.Y_Desc,n.number,charindex('+',b.Y_Desc+'+',n.number)-n.number))
from ##F a inner join ##Y b on a.Y_ID=b.Y_ID
join master..spt_values n ON n.type='p' AND n.number BETWEEN 1 AND LEN(b.Y_Desc)
where substring('+'+b.Y_Desc,n.number,1)='+'
) t
order by convert(int,left (D,patindex('%[^0-9]%',D) -1 )) desc/*
F Y_Desc D
---- ----------- -----------
A 50M+122N 122N(所影响的行数为 1 行)
*/
select top 1 * from
(
Select a.F,b.Y_Desc,
D=ltrim(substring(b.Y_Desc,n.number,charindex('+',b.Y_Desc+'+',n.number)-n.number))
from ##F a inner join ##Y b on a.Y_ID=b.Y_ID
join master..spt_values n ON n.type='p' AND n.number BETWEEN 1 AND LEN(b.Y_Desc)
where substring('+'+b.Y_Desc,n.number,1)='+'
) t
order by convert(int,left (D,patindex('%[^0-9]%',D) -1 )) asc/*
F Y_Desc D
---- ----------- -----------
A 40D+70D未知 40D(所影响的行数为 1 行)
*/
因为在平时查询的时候不可能就查询一个物件A,可能是N个物件的最大、最小材料
不过仍然谢谢你!
where Y_Desc=(
select top 1 Y_Desc from
(
Select a.F,b.Y_Desc,
D=ltrim(substring(b.Y_Desc,n.number,charindex('+',b.Y_Desc+'+',n.number)-n.number))
from ##F a inner join ##Y b on a.Y_ID=b.Y_ID
join master..spt_values n ON n.type='p' AND n.number BETWEEN 1 AND LEN(b.Y_Desc)
where substring('+'+b.Y_Desc,n.number,1)='+' and a.F=m.F
) t
order by convert(int,left (D,patindex('%[^0-9]%',D) -1 )) desc
)/*
F Y_Desc
---- -----------
A 50M+122N(所影响的行数为 1 行)
*/