日期:2014-05-17 浏览次数:20535 次
--> 测试数据:#tb
IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO
CREATE TABLE #tb(id int identity,[col] xml)
INSERT #tb
select '<ImageSize> <Width> 1491 </Width> <Height> 738 </Height> </ImageSize>'
union all select '<ImageSize> <Width> 648 </Width> <Height> 864 </Height> </ImageSize>'
union all select '<ImageSize> <Width> 768 </Width> <Height> 1024 </Height> </ImageSize>'
union all select '<ImageSize> <Width> 682 </Width> <Height> 1024 </Height> </ImageSize>'
select * from #tb
--我想要实现如下功能:
--1. 找出所有Width > 1200的ID
select id from #tb where [col].value('(/ImageSize/Width)[1]','int') >1200
--2. 找出所有Width > Height的ID
select id from #tb where [col].value('(/ImageSize/Width)[1]','int') >[col].value('(/ImageSize/Height)[1]','int')
--3. 找出所有Width / Height = 3:4或4:3的ID
select id from #tb where [col].value('(/ImageSize/Width)[1]','int')/[col].value('(/ImageSize/Height)[1]','int')
in (3/4,4/3)
--4. 输出字段为 ID Width Height 这样的传统格式表
select id,
[col].value('(/ImageSize/Width)[1]','int') as Width,
[col].value('(/ImageSize/Height)[1]','int') as Height
from #tb