怎么查询一个品牌在数据库里面出现的次数

如何查询一个品牌在数据库里面出现的次数
sql2000
请问如何查询一个品牌在数据里面出现的次数
目前表情况如下



表一、表名称kehu_rhypp

         id   PP_mc

        1    引航

        2    壳牌

        3    龙蟠

        4    康普顿

        5    角马

        6   统一


表二、表名称Kehu_Info


        KehuMc       jyrhypp

        张三修理厂    引航,龙蟠

        李四修理厂    壳牌,引航

        王二修理厂   角马,引航 壳牌

        小小修理厂   康普顿,壳牌,龙蟠,引航

        小王修理厂  统一

想要实现效果获得 3种占比最多的品牌名称剩下的显示其他



品牌名称  次数

引航     4

壳牌     3

龙蟠     2

其他     3

------解决思路----------------------
SQL2005以上用CTE，SQL2000版本把前三生成个别名表连接

SQL2000方法
(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)

怎么查询一个品牌在数据库里面出现的次数

相关推荐