如何获取表中相同记录的总和/数量 [英] How to get sum / Qty of same records in a table

问题描述

我有两张桌子公司和公司喜欢



CompanyLike

 Id 
 ratingStar 
数
 Companyid 

公司与公司有1对多的关系。一家公司可以有很多喜欢



公司喜欢的桌子看起来像这样

 + ---------------------------- + 
 | ID RatingStar Companyid | 
 + ---------------------------- + 
 | 1 5 5636 | 
 | 2 5 5636 | 
 | 3 1 101 | 
 | 4 2 959 | 
 + ---------------------------- + 

现在我想得到评级明星的评分，评分明星是多少次5，以及2对每个Companyid的时间多少



我想要这样的结果

 + ---------------------------- -------------------------------------------------- ------------- + 
 | ID Companyid评级Star1评级星级2星级等级星级4星级星级5 | 
 + --------------------------------------------- ---------------------------------------------- + 
 | 1 5636 0 0 0 0 2 | 
 | 1 101 0 0 0 0 0 | 
 | 1 959 1 1 0 0 0 | 
 + --------------------------------------------- ---------------------------------------------- + 

怎么做？



代码块添加 - OriginalGriff [ / edit]

解决方案

尝试以下查询：

  DECLARE   @cols   NVARCHAR （ 200 ）
 
   -   评级星 
  SET   @cols  = '  [1]，[2]，[3]，[4]，[5]' 
 
  SELECT  [CompanyID]， @cols  
  FROM （ SELECT  *  FROM 公司） AS  DT 
 PIVOT（COUNT（[CompanyId]） FOR  [RatingStar]  IN （ @cols ））

结果：

 CompanyID 1 2 3 4 5 
 5636 0 0 0 0 2 
 ... 

 选择 RatingStar  as  RatingStar，com .id  as  Companyid，com.Name  as  
 CompanyName，
 
计数（ratingStar） as 计数，MIN（c.ID） as  Id 
 来自 CompanyReviewLike  as  c 
  inner  < span class =code-keyword> join 公司 as  com  on  com.id = c.Companyid 
  Group  按 
 RatingStar，com.Name，com.id 

I have two tables Company and CompanyLike

CompanyLike

Id
RatingStar
Count
Companyid

Company and CompanyLike have 1 to Many relation . One company can have many likes

Company Like Table Will Look Like this

+----------------------------+
| ID  RatingStar   Companyid |
+----------------------------+
| 1      5           5636    |
| 2      5           5636    |
| 3      1           101     |
| 4      2            959    |
+----------------------------+

Now i want to get count of rating star that how many times RatingStar is 5 , and how many time its 2 against every Companyid

I want my result like this

+-------------------------------------------------------------------------------------------+
| ID     Companyid    RatingStar1   RatingStar2   RatingStar3   RatingStar4   RatingStar5   |
+-------------------------------------------------------------------------------------------+
| 1       5636               0           0               0          0             2         |
| 1       101                0           0               0          0             0         |
| 1       959                1           1               0          0             0         |
+-------------------------------------------------------------------------------------------+

How to do this ?

[edit]Code block added - OriginalGriff[/edit]

解决方案

Try below query:

DECLARE @cols NVARCHAR(200)

--rating star
SET @cols = '[1],[2],[3],[4],[5]'

SELECT [CompanyID], @cols
FROM (SELECT * FROM Company) AS DT
PIVOT (COUNT([CompanyId]) FOR [RatingStar] IN (@cols))

Result:

CompanyID    1     2    3   4   5
5636    0    0     0    0    2
...

select RatingStar as RatingStar,com.id as Companyid  ,com.Name as
         CompanyName ,

 Count(RatingStar) as Count,MIN(c.ID) as Id
     from CompanyReviewLike as c
   inner join Company as com on com.id = c.Companyid
 Group By
 RatingStar ,com.Name , com.id

这篇关于如何获取表中相同记录的总和/数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！