例如,在表A中有如下数据
Product
---------------------------
<product>dvdrom</product>
<product>dvdrom</product>
<product>keyboard</product>
<product>keyboard</product>
<product>keyboard</product>
<product>dvdrom</product>
<product>keyboard</product>
<product>mouse</product>
<product>mouse</product>
<product>mouse</product>如果执行如下查询
select Product.query('//product/text()[../text()="dvdrom"]') As T From A
得到的结果是
T
---------------------------------
dvdrom
dvdrom
null
null
null
dvdrom
null
null
null
null
共10条记录(其中7条为Null的记录不是我想得到的记录)如果我想得到以下记录,该怎么查询?
T
------------------
dvdrom
dvdrom
dvdrom(共3条记录)ps,如果我想按照不同的//product/text()对这些记录进行分类,并取得每种类型的数量,该怎么做?
T Count
---------------
dvdrom 3
mouse 3
keyboard 4?
Product
---------------------------
<product>dvdrom</product>
<product>dvdrom</product>
<product>keyboard</product>
<product>keyboard</product>
<product>keyboard</product>
<product>dvdrom</product>
<product>keyboard</product>
<product>mouse</product>
<product>mouse</product>
<product>mouse</product>如果执行如下查询
select Product.query('//product/text()[../text()="dvdrom"]') As T From A
得到的结果是
T
---------------------------------
dvdrom
dvdrom
null
null
null
dvdrom
null
null
null
null
共10条记录(其中7条为Null的记录不是我想得到的记录)如果我想得到以下记录,该怎么查询?
T
------------------
dvdrom
dvdrom
dvdrom(共3条记录)ps,如果我想按照不同的//product/text()对这些记录进行分类,并取得每种类型的数量,该怎么做?
T Count
---------------
dvdrom 3
mouse 3
keyboard 4?
SELECT nref.value('.','varchar(100)')
FROM A CROSS apply A.Product.nodes('./product') AS t(nref)
WHERE RTRIM(LTRIM(nref.value('.','varchar(100)'))) = 'dvdrom'
执行结果 :
dvdrom
dvdrom
dvdrom 2.
SELECT *,count(*) FROM (
SELECT nref.value('.','varchar(100)') v
FROM A CROSS apply A.Product.nodes('./product') AS t(nref)
) t
GROUP BY t.v
执行结果:
dvdrom 3
keyboard 4
mouse 3以前搞oracle的,刚刚搞了几天sql server,sql代码写的不好
T Count
---------------
dvdrom 3
mouse 3
keyboard 4 select Product.query( '//product/text()[../text()= "dvdrom "] ') As T From A