如何从sql中的字段计算相同的评级

bq9c1y66 于 2021-06-18 发布在 Mysql

关注(0)|答案(2)|浏览(330)

我在sql中计算评级时遇到问题。我的数据是这样的：
数据

CREATE TABLE `restaurant` (
  `id_restaurant` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id_restaurant`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

insert  into `restaurant`(`id_restaurant`,`name`) values (1,'Mc Donald');
insert  into `restaurant`(`id_restaurant`,`name`) values (2,'KFC');

    CREATE TABLE `user` (
  `id_user` int(11) NOT NULL AUTO_INCREMENT,
  `userName` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id_user`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

insert  into `user`(`id_user`,`userName`) values (1,'Audey');

    CREATE TABLE `factors` (
  `factor_id` int(11) NOT NULL AUTO_INCREMENT,
  `factor_clean` int(11) NOT NULL DEFAULT '0',
  `factor_delicious` int(11) NOT NULL DEFAULT '0',
  `id_restaurant` int(11) DEFAULT NULL,
  `id_user` int(11) DEFAULT NULL,
  PRIMARY KEY (`factor_id`),
  KEY `id_restaurant` (`id_restaurant`),
  KEY `id_user` (`id_user`),
  CONSTRAINT `factors_ibfk_1` FOREIGN KEY (`id_restaurant`) REFERENCES `restaurant` (`id_restaurant`),
  CONSTRAINT `factors_ibfk_2` FOREIGN KEY (`id_user`) REFERENCES `user` (`id_user`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

    insert  into `factors`(`factor_id`,`factor_clean`,`factor_delicious`,`id_restaurant`,`id_user`) values (1,1,5,1,1);
insert  into `factors`(`factor_id`,`factor_clean`,`factor_delicious`,`id_restaurant`,`id_user`) values (2,0,5,1,1);
insert  into `factors`(`factor_id`,`factor_clean`,`factor_delicious`,`id_restaurant`,`id_user`) values (3,1,5,1,1);
insert  into `factors`(`factor_id`,`factor_clean`,`factor_delicious`,`id_restaurant`,`id_user`) values (4,3,3,1,1);

结果应该是这样的，显示所有的评分（1，2，3，4，5）和它们在字段中的计数 rating_clean , rating_delicious ，和 rating_clean
谢谢你的帮助。
但我得到的结果

SELECT COUNT(`factor_clean`+`factor_delicious`),'1' AS rating_1 FROM `factors` WHERE 1 GROUP BY `id_restaurant`

结果不应该这样
结果不应该是这样的，我的问题是，如何只选择factor_clean和factor_delicious，其中factor_clean=1和factor_delicious=1

sql mysql

来源：https://stackoverflow.com/questions/52791044/how-to-count-same-rating-from-field-in-sql

2条答案

按热度按时间

2admgd591#

使用 union all 要取消打印数据并进行聚合，请执行以下操作：

select id_restaurant, rating, count(*)
from ((select r.id_restaurant, r.rating_clean as rating, r.date
       from ratings r
      ) union all
      (select r.id_restaurant, r.rating_delicious, r.date
       from ratings r
      ) union all
      (select r.id_restaurant, r.rating_clean2, r.date
       from ratings r
      ) 
     ) r
group by id_restaurant, rating
order by id_restaurant, rating;

赞(0）回复(0）举报 2021-06-18

rqcrx0a62#

例如，这是一个解决方案，用于具有列评级为“美味”和“干净”的表（只有一个！）：
首先，你应该创建一个额外的表，我称之为因子：

CREATE TABLE `factors` (
 `factor_id` int(11) NOT NULL AUTO_INCREMENT,
 `factor_clean` int(11) NOT NULL DEFAULT '0',
 `factor_delicious` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`factor_id`)
)

接下来添加两条记录：

INSERT INTO `factors` (`factor_id`, `factor_clean`, `factor_delicious`) VALUES (NULL, '1', '0'), (NULL, '0', '1');

现在可以联接这些表并获得结果：

SELECT x.id_restaurant
     , (x.rating_clean * f.factor_clean) + (x.rating_delicious * f.factor_delicious) AS rating
     , count(*) 
  FROM your_table x
  JOIN factors f
 WHERE 1 
 GROUP 
    BY x.id_restaurant
     , rating

为了使用下一列( rating_third )，您应该和列 factor_third 至 factors ，插入新行 1 在本专栏中，最后添加 your_table.rating_third*factors.factor_third 合计 SELECT

赞(0）回复(0）举报 2021-06-18

我来回答

如何从sql中的字段计算相同的评级

2条答案

相关问题

热门标签

最新问答