新手问题 ,谢谢大家表11.7788788877777878777877787877787787778788877887777778787878
2.88888778788777888877788778887878788787887787888778887887888
.........
把表1的数据 每隔3个数分开,就是3个数变成1个数,然后统计出现的次数如表1数据,分成如下:778 878 887 777 787 877 787 778 787 778 778 777 878 887 788 777 777 878 787 8
888 887 787 887 778 888 777 887 788 878 787 887 878 877 878 887 788 878 878 88
得结果表2
1 778 4次 878 3次 887 2次 777 4次 787 4次 877 1次 788 1次
2.....
2.88888778788777888877788778887878788787887787888778887887888
.........
把表1的数据 每隔3个数分开,就是3个数变成1个数,然后统计出现的次数如表1数据,分成如下:778 878 887 777 787 877 787 778 787 778 778 777 878 887 788 777 777 878 787 8
888 887 787 887 778 888 777 887 788 878 787 887 878 877 878 887 788 878 878 88
得结果表2
1 778 4次 878 3次 887 2次 777 4次 787 4次 877 1次 788 1次
2.....
-- Author :SQL77(只为思齐老)
-- Date :2010-02-26 18:05:26
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([A] INT,[B] VARCHAR(5000))
insert #TB
select 1,'7788788877777878777877787877787787778788877887777778787878' union all
select 2,'88888778788777888877788778887878788787887787888778887887888'
--------------开始查询--------------------------
SELECT A,B,COUNT(*)AS NUM
FROM (
select A,SUBSTRING(B,NUMBER,3)AS B
from #TB T ,MASTER..SPT_VALUES
WHERE TYPE='P' AND (NUMBER-1)%3=0)AS T WHERE B<>'' GROUP BY A,B
----------------结果----------------------------
/* (所影响的行数为 2 行)A B NUM
----------- ------ -----------
1 777 4
2 777 1
1 778 4
2 778 1
1 787 4
2 787 2
1 788 1
2 788 2
1 8 1
1 877 1
2 877 1
1 878 3
2 878 5
2 88 1
1 887 2
2 887 5
2 888 2(所影响的行数为 17 行)
*/
-- Author :SQL77(只为思齐老)
-- Date :2010-02-26 18:05:26
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([A] INT,[B] VARCHAR(5000))
insert #TB
select 1,'7788788877777878777877787877787787778788877887777778787878' union all
select 2,'88888778788777888877788778887878788787887787888778887887888'
--------------开始查询--------------------------
SELECT A,B,COUNT(*)AS NUM
FROM (
select A,SUBSTRING(B,NUMBER,3)AS B
from #TB T ,MASTER..SPT_VALUES
WHERE TYPE='P' AND (NUMBER-1)%3=0)AS T
WHERE B<>'' GROUP BY A,B
ORDER BY A,CONVERT(INT,B)
----------------结果----------------------------
/*
(所影响的行数为 2 行)A B NUM
----------- ------ -----------
1 8 1
1 777 4
1 778 4
1 787 4
1 788 1
1 877 1
1 878 3
1 887 2
2 88 1
2 777 1
2 778 1
2 787 2
2 788 2
2 877 1
2 878 5
2 887 5
2 888 2(所影响的行数为 17 行)*/
加排序
-- Author :SQL77(只为思齐老)
-- Date :2010-02-26 18:05:26
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([A] INT,[B] VARCHAR(5000))
insert #TB
select 1,'7788788877777878777877787877787787778788877887777778787878' union all
select 2,'88888778788777888877788778887878788787887787888778887887888'
--------------开始查询--------------------------
SELECT A,B,COUNT(*)AS NUM INTO #T
FROM (
select A,SUBSTRING(B,NUMBER,3)AS B
from #TB T ,MASTER..SPT_VALUES
WHERE TYPE='P' AND (NUMBER-1)%3=0)AS T
WHERE B<>'' AND LEN(B)=3 GROUP BY A,B
ORDER BY A,CONVERT(INT,B)--SELECT * FROM #TDECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT A'
SELECT @SQL=@SQL+',MAX(CASE WHEN B='+LTRIM(B)+' THEN LTRIM(NUM)+''次'' ELSE ''0次'' END)AS '''+LTRIM(B)+''''
FROM (SELECT DISTINCT B FROM #T)AS TEXEC( @SQL+' FROM #T GROUP BY A')
----------------结果----------------------------
/*
A 777 778 787 788 877 878 887 888
----------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- --------------
1 4次 4次 4次 1次 1次 3次 2次 0次
2 1次 1次 2次 2次 1次 5次 5次 2次
*/