如何用Sql语句完成下面的例子?

Question

数据表tb:QID        Count       PList
1              99             1,2,3
3              90             2,3,1
8              80             5,2,2QID是标识取PList的第几位. 得出的Re字段为: Count*(PList的第几位/100).
如第一个数据就是  99*(1/100)=0.99   如此类推.
最后想要的结果是:QID        Count       PList          Re
1              99             1,2,3         0.99
3              93             2,3,1         0.93
1              80             5,2,2         4.00
如何写Sql语句? 非常感谢.

Answer 1

CREATE TABLE #t
( 
  QID INT,
  COUNT INT,
  PLIST NVARCHAR(MAX)
)
INSERT INTO #t VALUES
(1,99,'1,2,3'),
(3,90,'2,3,1'),
(8,80,'5,2,2')
WITH CTE AS
(
SELECT A.QID,A.COUNT,A.PLIST,B.id
      FROM (SELECT [value] = CONVERT(XML , '<v>' + REPLACE(PLIST , ',' , '</v><v>')+ '</v>'),* FROM #T
      ) A
      OUTER APPLY ( SELECT id = N.v.value('.' , 'varchar(100)')
                    FROM A.[value].nodes('/v') N (v)
                  ) B
)
SELECT QID,COUNT,plist,REPLACE = COUNT*(MIN(ID)/100.0)
from CTE
group by QID,COUNT,plist

Answer 2

WITH前面加;
你帖的结果是不是错了，算出来不一样

Answer 3

不好意思.是我写的数据错了.应该是
QID        Count       PList
1              99             1,2,3
3              90             2,3,1
1              80             5,2,2

Answer 4

数据表tb:

QID        Count       PList
1              99             1,2,3
3              90             2,3,1
1              80             5,2,2QID是标识取PList的第几位. 得出的Re字段为: Count*(PList的第几位/100).
如第一个数据就是  99*(1/100)=0.99   如此类推.
最后想要的结果是:QID        Count       PList          Re
1              99             1,2,3         0.99
3              93             2,3,1         0.93
1              80             5,2,2         4.00
如何写Sql语句? 非常感谢. 

Answer 5

drop table #t
CREATE TABLE #t
( 
  QID INT,
  COUNT INT,
  PLIST NVARCHAR(MAX)
)
INSERT INTO #t VALUES
(1,99,'1,2,3'),
(3,90,'2,3,1'),
(1,80,'5,2,2');
go
WITH CTE AS
(
SELECT ROW_NUMBER()over(partition by QID,count,plist order by getdate()) nid,A.QID,A.COUNT,A.PLIST,B.id
      FROM (SELECT [value] = CONVERT(XML , '<v>' + REPLACE(PLIST , ',' , '</v><v>')+ '</v>'),* FROM #T
      ) A
      OUTER APPLY ( SELECT id = N.v.value('.' , 'varchar(100)')
                    FROM A.[value].nodes('/v') N (v)
                  ) B
)
SELECT nid,id,QID,COUNT,plist,REPLACE = COUNT*(ID/100.0)
from CTE
where nid = QID 

Answer 6

如果格式固定一个数字，这样更简单
select *,COUNT*(substring(REPLACE(PLIST,',',''),QID,1)/100.0) from #t a

Answer 7

最后一行的PList里没有1喔,请问结果4.00是怎么来的?

Answer 8

select SUBSTRING(PLIST,QID*2-1,1)/100.00*COUNT from #t