use Tempdb
go
--> -->
if not object_id(N'Tempdb..#kehu_rhypp') is null
drop table #kehu_rhypp
Go
Create table #kehu_rhypp([id] int,[PP_mc] nvarchar(50))
Insert #kehu_rhypp
select 1,N'引航' union all
select 2,N'壳牌' union all
select 3,N'龙蟠' union all
select 4,N'康普顿' union all
select 5,N'角马' union all
select 6,N'统一'
Go
if not object_id(N'Tempdb..#Kehu_Info') is null
drop table #Kehu_Info
Go
Create table #Kehu_Info([KehuMc] nvarchar(5),[jyrhypp] nvarchar(12))
Insert #Kehu_Info
select N'张三修理厂',N'引航,龙蟠' union all
select N'李四修理厂',N'壳牌,引航' union all
select N'王二修理厂',N'角马,引航,壳牌' union all
select N'小小修理厂',N'康普顿,壳牌,龙蟠,引航' union all
select N'小王修理厂',N'统一'
Go
SELECT TOP 3 a.[PP_mc],COUNT(1) AS [次数]
FROM #kehu_rhypp AS a
INNER JOIN #Kehu_Info AS b ON ','+b.[jyrhypp]+',' LIKE '%,'+a.[PP_mc]+',%'
GROUP BY a.[PP_mc]
ORDER BY [次数] DESC
/*
PP_mc 次数
引航 4
壳牌 3
龙蟠 2
*/
go
--> -->
if not object_id(N'Tempdb..#kehu_rhypp') is null
drop table #kehu_rhypp
Go
Create table #kehu_rhypp([id] int,[PP_mc] nvarchar(50))
Insert #kehu_rhypp
select 1,N'引航' union all
select 2,N'壳牌' union all
select 3,N'龙蟠' union all
select 4,N'康普顿' union all
select 5,N'角马' union all
select 6,N'统一'
Go
if not object_id(N'Tempdb..#Kehu_Info') is null
drop table #Kehu_Info
Go
Create table #Kehu_Info([KehuMc] nvarchar(5),[jyrhypp] nvarchar(12))
Insert #Kehu_Info
select N'张三修理厂',N'引航,龙蟠' union all
select N'李四修理厂',N'壳牌,引航' union all
select N'王二修理厂',N'角马,引航,壳牌' union all
select N'小小修理厂',N'康普顿,壳牌,龙蟠,引航' union all
select N'小王修理厂',N'统一'
Go
SELECT TOP 3 a.[PP_mc],COUNT(1) AS [次数]
FROM #kehu_rhypp AS a
INNER JOIN #Kehu_Info AS b ON ','+b.[jyrhypp]+',' LIKE '%,'+a.[PP_mc]+',%'
GROUP BY a.[PP_mc]
ORDER BY [次数] DESC
/*
PP_mc 次数
引航 4
壳牌 3
龙蟠 2
*/
解决方案 »
- 根据一个字段的值不同插入一条记值不同到不同的字段
- 查询表tsc-bzx中id是否有重复的纪录
- SQL2008 连接问题
- 问个sql语句的问题,在线等!!!马上给分哦!!
- 超级简单的一个问题:如何在sql server 2000中从一个脚本文件生成一个数据库
- 关于数据库删除后再添加的问题
- 请教库存呆料的分析SQL脚本思路
- 请教:用户的密码如果要存储在数据库中,用什么方法可以具备较好的安全性
- 求教大家!有谁知道Sybase Adaptive Server Anywhere的Mail复制?帮帮我!
- select * from 表 where parentid="&pid parentid改为非数值型字段类型,怎么写?
- SQL2005连接不上SQL2012
- 关于查询结果保留小数位数的问题!
go
--> -->
if not object_id(N'Tempdb..#kehu_rhypp') is null
drop table #kehu_rhypp
Go
Create table #kehu_rhypp([id] int,[PP_mc] nvarchar(50))
Insert #kehu_rhypp
select 1,N'引航' union all
select 2,N'壳牌' union all
select 3,N'龙蟠' union all
select 4,N'康普顿' union all
select 5,N'角马' union all
select 6,N'统一'
Go
if not object_id(N'Tempdb..#Kehu_Info') is null
drop table #Kehu_Info
Go
Create table #Kehu_Info([KehuMc] nvarchar(5),[jyrhypp] nvarchar(12))
Insert #Kehu_Info
select N'张三修理厂',N'引航,龙蟠' union all
select N'李四修理厂',N'壳牌,引航' union all
select N'王二修理厂',N'角马,引航,壳牌' union all
select N'小小修理厂',N'康普顿,壳牌,龙蟠,引航' union all
select N'小王修理厂',N'统一'
Go;WITH Cte
AS
(SELECT a.[PP_mc],COUNT(1) AS [次数]
FROM #kehu_rhypp AS a
INNER JOIN #Kehu_Info AS b ON ','+b.[jyrhypp]+',' LIKE '%,'+a.[PP_mc]+',%'
GROUP BY a.[PP_mc]
)
SELECT CASE WHEN b.[PP_mc] IS NULL THEN N'其它'
ELSE a.[PP_mc]
END AS [PP_mc]
,SUM([次数]) AS [次数]
FROM Cte AS a
LEFT JOIN ( SELECT TOP 3
[PP_mc]
FROM Cte
ORDER BY [次数] DESC
) AS b ON a.[PP_mc] = b.[PP_mc]
GROUP BY CASE WHEN b.[PP_mc] IS NULL THEN N'其它'
ELSE a.[PP_mc]
END,b.[PP_mc]
ORDER BY CASE WHEN b.[PP_mc] IS NULL THEN 2 ELSE 1 END,[次数] DESC
/*
PP_mc 次数
引航 4
壳牌 3
龙蟠 2
其它 3
*/
(SELECT CASE WHEN b.[PP_mc] IS NULL THEN N'其它'
ELSE a.[PP_mc]
END AS [PP_mc]
,SUM([次数]) AS [次数]
FROM ( SELECT a.[PP_mc]
,COUNT(1) AS [次数]
FROM #kehu_rhypp AS a
INNER JOIN #Kehu_Info AS b ON ',' + b.[jyrhypp] + ',' LIKE '%,'
+ a.[PP_mc] + ',%'
GROUP BY a.[PP_mc]
) AS a
LEFT JOIN ( SELECT TOP 3
[PP_mc]
FROM #kehu_rhypp AS a
INNER JOIN #Kehu_Info AS b ON ',' + b.[jyrhypp]
+ ',' LIKE '%,'
+ a.[PP_mc] + ',%'
GROUP BY a.[PP_mc]
ORDER BY COUNT(1) DESC
) AS b ON a.[PP_mc] = b.[PP_mc]
GROUP BY CASE WHEN b.[PP_mc] IS NULL THEN N'其它'
ELSE a.[PP_mc]
END
,b.[PP_mc]
ORDER BY CASE WHEN b.[PP_mc] IS NULL THEN 2
ELSE 1
END
,[次数] DESC)
SELECT a.[PP_mc],COUNT(1) AS [次数]
FROM #kehu_rhypp AS a
INNER JOIN #Kehu_Info AS b ON ','+b.[jyrhypp]+',' LIKE '%,'+a.[PP_mc]+',%'
WHERE a.[id]<=3
GROUP BY a.[PP_mc]
UNION ALL
SELECT '其他',COUNT(1) AS [次数]
FROM #kehu_rhypp AS a
INNER JOIN #Kehu_Info AS b ON ','+b.[jyrhypp]+',' LIKE '%,'+a.[PP_mc]+',%'
WHERE a.[id]>3