27,579
社区成员
发帖
与我相关
我的任务
分享
DECLARE @Sql NVARCHAR(max)
SET @Sql='SELECT T1'
SELECT @Sql=@Sql+','+QUOTENAME(T3)+'=ISNULL(RTRIM(SUM(CASE WHEN T3='+QUOTENAME(T3,'''')+' THEN 1 END)),'''')'
FROM T ORDER BY LEN(T3)
EXEC(@Sql+' FROM T GROUP BY T1')
[/quote]
这个也有问题,上边有个运行不对的图片。[/quote]
DECLARE @S VARCHAR(2000) = (SELECT T3, 'AS ' + T3 AS NOTDO FROM # GROUP BY T3 ORDER BY LEN(T3),T3 FOR XML PATH)
SET @S = REPLACE(@S, '<row><T3>', ',COUNT(CASE T3 WHEN ''')
SET @S = REPLACE(@S, '</T3><NOTDO>', ''' THEN 1 END) ')
SET @S = REPLACE(@S, '</NOTDO></row>', '')
SET @S = LEFT(@S,LEN(@S)-1)
SET @S = 'SELECT T1' + @S + ' FROM # GROUP BY T1 ORDER BY CASE T1 WHEN ''' + '甲' + ''' THEN 1 WHEN ''' + '乙' + ''' THEN 2 WHEN ''' + '丙' + ''' THEN 3 WHEN ''' + '丁' + ''' THEN 4 END'
PRINT @S
EXECUTE(@S)
[/quote]
T1列是自动增加的,不限于甲乙丙丁的。[/quote]
汉字的排序是根据拼音字母来的,所以要转换才能精准,你T1自动递增的话,肯定得有个标准出来,是数字还是字符还是别的什么,这样才能正确排序[/quote]
按升序排列就行,就是丙丁甲乙。[/quote]
DECLARE @S VARCHAR(2000) = (SELECT T3, 'AS ' + T3 AS NOTDO FROM # GROUP BY T3 ORDER BY LEN(T3),T3 FOR XML PATH)
SET @S = REPLACE(@S, '<row><T3>', ',COUNT(CASE T3 WHEN ''')
SET @S = REPLACE(@S, '</T3><NOTDO>', ''' THEN 1 END) ')
SET @S = REPLACE(@S, '</NOTDO></row>', '')
SET @S = LEFT(@S,LEN(@S)-1)
SET @S = 'SELECT T1' + @S + ' FROM # GROUP BY T1 ORDER BY T1
PRINT @S
EXECUTE(@S)
[/quote]
谢谢哈,费心了。
DECLARE @Sql NVARCHAR(max)
SET @Sql='SELECT T1'
SELECT @Sql=@Sql+','+QUOTENAME(T3)+'=ISNULL(RTRIM(SUM(CASE WHEN T3='+QUOTENAME(T3,'''')+' THEN 1 END)),'''')'
FROM T ORDER BY LEN(T3)
EXEC(@Sql+' FROM T GROUP BY T1')
DECLARE @Sql NVARCHAR(max)
SET @Sql='SELECT T1'
SELECT @Sql=@Sql+','+QUOTENAME(T3)+'=ISNULL(RTRIM(SUM(CASE WHEN T3='+QUOTENAME(T3,'''')+' THEN 1 END)),'''')'
FROM T ORDER BY LEN(T3)
EXEC(@Sql+' FROM T GROUP BY T1')
[/quote]
这个也有问题,上边有个运行不对的图片。DECLARE @Sql NVARCHAR(max)
SET @Sql='SELECT T1'
SELECT @Sql=@Sql+','+QUOTENAME(T3)+'=ISNULL(RTRIM(SUM(CASE WHEN T3='+QUOTENAME(T3,'''')+' THEN 1 END)),'''')'
FROM (SELECT T3,ROW_NUMBER()OVER(ORDER BY LEN(T3),T3) AS RN FROM T GROUP BY T3) AS T
ORDER BY RN
EXEC(@Sql+' FROM T GROUP BY T1')
DECLARE @Sql NVARCHAR(max)
SET @Sql='SELECT T1'
SELECT @Sql=@Sql+','+QUOTENAME(T3)+'=ISNULL(RTRIM(SUM(CASE WHEN T3='+QUOTENAME(T3,'''')+' THEN 1 END)),'''')'
FROM T ORDER BY LEN(T3)
EXEC(@Sql+' FROM T GROUP BY T1')
[/quote]
这个也有问题,上边有个运行不对的图片。[/quote]
DECLARE @S VARCHAR(2000) = (SELECT T3, 'AS ' + T3 AS NOTDO FROM # GROUP BY T3 ORDER BY LEN(T3),T3 FOR XML PATH)
SET @S = REPLACE(@S, '<row><T3>', ',COUNT(CASE T3 WHEN ''')
SET @S = REPLACE(@S, '</T3><NOTDO>', ''' THEN 1 END) ')
SET @S = REPLACE(@S, '</NOTDO></row>', '')
SET @S = LEFT(@S,LEN(@S)-1)
SET @S = 'SELECT T1' + @S + ' FROM # GROUP BY T1 ORDER BY CASE T1 WHEN ''' + '甲' + ''' THEN 1 WHEN ''' + '乙' + ''' THEN 2 WHEN ''' + '丙' + ''' THEN 3 WHEN ''' + '丁' + ''' THEN 4 END'
PRINT @S
EXECUTE(@S)
[/quote]
T1列是自动增加的,不限于甲乙丙丁的。[/quote]
汉字的排序是根据拼音字母来的,所以要转换才能精准,你T1自动递增的话,肯定得有个标准出来,是数字还是字符还是别的什么,这样才能正确排序[/quote]
按升序排列就行,就是丙丁甲乙。[/quote]
DECLARE @S VARCHAR(2000) = (SELECT T3, 'AS ' + T3 AS NOTDO FROM # GROUP BY T3 ORDER BY LEN(T3),T3 FOR XML PATH)
SET @S = REPLACE(@S, '<row><T3>', ',COUNT(CASE T3 WHEN ''')
SET @S = REPLACE(@S, '</T3><NOTDO>', ''' THEN 1 END) ')
SET @S = REPLACE(@S, '</NOTDO></row>', '')
SET @S = LEFT(@S,LEN(@S)-1)
SET @S = 'SELECT T1' + @S + ' FROM # GROUP BY T1 ORDER BY T1
PRINT @S
EXECUTE(@S)
DECLARE @Sql NVARCHAR(max)
SET @Sql='SELECT T1'
SELECT @Sql=@Sql+','+QUOTENAME(T3)+'=ISNULL(RTRIM(SUM(CASE WHEN T3='+QUOTENAME(T3,'''')+' THEN 1 END)),'''')'
FROM T ORDER BY LEN(T3)
EXEC(@Sql+' FROM T GROUP BY T1')
[/quote]
这个也有问题,上边有个运行不对的图片。[/quote]
DECLARE @S VARCHAR(2000) = (SELECT T3, 'AS ' + T3 AS NOTDO FROM # GROUP BY T3 ORDER BY LEN(T3),T3 FOR XML PATH)
SET @S = REPLACE(@S, '<row><T3>', ',COUNT(CASE T3 WHEN ''')
SET @S = REPLACE(@S, '</T3><NOTDO>', ''' THEN 1 END) ')
SET @S = REPLACE(@S, '</NOTDO></row>', '')
SET @S = LEFT(@S,LEN(@S)-1)
SET @S = 'SELECT T1' + @S + ' FROM # GROUP BY T1 ORDER BY CASE T1 WHEN ''' + '甲' + ''' THEN 1 WHEN ''' + '乙' + ''' THEN 2 WHEN ''' + '丙' + ''' THEN 3 WHEN ''' + '丁' + ''' THEN 4 END'
PRINT @S
EXECUTE(@S)
[/quote]
T1列是自动增加的,不限于甲乙丙丁的。[/quote]
汉字的排序是根据拼音字母来的,所以要转换才能精准,你T1自动递增的话,肯定得有个标准出来,是数字还是字符还是别的什么,这样才能正确排序[/quote]
按升序排列就行,就是丙丁甲乙。DECLARE @Sql NVARCHAR(max)
SET @Sql='SELECT T1'
SELECT @Sql=@Sql+','+QUOTENAME(T3)+'=ISNULL(RTRIM(SUM(CASE WHEN T3='+QUOTENAME(T3,'''')+' THEN 1 END)),'''')'
FROM (SELECT T3,ROW_NUMBER()OVER(ORDER BY LEN(T3),T3) AS RN FROM T) AS T
ORDER BY RN
EXEC(@Sql+' FROM T GROUP BY T1')
改改,测试一下结果对不
DECLARE @Sql NVARCHAR(max)
SET @Sql='SELECT T1'
SELECT @Sql=@Sql+','+QUOTENAME(T3)+'=ISNULL(RTRIM(SUM(CASE WHEN T3='+QUOTENAME(T3,'''')+' THEN 1 END)),'''')'
FROM T ORDER BY LEN(T3)
EXEC(@Sql+' FROM T GROUP BY T1')
[/quote]
这个也有问题,上边有个运行不对的图片。[/quote]
DECLARE @S VARCHAR(2000) = (SELECT T3, 'AS ' + T3 AS NOTDO FROM # GROUP BY T3 ORDER BY LEN(T3),T3 FOR XML PATH)
SET @S = REPLACE(@S, '<row><T3>', ',COUNT(CASE T3 WHEN ''')
SET @S = REPLACE(@S, '</T3><NOTDO>', ''' THEN 1 END) ')
SET @S = REPLACE(@S, '</NOTDO></row>', '')
SET @S = LEFT(@S,LEN(@S)-1)
SET @S = 'SELECT T1' + @S + ' FROM # GROUP BY T1 ORDER BY CASE T1 WHEN ''' + '甲' + ''' THEN 1 WHEN ''' + '乙' + ''' THEN 2 WHEN ''' + '丙' + ''' THEN 3 WHEN ''' + '丁' + ''' THEN 4 END'
PRINT @S
EXECUTE(@S)
[/quote]
T1列是自动增加的,不限于甲乙丙丁的。[/quote]
汉字的排序是根据拼音字母来的,所以要转换才能精准,你T1自动递增的话,肯定得有个标准出来,是数字还是字符还是别的什么,这样才能正确排序
DECLARE @Sql NVARCHAR(max)
SET @Sql='SELECT T1'
SELECT @Sql=@Sql+','+QUOTENAME(T3)+'=ISNULL(RTRIM(SUM(CASE WHEN T3='+QUOTENAME(T3,'''')+' THEN 1 END)),'''')'
FROM T ORDER BY LEN(T3)
EXEC(@Sql+' FROM T GROUP BY T1')
[/quote]
这个也有问题,上边有个运行不对的图片。[/quote]
DECLARE @S VARCHAR(2000) = (SELECT T3, 'AS ' + T3 AS NOTDO FROM # GROUP BY T3 ORDER BY LEN(T3),T3 FOR XML PATH)
SET @S = REPLACE(@S, '<row><T3>', ',COUNT(CASE T3 WHEN ''')
SET @S = REPLACE(@S, '</T3><NOTDO>', ''' THEN 1 END) ')
SET @S = REPLACE(@S, '</NOTDO></row>', '')
SET @S = LEFT(@S,LEN(@S)-1)
SET @S = 'SELECT T1' + @S + ' FROM # GROUP BY T1 ORDER BY CASE T1 WHEN ''' + '甲' + ''' THEN 1 WHEN ''' + '乙' + ''' THEN 2 WHEN ''' + '丙' + ''' THEN 3 WHEN ''' + '丁' + ''' THEN 4 END'
PRINT @S
EXECUTE(@S)
[/quote]
T1列是自动增加的,不限于甲乙丙丁的。
DECLARE @Sql NVARCHAR(max)
SET @Sql='SELECT T1'
SELECT @Sql=@Sql+','+QUOTENAME(T3)+'=ISNULL(RTRIM(SUM(CASE WHEN T3='+QUOTENAME(T3,'''')+' THEN 1 END)),'''')'
FROM T ORDER BY LEN(T3)
EXEC(@Sql+' FROM T GROUP BY T1')
[/quote]
这个也有问题,上边有个运行不对的图片。[/quote]
DECLARE @S VARCHAR(2000) = (SELECT T3, 'AS ' + T3 AS NOTDO FROM # GROUP BY T3 ORDER BY LEN(T3),T3 FOR XML PATH)
SET @S = REPLACE(@S, '<row><T3>', ',COUNT(CASE T3 WHEN ''')
SET @S = REPLACE(@S, '</T3><NOTDO>', ''' THEN 1 END) ')
SET @S = REPLACE(@S, '</NOTDO></row>', '')
SET @S = LEFT(@S,LEN(@S)-1)
SET @S = 'SELECT T1' + @S + ' FROM # GROUP BY T1 ORDER BY CASE T1 WHEN ''' + '甲' + ''' THEN 1 WHEN ''' + '乙' + ''' THEN 2 WHEN ''' + '丙' + ''' THEN 3 WHEN ''' + '丁' + ''' THEN 4 END'
PRINT @S
EXECUTE(@S)
DECLARE @Sql NVARCHAR(max)
SET @Sql='SELECT T1'
SELECT @Sql=@Sql+','+QUOTENAME(T3)+'=ISNULL(RTRIM(SUM(CASE WHEN T3='+QUOTENAME(T3,'''')+' THEN 1 END)),'''')'
FROM T ORDER BY LEN(T3),LEN(T3)
EXEC(@Sql+' FROM T GROUP BY T1')