--建立表TB1
if object_id('tempdb..#tb1','U') is not null drop table #tb1
go
create table #tb1(notext nchar(22)) --检查 select * from #tb1
go
insert into #tb1
SELECT'01 11 12 17 27 31'
UNION ALL SELECT'01 11 12 17 28 31'
UNION ALL SELECT'01 11 12 21 24 30'
UNION ALL SELECT'01 11 12 22 28 31'
UNION ALL SELECT'01 11 12 24 28 31'
UNION ALL SELECT'01 11 12 25 28 29'
UNION ALL SELECT'01 11 13 22 23 30'
UNION ALL SELECT'01 11 13 24 26 30'
UNION ALL SELECT'01 11 13 24 30 31'
UNION ALL SELECT'01 11 15 19 24 30'
UNION ALL SELECT'01 11 15 20 30 33'
UNION ALL SELECT'01 11 16 26 28 31'
UNION ALL SELECT'01 11 17 18 24 29'
UNION ALL SELECT'01 11 17 19 28 31'
UNION ALL SELECT'01 11 17 26 28 31'
UNION ALL SELECT'01 11 17 26 31 33'
UNION ALL SELECT'01 11 17 28 29 31'
UNION ALL SELECT'01 11 18 19 23 31'
UNION ALL SELECT'01 11 18 24 26 31'
UNION ALL SELECT'01 11 19 24 26 30'
-测试语句需要解决的问题
请用SQL语句 统计表TB1里每一个号码出现的次数（号码范围是1——35之间的35个自然数）
楼主需要的结果是
号码 统计次数
1 20
2 0
3 0
4 0
5 0
6 0
7 0
8 0
9 0
10 0
11 20
12 6
13 3
14 0
15 2
16 1
17 7
18 3
19 4
20 1
21 1
22 2
23 2
24 8
25 1
26 6
27 1
28 8
29 3
30 7
31 12
32 0
33 2
34 0
35 0
------解决思路----------------------
;WITH CTE AS(
SELECT T2.V FROM
(SELECT CAST('<V>'+REPLACE(RTRIM(notext),' ','</V><V>')+'</V>' AS XML)VS FROM #tb1)T1
CROSS APPLY(SELECT N.V.value('.','VARCHAR(100)')V FROM T1.VS.nodes('/V') N(V))T2
)
,CTE2 AS(
SELECT V,ROW_NUMBER()OVER(ORDER BY V)RN
FROM CTE
GROUP BY V
)
SELECT T1.number[号码],COUNT(T2.V)[统计次数],T3.V 出现的号码
FROM master..spt_values T1
LEFT JOIN CTE T2 ON T1.number=T2.V
LEFT JOIN CTE2 T3 ON T1.number=T3.RN
WHERE T1.type='P'AND T1.number BETWEEN 1 AND 35
GROUP BY T1.number,T3.V
ORDER BY T1.number
------解决思路----------------------
if object_id('tempdb..#tb1','U') is not null drop table #tb1
go
create table #tb1(notext nchar(22)) --检查 select * from #tb1
go
insert into #tb1
SELECT '01 11 12 17 27 31'
UNION ALL SELECT'01 11 12 17 28 31'
UNION ALL SELECT'01 11 12 21 24 30'
UNION ALL SELECT'01 11 12 22 28 31'
UNION ALL SELECT'01 11 12 24 28 31'
UNION ALL SELECT'01 11 12 25 28 29'
UNION ALL SELECT'01 11 13 22 23 30'
UNION ALL SELECT'01 11 13 24 26 30'
UNION ALL SELECT'01 11 13 24 30 31'
UNION ALL SELECT'01 11 15 19 24 30'
UNION ALL SELECT'01 11 15 20 30 33'
UNION ALL SELECT'01 11 16 26 28 31'
UNION ALL SELECT'01 11 17 18 24 29'
UNION ALL SELECT'01 11 17 19 28 31'
UNION ALL SELECT'01 11 17 26 28 31'
UNION ALL SELECT'01 11 17 26 31 33'
UNION ALL SELECT'01 11 17 28 29 31'
UNION ALL SELECT'01 11 18 19 23 31'
UNION ALL SELECT'01 11 18 24 26 31'
UNION ALL SELECT'01 11 19 24 26 30'
;
with t AS
(
SELECT CAST(SUBSTRING(notext, 1, 2) AS TINYINT) num FROM #tb1
UNION ALL SELECT CAST(SUBSTRING(notext, 4, 2) AS TINYINT) FROM #tb1
UNION ALL SELECT CAST(SUBSTRING(notext, 7, 2) AS TINYINT) FROM #tb1
UNION ALL SELECT CAST(SUBSTRING(notext, 10, 2) AS TINYINT) FROM #tb1
UNION ALL SELECT CAST(SUBSTRING(notext, 13, 2) AS TINYINT) FROM #tb1
UNION ALL SELECT CAST(SUBSTRING(notext, 16, 2) AS TINYINT) FROM #tb1
), t2 AS
(
SELECT num, COUNT(t.num) cnt
, rn=ROW_NUMBER() OVER(ORDER BY num)
FROM t
GROUP BY num
)
SELECT t3.number 号码, ISNULL(t2.cnt, 0) 统计次数, t4.num 出现的号码
FROM master..spt_values t3 LEFT JOIN t2 ON t2.num=t3.number
LEFT JOIN t2 t4 ON t3.number=t4.rn
WHERE t3.type='P' AND t3.number>0 AND t3.number<36
ORDER BY t3.number
------解决思路----------------------
select ROW_NUMBER()over(order by col),col,count(1) countnumber from
(
SELECT SUBSTRING(REPLACE(A.notext,' ',''),number,2) col FROM #tb1 A
JOIN master..spt_values B ON B.type='P' and number >= 1 AND number <=11 AND number%2 =1
)a
group by col
------解决思路----------------------
WITH t1 AS (
SELECT n.number 号码,
COUNT(#tb1.notext) 统计次数
FROM (SELECT number,
RIGHT(N'0'+Convert(nvarchar(2), number),2) code
FROM master..spt_values
WHERE type = 'p'
AND number BETWEEN 1 AND 35
) n
LEFT JOIN #tb1
ON CHARINDEX(n.code, #tb1.notext) <> 0
GROUP BY n.number
),
t2 AS (
SELECT 号码 出现的号码,
ROW_NUMBER() OVER(ORDER BY 号码) rn
FROM t1
WHERE 统计次数<>0
)
SELECT t1.号码,
t1.统计次数
t2.出现的号码
FROM t1
LEFT JOIN t2
ON t1.号码 = t2.rn
ORDER BY t1.号码