如何用sql实现对字段内的字符串排序？？？

lxctb888 2014-06-22 09:11:33
有这样的表A：
id str
1 01,15,05,09
2 03,09,01,29
3 11,22,25,05
4 21,13,05,09
5 09,14,02,19

对A表中的字段str内容排序后：
(也就是对字段str中的字符串从小到大排序)
id str
1 01,05,09,15
2 01,03,09,29
3 05,11,22,25
4 05,09,13,21
5 02,09,14,19

求大神如何用SQL语句来实现？？？？
...全文
699 24 打赏 收藏 转发到动态 举报
写回复
用AI写文章
24 条回复
切换为时间正序
请发表友善的回复…
发表回复
飞啊子 2014-06-30
  • 打赏
  • 举报
 回复
引用 23 楼 lxctb888 的回复:
引用 22 楼 feiazifeiazi 的回复:
你好，首先很感谢你的解答。不过我的表里数据非常多，目前有8万条数据，不知道这样的SQL性能如何？还有就是你的SQL语句中 dbo.[Split](m.aa,',') AS s 这个不太明白，我运行有错误。我目前很希望在插入一条数据的时候，可以自动计算各个字段列A,B,C,D。你看触发器如何呢？具体如何写这个触发器呢？小弟不才还请多多指教！再次谢谢feiazifeiazi这位大哥
-- dbo.Split方法见 -- http://blog.csdn.net/feiazifeiazi/article/details/17242355 dbo.Split是一个函数，要添加一下。 (C#,java都有这个函数.) 8万不是很多..每月会增加多少啊? 你先我的语句弄明白了，触发器里面也就会写了.. 如果还不会，那你就别用触发器了。怕你控制不了它。
lxctb888 2014-06-28
  • 打赏
  • 举报
 回复
引用 22 楼 feiazifeiazi 的回复:



--DROP TABLE mytable;

CREATE TABLE mytable --源数据表
    (
        ID INT 
        ,Num INT
        ,aa VARCHAR(400)
    )
    INSERT INTO mytable(ID,Num,aa)
    SELECT 1,      636550,        '15,78,25,42,18,71,35,39,17,70,61,23,05,38,68,14,59,69,22,27' UNION ALL
    SELECT 2,      636549,        '49,40,08,43,33,02,19,42,57,11,34,28,70,14,36,25,77,10,27,16' UNION ALL
    SELECT 3,      636548,        '63,07,61,65,33,42,13,74,40,60,23,73,45,58,14,71,62,35,06,52' UNION ALL
    SELECT 4,      636547,        '17,61,72,09,48,37,75,10,52,32,78,08,34,12,67,58,07,62,41,01' UNION ALL
    SELECT 5,      636546,        '06,69,60,16,01,47,64,02,52,66,13,38,04,78,36,50,11,77,40,49' UNION ALL
    SELECT 6,      636545,        '76,15,55,29,78,07,35,16,71,58,04,70,41,01,24,65,74,11,42,40' UNION ALL
    SELECT 7,      636540,        '52,31,06,44,66,12,49,67,30,37,15,74,33,46,28,73,40,45,17,68' UNION ALL
    SELECT 8,      636539,        '44,13,70,55,27,01,35,61,03,47,66,18,30,07,80,28,45,15,71,33' UNION ALL
    SELECT 9,      636538,        '10,34,21,78,05,24,15,54,41,02,52,27,01,17,42,65,06,29,26,67' UNION ALL
    SELECT 10,      636537,        '79,42,51,38,76,44,50,35,72,56,41,02,49,62,17,52,65,40,43,20'
 
 
 SELECT p.ID,p.Num
 ,p.[0] [A],p.[1] [B],p.[2] [C],p.[0]+p.[1]+p.[2] [D]
 ,p.aa,p.bb
  FROM mytable AS m
 CROSS APPLY(
 	SELECT STUFF((SELECT ','+ls0.Value FROM(
	SELECT s.Value,cast(s.Value AS INT) ValueOfInt
	,ROW_NUMBER() OVER ( ORDER BY cast(s.Value AS INT)) Rn
		FROM dbo.[Split](m.aa,',') AS s
 	) ls0 ORDER BY ls0.Rn FOR XML PATH('')),1,1,'') bb	
 )r1
 CROSS APPLY(
	 SELECT cast(right(sum(ls1.ValueOfInt),1) AS INT) SumValueOfInt
	 ,ls1.Rn/6 groupId 
	 FROM (
		SELECT cast(s.Value AS INT) ValueOfInt
		,ROW_NUMBER() OVER ( ORDER BY cast(s.Value AS INT))-1 Rn
		FROM dbo.[Split](m.aa,',') AS s 
	 ) ls1 GROUP BY ls1.Rn/6
	 HAVING ls1.Rn/6<=2
 )r2
 PIVOT (MAX(r2.SumValueOfInt) FOR r2.groupId IN([0],[1],[2])) p
 ORDER BY p.ID
 
 

 
 
 
 
 
 

 /*结果


ID          Num         A           B           C           D           aa                                                                                                                                                                                                                                                               bb
----------- ----------- ----------- ----------- ----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           636550      1           7           9           17          15,78,25,42,18,71,35,39,17,70,61,23,05,38,68,14,59,69,22,27                                                                                                                                                                                                      05,14,15,17,18,22,23,25,27,35,38,39,42,59,61,68,69,70,71,78
2           636549      1           6           7           14          49,40,08,43,33,02,19,42,57,11,34,28,70,14,36,25,77,10,27,16                                                                                                                                                                                                      02,08,10,11,14,16,19,25,27,28,33,34,36,40,42,43,49,57,70,77
3           636548      6           2           2           10          63,07,61,65,33,42,13,74,40,60,23,73,45,58,14,71,62,35,06,52                                                                                                                                                                                                      06,07,13,14,23,33,35,40,42,45,52,58,60,61,62,63,65,71,73,74
4           636547      7           9           2           18          17,61,72,09,48,37,75,10,52,32,78,08,34,12,67,58,07,62,41,01                                                                                                                                                                                                      01,07,08,09,10,12,17,32,34,37,41,48,52,58,61,62,67,72,75,78
5           636546      7           6           1           14          06,69,60,16,01,47,64,02,52,66,13,38,04,78,36,50,11,77,40,49                                                                                                                                                                                                      01,02,04,06,11,13,16,36,38,40,47,49,50,52,60,64,66,69,77,78
6           636545      4           1           3           8           76,15,55,29,78,07,35,16,71,58,04,70,41,01,24,65,74,11,42,40                                                                                                                                                                                                      01,04,07,11,15,16,24,29,35,40,41,42,55,58,65,70,71,74,76,78
7           636540      8           0           8           16          52,31,06,44,66,12,49,67,30,37,15,74,33,46,28,73,40,45,17,68                                                                                                                                                                                                      06,12,15,17,28,30,31,33,37,40,44,45,46,49,52,66,67,68,73,74
8           636539      7           7           4           18          44,13,70,55,27,01,35,61,03,47,66,18,30,07,80,28,45,15,71,33                                                                                                                                                                                                      01,03,07,13,15,18,27,28,30,33,35,44,45,47,55,61,66,70,71,80
9           636538      9           4           8           21          10,34,21,78,05,24,15,54,41,02,52,27,01,17,42,65,06,29,26,67                                                                                                                                                                                                      01,02,05,06,10,15,17,21,24,26,27,29,34,41,42,52,54,65,67,78
10          636537      2           9           8           19          79,42,51,38,76,44,50,35,72,56,41,02,49,62,17,52,65,40,43,20                                                                                                                                                                                                      02,17,20,35,38,40,41,42,43,44,49,50,51,52,56,62,65,72,76,79

(10 行受影响)

--  dbo.Split方法见

-- http://blog.csdn.net/feiazifeiazi/article/details/17242355



*/
你好，首先很感谢你的解答。不过我的表里数据非常多，目前有8万条数据，不知道这样的SQL性能如何？还有就是你的SQL语句中 dbo.[Split](m.aa,',') AS s 这个不太明白，我运行有错误。我目前很希望在插入一条数据的时候，可以自动计算各个字段列A,B,C,D。你看触发器如何呢？具体如何写这个触发器呢？小弟不才还请多多指教！再次谢谢feiazifeiazi这位大哥
飞啊子 2014-06-25
  • 打赏
  • 举报
 回复 



--DROP TABLE mytable;

CREATE TABLE mytable --源数据表
    (
        ID INT 
        ,Num INT
        ,aa VARCHAR(400)
    )
    INSERT INTO mytable(ID,Num,aa)
    SELECT 1,      636550,        '15,78,25,42,18,71,35,39,17,70,61,23,05,38,68,14,59,69,22,27' UNION ALL
    SELECT 2,      636549,        '49,40,08,43,33,02,19,42,57,11,34,28,70,14,36,25,77,10,27,16' UNION ALL
    SELECT 3,      636548,        '63,07,61,65,33,42,13,74,40,60,23,73,45,58,14,71,62,35,06,52' UNION ALL
    SELECT 4,      636547,        '17,61,72,09,48,37,75,10,52,32,78,08,34,12,67,58,07,62,41,01' UNION ALL
    SELECT 5,      636546,        '06,69,60,16,01,47,64,02,52,66,13,38,04,78,36,50,11,77,40,49' UNION ALL
    SELECT 6,      636545,        '76,15,55,29,78,07,35,16,71,58,04,70,41,01,24,65,74,11,42,40' UNION ALL
    SELECT 7,      636540,        '52,31,06,44,66,12,49,67,30,37,15,74,33,46,28,73,40,45,17,68' UNION ALL
    SELECT 8,      636539,        '44,13,70,55,27,01,35,61,03,47,66,18,30,07,80,28,45,15,71,33' UNION ALL
    SELECT 9,      636538,        '10,34,21,78,05,24,15,54,41,02,52,27,01,17,42,65,06,29,26,67' UNION ALL
    SELECT 10,      636537,        '79,42,51,38,76,44,50,35,72,56,41,02,49,62,17,52,65,40,43,20'
 
 
 SELECT p.ID,p.Num
 ,p.[0] [A],p.[1] [B],p.[2] [C],p.[0]+p.[1]+p.[2] [D]
 ,p.aa,p.bb
  FROM mytable AS m
 CROSS APPLY(
 	SELECT STUFF((SELECT ','+ls0.Value FROM(
	SELECT s.Value,cast(s.Value AS INT) ValueOfInt
	,ROW_NUMBER() OVER ( ORDER BY cast(s.Value AS INT)) Rn
		FROM dbo.[Split](m.aa,',') AS s
 	) ls0 ORDER BY ls0.Rn FOR XML PATH('')),1,1,'') bb	
 )r1
 CROSS APPLY(
	 SELECT cast(right(sum(ls1.ValueOfInt),1) AS INT) SumValueOfInt
	 ,ls1.Rn/6 groupId 
	 FROM (
		SELECT cast(s.Value AS INT) ValueOfInt
		,ROW_NUMBER() OVER ( ORDER BY cast(s.Value AS INT))-1 Rn
		FROM dbo.[Split](m.aa,',') AS s 
	 ) ls1 GROUP BY ls1.Rn/6
	 HAVING ls1.Rn/6<=2
 )r2
 PIVOT (MAX(r2.SumValueOfInt) FOR r2.groupId IN([0],[1],[2])) p
 ORDER BY p.ID
 
 

 
 
 
 
 
 

 /*结果


ID          Num         A           B           C           D           aa                                                                                                                                                                                                                                                               bb
----------- ----------- ----------- ----------- ----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           636550      1           7           9           17          15,78,25,42,18,71,35,39,17,70,61,23,05,38,68,14,59,69,22,27                                                                                                                                                                                                      05,14,15,17,18,22,23,25,27,35,38,39,42,59,61,68,69,70,71,78
2           636549      1           6           7           14          49,40,08,43,33,02,19,42,57,11,34,28,70,14,36,25,77,10,27,16                                                                                                                                                                                                      02,08,10,11,14,16,19,25,27,28,33,34,36,40,42,43,49,57,70,77
3           636548      6           2           2           10          63,07,61,65,33,42,13,74,40,60,23,73,45,58,14,71,62,35,06,52                                                                                                                                                                                                      06,07,13,14,23,33,35,40,42,45,52,58,60,61,62,63,65,71,73,74
4           636547      7           9           2           18          17,61,72,09,48,37,75,10,52,32,78,08,34,12,67,58,07,62,41,01                                                                                                                                                                                                      01,07,08,09,10,12,17,32,34,37,41,48,52,58,61,62,67,72,75,78
5           636546      7           6           1           14          06,69,60,16,01,47,64,02,52,66,13,38,04,78,36,50,11,77,40,49                                                                                                                                                                                                      01,02,04,06,11,13,16,36,38,40,47,49,50,52,60,64,66,69,77,78
6           636545      4           1           3           8           76,15,55,29,78,07,35,16,71,58,04,70,41,01,24,65,74,11,42,40                                                                                                                                                                                                      01,04,07,11,15,16,24,29,35,40,41,42,55,58,65,70,71,74,76,78
7           636540      8           0           8           16          52,31,06,44,66,12,49,67,30,37,15,74,33,46,28,73,40,45,17,68                                                                                                                                                                                                      06,12,15,17,28,30,31,33,37,40,44,45,46,49,52,66,67,68,73,74
8           636539      7           7           4           18          44,13,70,55,27,01,35,61,03,47,66,18,30,07,80,28,45,15,71,33                                                                                                                                                                                                      01,03,07,13,15,18,27,28,30,33,35,44,45,47,55,61,66,70,71,80
9           636538      9           4           8           21          10,34,21,78,05,24,15,54,41,02,52,27,01,17,42,65,06,29,26,67                                                                                                                                                                                                      01,02,05,06,10,15,17,21,24,26,27,29,34,41,42,52,54,65,67,78
10          636537      2           9           8           19          79,42,51,38,76,44,50,35,72,56,41,02,49,62,17,52,65,40,43,20                                                                                                                                                                                                      02,17,20,35,38,40,41,42,43,44,49,50,51,52,56,62,65,72,76,79

(10 行受影响)

--  dbo.Split方法见

-- http://blog.csdn.net/feiazifeiazi/article/details/17242355



*/
飞啊子 2014-06-25
  • 打赏
  • 举报
 回复
引用 14 楼 lxctb888 的回复:
嗯，非常感谢chen357313771。思路我看懂了，可是我有几个问题:第一这存储过程不能重复执行，因为我的源数据表里的内容是不断更新的，这样重复操作就会出现数据重复了。其次由于内容不断更新，我不建议把得到的数据插入到目标表，这样就会增加数据库的大小体积压力。这是我个人的看法。
你的表的总数据量，会是多少呢? 如果不大的话，一次性查出来就好了.不需要用触发器的。
飞啊子 2014-06-25
  • 打赏
  • 举报
 回复
引用 2 楼 lxctb888 的回复:
首先很感谢chen357313771这位大神，第一步问题已经基本解决了，现在又来了第二个问题。详细内容及功能问题如下图，请求大神们赐教！【希望可以用SQL存储过程来实现优化功能】。非常感谢大家！！！
看到了如此详细的提问. 已知什么,求什么,怎么求,预期结果都写的很好.. +10086
chen357313771 2014-06-24
  • 打赏
  • 举报
 回复
没必要用触发器，在插入原始数据的存储中计算好了跟原始数据一起插入不就了了？
引用 18 楼 lxctb888 的回复:
chen357313771这位大哥，帮忙写一个插入原始表（字段有ID,Num,NotxtA,A,B,C,D）数据的触发器吧。即求插入一条新纪录时自动计算字段A,B,C,D内的数据内容，谢谢啊！
lxctb888 2014-06-23
  • 打赏
  • 举报
 回复
chen357313771这位大哥，帮忙写一个插入原始表（字段有ID,Num,NotxtA,A,B,C,D）数据的触发器吧。即求插入一条新纪录时自动计算字段A,B,C,D内的数据内容，谢谢啊！
chen357313771 2014-06-23
  • 打赏
  • 举报
 回复
引用 16 楼 lxctb888 的回复:
chen357313771，你看我这样想怎么样:排序大小的数据其实不是太重要，我最终是对字段A,B,C,D的统计分析。所以我这样做:做一个插入的存储过程，也就是触发器，即对源数据表插入一条新内容时，引发自动计算字段A,B,C,D内的数据内容。大哥你看这样可以吗？
可以
lxctb888 2014-06-23
  • 打赏
  • 举报
 回复
chen357313771，你看我这样想怎么样:排序大小的数据其实不是太重要，我最终是对字段A,B,C,D的统计分析。所以我这样做:做一个插入的存储过程，也就是触发器，即对源数据表插入一条新内容时，引发自动计算字段A,B,C,D内的数据内容。大哥你看这样可以吗？
chen357313771 2014-06-23
  • 打赏
  • 举报
 回复
引用 14 楼 lxctb888 的回复:
嗯，非常感谢chen357313771。思路我看懂了，可是我有几个问题:第一这存储过程不能重复执行，因为我的源数据表里的内容是不断更新的，这样重复操作就会出现数据重复了。其次由于内容不断更新，我不建议把得到的数据插入到目标表，这样就会增加数据库的大小体积压力。这是我个人的看法。
那你就更改源数据表结构，把结果更新到源数据表就可以了
lxctb888 2014-06-23
  • 打赏
  • 举报
 回复
嗯，非常感谢chen357313771。思路我看懂了，可是我有几个问题:第一这存储过程不能重复执行，因为我的源数据表里的内容是不断更新的，这样重复操作就会出现数据重复了。其次由于内容不断更新，我不建议把得到的数据插入到目标表，这样就会增加数据库的大小体积压力。这是我个人的看法。
chen357313771 2014-06-23
  • 打赏
  • 举报
 回复 
WITH tt(A,b)
AS( 
SELECT 1,'01,15,05,09' 
UNION ALL
SELECT 2,'03,09,01,29'
UNION ALL
SELECT 3,'11,22,25,05'
UNION ALL
SELECT 4,'21,13,05,09'
UNION ALL
SELECT 5,'09,14,02,19'
) 
SELECT A.A
	,B1.B
FROM tt AS A
OUTER APPLY (
	SELECT B= STUFF((SELECT ','+orderNum 
	FROM(
		SELECT A
		,t.c.value('.','char(2)') AS orderNum
		FROM(
			SELECT A
				,CONVERT(XML,'<root><v>'+replace(B,',','</v><v>')+'</v></root>') AS B
				from tt WHERE A=A.A
			) A 
		OUTER APPLY a.b.nodes('root/v') t(c)
		 ) AS A
	 ORDER BY orderNum
	 FOR XML PATH('')),1,1,'')
 ) AS B1
chen357313771 2014-06-23
  • 打赏
  • 举报
 回复 
CREATE TABLE test --源数据表
	(
		ID INT 
		,Num INT
		,NoTxtA VARCHAR(400)
	)
	INSERT INTO test(ID,Num,NoTxtA)
	SELECT 1,	  636550,	    '15,78,25,42,18,71,35,39,17,70,61,23,05,38,68,14,59,69,22,27' UNION ALL
	SELECT 2,	  636549,	    '49,40,08,43,33,02,19,42,57,11,34,28,70,14,36,25,77,10,27,16' UNION ALL
	SELECT 3,	  636548,	    '63,07,61,65,33,42,13,74,40,60,23,73,45,58,14,71,62,35,06,52' UNION ALL
	SELECT 4,	  636547,	    '17,61,72,09,48,37,75,10,52,32,78,08,34,12,67,58,07,62,41,01' UNION ALL
	SELECT 5,	  636546,	    '06,69,60,16,01,47,64,02,52,66,13,38,04,78,36,50,11,77,40,49' UNION ALL
	SELECT 6,	  636545,	    '76,15,55,29,78,07,35,16,71,58,04,70,41,01,24,65,74,11,42,40' UNION ALL
	SELECT 7,	  636540,	    '52,31,06,44,66,12,49,67,30,37,15,74,33,46,28,73,40,45,17,68' UNION ALL
	SELECT 8,	  636539,	    '44,13,70,55,27,01,35,61,03,47,66,18,30,07,80,28,45,15,71,33' UNION ALL
	SELECT 9,	  636538,	    '10,34,21,78,05,24,15,54,41,02,52,27,01,17,42,65,06,29,26,67' UNION ALL
	SELECT 10,	  636537,	    '79,42,51,38,76,44,50,35,72,56,41,02,49,62,17,52,65,40,43,20'
	CREATE TABLE test1 --目标表
	(
		ID INT 
		,Num INT
		,NoTxtA VARCHAR(400)
		,NotxtSort VARCHAR(400)
		,A INT
		,B INT
		,C INT
		,D INT
	)




CREATE PROC GetTest ---创建存储过程
AS
BEGIN


	INSERT INTO test1(ID,Num,NoTxtA,NotxtSort) ---把原始数据和排序后的数据插入到目标表
	SELECT A.*,B.B AS NoTxtSort
	FROM test AS A 
	JOIN (
	SELECT A.ID
		,B1.B
	FROM test AS A
	OUTER APPLY (
		SELECT B= STUFF((SELECT ','+num1 
		FROM(
			SELECT ID
			,t.c.value('.','char(2)') AS num1
			FROM(
				SELECT ID
					,CONVERT(XML,'<root><v>'+replace(NoTxtA,',','</v><v>')+'</v></root>') AS B
					from test WHERE ID=A.ID
				) A 
			OUTER APPLY a.b.nodes('root/v') t(c)
			 ) AS A
		 ORDER BY num1
		 FOR XML PATH('')),1,1,'')
	 ) AS B1
	  ) AS B ON A.ID =B.ID

	UPDATE A  -----把计算后的结果更新到目标表
	SET A.A=B.A,A.B=B.B,A.C=B.C,A.D=B.D
	FROM test1 AS A
	JOIN (
	SELECT ID,[1] AS A,[2] AS B,[3] AS C,[1]+[2]+[3] AS D 
	FROM (
		SELECT t.ID
			,t.ID1 
			,CAST(RIGHT(CAST(ISNULL(SUM(t.num1),0) AS VARCHAR(10)),1) AS INT) AS Num
		FROM (
				SELECT (ROW_NUMBER() OVER(PARTITION BY ID ORDER BY GETDATE())-1)/6+1 AS ID1 --每6行分组用于sum
				,ID
				,t.c.value('.','int') AS num1
				FROM(
					SELECT ID
						,CONVERT(XML,'<root><v>'+replace(NotxtSort,',','</v><v>')+'</v></root>') AS B
						from test1 
					) A 
				OUTER APPLY a.b.nodes('root/v') t(c)
				) AS T
		WHERE ID1<=3 ---只取前三组
		GROUP BY t.ID
			,t.ID1 
	 ) C
	 PIVOT (MAX(Num) FOR ID1 IN([1],[2],[3]))p
	 ) AS B ON A.ID = B.ID


	 SELECT * FROM test1


END

exec GetTest---不能多次执行，会导致重复操作，数据重复，最后又参数，有条件
lxctb888 2014-06-23
  • 打赏
  • 举报
 回复
你说的我能理解，但是不太会写存储过程，能不能帮忙写一下？现在只有一个源数据表，其实就是统计分析，最好一个表里有完整的这些数据就OK了！
chen357313771 2014-06-23
  • 打赏
  • 举报
 回复
你不是在用存储过程吗？在存储里面用临时表啊。然后在create一个表，把这两个临时表join的结果集插入进去，或者修改源数据表结构，增加一个字段，原来让他为空计算后更新进去不就可以了？
引用 9 楼 lxctb888 的回复:
这个我知道，可是数据库里没有这两个临时表啊！而且这些SQL语句太长了，我不知道如何解决啊。
lxctb888 2014-06-23
  • 打赏
  • 举报
 回复
能不能把这些sql语句变成一个存储过程啊！最终只想要一个表，这个表里包含这些所有的数据。请chen357313771帮帮忙，非常感谢！
lxctb888 2014-06-23
  • 打赏
  • 举报
 回复
这个我知道，可是数据库里没有这两个临时表啊！而且这些SQL语句太长了，我不知道如何解决啊。
chen357313771 2014-06-23
  • 打赏
  • 举报
 回复
可以，把#test和#test1 根据ID JOIN起来就可以了
引用 7 楼 lxctb888 的回复:
非常感chen357313771的耐心详细的解答。我刚刚测试了一下没有任何问题。还想请教一个问题:能不能把这些数据（原始数据和查询得到的数据）放到一个表中啊（类似视图这样的表，这样就便于查询数据了），最主要是一个表里的数据便于后期数据的统计分析。再次感谢chen357313771大神。在线等……
lxctb888 2014-06-23
  • 打赏
  • 举报
 回复
非常感chen357313771的耐心详细的解答。我刚刚测试了一下没有任何问题。还想请教一个问题:能不能把这些数据（原始数据和查询得到的数据）放到一个表中啊（类似视图这样的表，这样就便于查询数据了），最主要是一个表里的数据便于后期数据的统计分析。再次感谢chen357313771大神。在线等……
chen357313771 2014-06-23
  • 打赏
  • 举报
 回复
偷懒了。。。 
	IF OBJECT_ID('tempdb..#test','U') IS NOT NULL DROP TABLE #test
	CREATE TABLE #test
	(
		ID INT 
		,Num INT
		,NoTxtA VARCHAR(100)
		,NoTxtB VARCHAR(100)
	)
	INSERT INTO #test
	SELECT 1,	  636550,	    '15,78,25,42,18,71,35,39,17,70,61,23,05,38,68,14,59,69,22,27',NULL UNION ALL
	SELECT 2,	  636549,	    '49,40,08,43,33,02,19,42,57,11,34,28,70,14,36,25,77,10,27,16',NULL UNION ALL
	SELECT 3,	  636548,	    '63,07,61,65,33,42,13,74,40,60,23,73,45,58,14,71,62,35,06,52',NULL UNION ALL
	SELECT 4,	  636547,	    '17,61,72,09,48,37,75,10,52,32,78,08,34,12,67,58,07,62,41,01',NULL UNION ALL
	SELECT 5,	  636546,	    '06,69,60,16,01,47,64,02,52,66,13,38,04,78,36,50,11,77,40,49',NULL UNION ALL
	SELECT 6,	  636545,	    '76,15,55,29,78,07,35,16,71,58,04,70,41,01,24,65,74,11,42,40',NULL UNION ALL
	SELECT 7,	  636540,	    '52,31,06,44,66,12,49,67,30,37,15,74,33,46,28,73,40,45,17,68',NULL UNION ALL
	SELECT 8,	  636539,	    '44,13,70,55,27,01,35,61,03,47,66,18,30,07,80,28,45,15,71,33',NULL UNION ALL
	SELECT 9,	  636538,	    '10,34,21,78,05,24,15,54,41,02,52,27,01,17,42,65,06,29,26,67',NULL UNION ALL
	SELECT 10,	  636537,	    '79,42,51,38,76,44,50,35,72,56,41,02,49,62,17,52,65,40,43,20',NULL

	UPDATE #test 
	SET NoTxtB=B.B
	FROM #test AS A
	JOIN(
	SELECT A.ID
		,B1.B
	FROM #test AS A
	OUTER APPLY (
		SELECT B= STUFF((SELECT ','+num1 
		FROM(
			SELECT ID
			,t.c.value('.','char(2)') AS num1
			FROM(
				SELECT ID
					,CONVERT(XML,'<root><v>'+replace(NoTxtA,',','</v><v>')+'</v></root>') AS B
					from #test WHERE ID=A.ID
				) A 
			OUTER APPLY a.b.nodes('root/v') t(c)
			 ) AS A
		 ORDER BY num1
		 FOR XML PATH('')),1,1,'')
	 ) AS B1
	  ) AS B ON A.ID =B.ID

 SELECT * FROM #test ---排序后

  IF OBJECT_ID('tempdb..#test1','U') IS NOT NULL DROP TABLE #test1 --结果表
	CREATE TABLE #test1
	(
		ID INT
		,A INT
		,B INT
		,C INT
		,D INT  
	)

	INSERT INTO #test1
	SELECT ID,[1] AS A,[2] AS B,[3] AS C,[1]+[2]+[3] AS D 
	FROM (
		SELECT t.ID
			,t.ID1 
			,CAST(RIGHT(CAST(ISNULL(SUM(t.num1),0) AS VARCHAR(10)),1) AS INT) AS Num
		FROM (
				SELECT (ROW_NUMBER() OVER(PARTITION BY ID ORDER BY GETDATE())-1)/6+1 AS ID1 --每6行分组用于sum
				,ID
				,t.c.value('.','int') AS num1
				FROM(
					SELECT ID
						,CONVERT(XML,'<root><v>'+replace(NoTxtB,',','</v><v>')+'</v></root>') AS B
						from #test 
					) A 
				OUTER APPLY a.b.nodes('root/v') t(c)
				) AS T
		WHERE ID1<=3 ---只取前三组
		GROUP BY t.ID
			,t.ID1 
	 ) C
	 PIVOT (MAX(Num) FOR ID1 IN([1],[2],[3]))p



	 SELECT * FROM #test1


/*
ID	Num		NoTxtA														NoTxtB
1	636550	15,78,25,42,18,71,35,39,17,70,61,23,05,38,68,14,59,69,22,27	05,14,15,17,18,22,23,25,27,35,38,39,42,59,61,68,69,70,71,78
2	636549	49,40,08,43,33,02,19,42,57,11,34,28,70,14,36,25,77,10,27,16	02,08,10,11,14,16,19,25,27,28,33,34,36,40,42,43,49,57,70,77
3	636548	63,07,61,65,33,42,13,74,40,60,23,73,45,58,14,71,62,35,06,52	06,07,13,14,23,33,35,40,42,45,52,58,60,61,62,63,65,71,73,74
4	636547	17,61,72,09,48,37,75,10,52,32,78,08,34,12,67,58,07,62,41,01	01,07,08,09,10,12,17,32,34,37,41,48,52,58,61,62,67,72,75,78
5	636546	06,69,60,16,01,47,64,02,52,66,13,38,04,78,36,50,11,77,40,49	01,02,04,06,11,13,16,36,38,40,47,49,50,52,60,64,66,69,77,78
6	636545	76,15,55,29,78,07,35,16,71,58,04,70,41,01,24,65,74,11,42,40	01,04,07,11,15,16,24,29,35,40,41,42,55,58,65,70,71,74,76,78
7	636540	52,31,06,44,66,12,49,67,30,37,15,74,33,46,28,73,40,45,17,68	06,12,15,17,28,30,31,33,37,40,44,45,46,49,52,66,67,68,73,74
8	636539	44,13,70,55,27,01,35,61,03,47,66,18,30,07,80,28,45,15,71,33	01,03,07,13,15,18,27,28,30,33,35,44,45,47,55,61,66,70,71,80
9	636538	10,34,21,78,05,24,15,54,41,02,52,27,01,17,42,65,06,29,26,67	01,02,05,06,10,15,17,21,24,26,27,29,34,41,42,52,54,65,67,78
10	636537	79,42,51,38,76,44,50,35,72,56,41,02,49,62,17,52,65,40,43,20	02,17,20,35,38,40,41,42,43,44,49,50,51,52,56,62,65,72,76,79	 
*/

/*
ID	A	B	C	D
1	1	7	9	17
2	1	6	7	14
3	6	2	2	10
4	7	9	2	18
5	7	6	1	14
6	4	1	3	8
7	8	0	8	16
8	7	7	4	18
9	9	4	8	21
10	2	9	8	19
*/
加载更多回复（4）
sql 字符串排序
在MySQL中，我们经常会对一个字段进行排序查询，但进行中文排序和查找的时候，对汉字的排序和查找结果往往都是错误的。 网上提供了很多方法: 比如字段后" + 0" 或者 " *1", 但是如果是按照首字母排序实际上并没有效果, 顺序依旧让人摸不着头脑, 今天试了下做了个总结 一按照数字排序 手动转换类型： 用下面的方法就可以了，使server_id+0之后再排序，问题解决了。 se...
sql更新某个字段，字段=字段+字符串 拼接
group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator ‘分隔符’])第一个参数是其他参数的分隔符，分隔符的位置要放在两个字符串连接的位置之间。concat_ws()代表concat with separator,是concat()函数的特殊形式。注意：如果分隔符为NULL，那么结果也为NULL，函数会忽略任何分隔符参数后的NULL值。concat()函数用于将两个字符串连接起来，形成一个单一的字符串。
SQL字符串字段内的字符排序
排序要求： 1.首先安装 #号前面的字符大小排序 2.再按照 / 左右消除的结果排序 例如： 1/4  排序前 1/4-20 10#-24 2#-56 4#-40 5/16-18 6#-32 8#-32 排序后结果 2#-56 4#-40 6#-32 8#-32 10#-24 1/4-20 5/16-18 SQL: --> 测试数据：[TB] if
sql对字符串进行排序
tp5写法： $gold = Db::table('table')-&gt;order('CAST(需要排序的字段 AS UNSIGNED) desc')-&gt;limit(10)-&gt;select(); 原生写法： select * from table  ORDER BY CAST(需要排序的字段 AS UNSIGNED) desc...
SQL字符型字段按数字型字段排序实现方法
得分字段为varchar 那么按得分排序得到的结果可能是： 1 10 11 123 1234 2 25 253 3 但是我们想要按照数字排序 1 2 3 10 11 25 123 1234 253这时候只需要 一种 、 数字字符串为01 0100 02 0200 一排序就是这个顺序，我想要变成01 02 0100 0200 就用下面这个办法 把这个字段转
疑难问题

22,209

社区成员

121,731

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章