查询GROUP BY和ORDER BY

问题描述：

SELECT 
    v.idutente AS idutente, 
    vl.idvideo AS idvideo, 
    (vl.likeY-vl.unlikeY) AS sott 
FROM video_likeunlike AS vl 
LEFT OUTER JOIN video AS v 
ON vl.idvideo = v.ID 
WHERE status = '1'

我从表

idutente idvideo likeY  unlikeY sott 
------------------------------------------------ 
1    70  2   5   -3 
2    81  6   10  -4 
1    52  200   198  2 
1    54  30   2   28 
2    67  20   5   15 
3    22  12   0   12

这个数据，我需要这样的结果，有可能吗？

idutente likeY  unlikeY sott 
------------------------------------------------ 
1   232   205  27 
2   26   15   11 
3   12   0   12

我尝试此查询

SELECT 
    v.idutente AS idutente, 
    vl.idvideo AS idvideo, 
    vl.likeY as likeY, 
    vl.unlikeY as unlikeY, 
    (vl.likeY-vl.unlikeY) AS sott 
FROM video_likeunlike AS vl 
LEFT OUTER JOIN video AS v 
ON vl.idvideo = v.ID 
WHERE status = '1' 
GROUP BY v.idutente 
ORDER BY sott DESC

您正在使用2台在您尝试查询，但你仅列1中的问题表。 – wast

请参阅：[为什么我应该为我认为是非常简单的SQL查询提供一个MCVE？]（https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve -for - 什么 - 似乎对我将要-A-极简单的SQL查询） – Strawberry

答

请对选择列使用聚合SUM（）操作。查询可修改为：

SELECT v.idutente AS idutente, 
     vl.idvideo AS idvideo, 
     SUM(vl.likeY) AS likeY, 
     SUM(vl.unlikeY) AS unlikeY, 
     SUM(vl.likeY-vl.unlikeY) AS sott 
FROM video_likeunlike AS vl 
LEFT OUTER JOIN video AS v ON vl.idvideo = v.ID 
WHERE status = '1' 
GROUP BY v.idutente 
ORDER BY sott DESC

答

看起来你要使用的SQL sum()功能

SELECT 
    v.idutente AS idutente, 
    sum(vl.likeY) as likeY, 
    sum(vl.unlikeY) as unlikeY, 
    sum(vl.likeY)-sum(vl.unlikeY) AS sott 
FROM video_likeunlike AS vl 
LEFT OUTER JOIN video AS v 
ON vl.idvideo = v.ID 
WHERE status = '1' 
GROUP BY v.idutente 
ORDER BY sott DESC

答

SELECT idutente, 
    SUM(likeY) likeY, 
    SUM(unlikeY) unlikeY, 
    SUM(sott) sott 
FROM mytable 
GROUP BY idutente 
ORDER BY sott DESC

尝试在SQL Fiddle。

答

将您的第一个查询用作子查询并执行group by。

查询

SELECT t.idutente, SUM(t.likeY) as likeY, SUM(t.unlikeY) as unlikeY from(
    SELECT 
     v.idutente AS idutente, 
     vl.idvideo AS idvideo, 
     vl.likeY, 
     vl.unlikeY, 
     (vl.likeY-vl.unlikeY) AS sott 
    FROM video_likeunlike AS vl 
    LEFT OUTER JOIN video AS v 
    ON vl.idvideo = v.ID 
    WHERE status = '1' 
) t 
GROUP BY t.idutente 
ORDER t.sott desc;

查询GROUP BY和ORDER BY

相关推荐