如何使用具有最大upvote值的MySQL count()检索值?
假设我们在表中有一些列,一列用于图像路径,另一列用于upvotes。但是,第一列是自动增量ID,如下所示-
mysql> create table DemoTable( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,ImagePath varchar(100),UpvoteValue int );
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable(ImagePath,UpvoteValue) values('Image1.jpeg',90); mysql> insert into DemoTable(ImagePath,UpvoteValue) values('Image2.jpeg',10); mysql> insert into DemoTable(ImagePath,UpvoteValue) values('Image3.jpeg',120); mysql> insert into DemoTable(ImagePath,UpvoteValue) values('Image4.jpeg',114);
使用select语句显示表中的所有记录-
mysql> select *from DemoTable;
这将产生以下输出-
+----+-------------+-------------+ | Id | ImagePath | UpvoteValue | +----+-------------+-------------+ | 1 | Image1.jpeg | 90 | | 2 | Image2.jpeg | 10 | | 3 | Image3.jpeg | 120 | | 4 | Image4.jpeg | 114 | +----+-------------+-------------+ 4 rows in set (0.00 sec)
这是检索count()
具有最大upvote值的图像路径值的查询-
mysql> select ImagePath from DemoTable where UpvoteValue IN (select max(UpvoteValue) from DemoTable);
这将产生以下输出>
+-------------+ | ImagePath | +-------------+ | Image3.jpeg | +-------------+ 1 row in set (0.00 sec)