美好的一天。

STRUCTURE TABLES AND ERROR WHEN EXECUTE QUERY ON SQLFIDDLE

我有一些 sql 查询:

第一次查询:

SELECT 
            n.Type AS Type,
            n.UserIdn AS UserIdn,
            u.Username AS Username,
            n.NewsIdn AS NewsIdn,
            n.Header AS Header,
            n.Text AS Text,
            n.Tags AS Tags,
            n.ImageLink AS ImageLink,
            n.VideoLink AS VideoLink,
            n.DateCreate AS DateCreate
        FROM News n 
        LEFT JOIN Users u ON n.UserIdn = u.UserIdn 

第二个查询:

SELECT 
    IFNULL(SUM(Type = 'up'),0) AS Uplikes, 
    IFNULL(SUM(Type = 'down'),0) AS Downlikes,
    (IFNULL(SUM(Type = 'up'),0) - IFNULL(SUM(Type = 'down'),0)) AS SumLikes
FROM JOIN Likes
WHERE NewsIdn=NewsIdn  //only for example- in main sql NewsIdn = value NewsIdn from row table News
ORDER BY UpLikes DESC

和树查询

SELECT 
count(*) as Favorit
Form Favorites
WHERE NewsIdn=NewsIdn  //only for example- in main sql NewsIdn = value NewsIdn from row table News

我想结合这两个查询，显示表 News 中的所有行，以及 Uplikes、DownLikes 和News 表中每个值 NewsIdn 的 Favorit 数量(即 Uplikes、DownLikes 的数量 和每行 News 的 Favorit 数量)，并按 Uplikes Desc 排序。

请告诉我怎么做？

P.S.: 结果我想要下一个值

TYPE  USERIDN USERNAME NEWSIDN HEADER TEXT                      TAGS     IMAGELINK       VIDEOLINK DATECREATE                      UPLIKES DOWNLIKES SUMLIKES FAVORIT
image 346412  test     260806  test                                      1388152519.jpg            December, 27 2013 08:55:27+0000 2       0          2       2
image 108546  test2    905554  test2  1231231111111111111111111 123. 123 1388153493.jpg            December, 27 2013 09:11:41+0000 1       0          1       0
text  108546  test2    270085  test3                            123 .123                           December, 27 2013 09:13:30+0000 1       0          1       0
image 108546  test2    764955  test4                                     1388192300.jpg            December. 27 2013 19:58:22+0000 0       1         -1       0

最佳答案

首先，您的表结构具有 varchar(30) 的所有“Idn”。看起来这些实际上是其他表的 ID 键，并且应该是整数以获得更好的索引和连接性能。

其次，这种类型的流程，尤其是基于 Web 的流程，是通过实际将这些列作为直接记录在记录中的计数器(例如:新闻表)来对喜欢、不喜欢和喜欢的值进行非规范化的完美示例。当一个人喜欢、不喜欢或成为最喜欢的人时，立即在上面盖章并完成它。如果您是第一次执行批量 sql-update，那么还可以在表上使用触发器来自动处理适本地更新计数。这样，您只需直接查询表并按您需要的顺序进行排序，而不需要查询所有连接到所有新闻的喜欢 +/- 记录并查看哪个是最好的。在新闻表上建立索引将是您最好的选择。

现在，也就是说，使用您现有的表结构，您可以通过预聚合查询并将它们作为别名加入到 sql FROM 子句中...类似

SELECT
      N.Type,
      N.UserIdn,
      U.UserName,
      N.NewsIdn,
      N.Header,
      N.Text,
      N.Tags,
      N.ImageLink,
      N.VideoLink,
      N.DateCreate,
      COALESCE( SumL.UpLikes, 0 ) as Uplikes,
      COALESCE( SumL.DownLikes, 0 ) as DownLikes,
      COALESCE( SumL.NetLikes, 0 ) as NetLikes,
      COALESCE( Fav.FavCount, 0 ) as FavCount
   from
      News N
         JOIN Users U
            ON N.UserIdn = U.UserIdn
         LEFT JOIN ( select 
                           L.NewsIdn,
                           SUM( L.Type = 'up' ) as UpLikes,
                           SUM( L.Type = 'down' ) as DownLikes,
                           SUM( ( L.Type = 'up' ) - ( L.Type = 'down' )) as NetLikes
                        from
                           Likes L
                        group by
                           L.NewsIdn ) SumL
            ON N.NewsIdn = SumL.NewsIdn
         LEFT JOIN ( select 
                           F.NewsIdn,
                           COUNT(*) as FavCount
                        from
                           Favorites F
                        group by
                           F.NewsIdn ) Fav
            ON N.NewsIdn = Fav.NewsIdn
   order by
      SumL.UpLikes DESC

同样，我不明白为什么您要为新闻表设置一个自动递增的数字 ID 列，然后将另一个值作为 NewsIdn 作为 varchar。我只想让这个和你的其他表直接引用 News.ID 列......为什么有两列代表相同的组件。显然，您正在聚合(喜欢、收藏)的每个表都应该具有您要加入或聚合(因此是 NewsIdn)列、UserIdn 等的任何此类标准的索引。

最后提醒一下，这种类型的查询总是针对您每次都喜欢、收藏的整个表运行聚合，并建议使用非规范化列来保存有人如此选择它们时的计数。如果您想显示或更新特定人员以更改他们喜欢/不喜欢/喜欢的状态，您可以随时返回原始表格。

您必须研究阅读触发器，因为每个数据库都有自己的处理语法。

至于表结构，这是我所拥有的简化版本(从您的 SQLFiddle 示例中删除了许多其他列)

CREATE TABLE IF NOT EXISTS `News` (
 id int(11) NOT NULL AUTO_INCREMENT,
 UserID integer NOT NULL,
 ... other fields
 `DateCreate` datetime NOT NULL,
 PRIMARY KEY ( id ),
 KEY ( UserID )  
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

用户 ID 上的额外键，以防您想要由特定用户创建的所有新闻事件。

CREATE TABLE IF NOT EXISTS `Users` (
  id int(11) NOT NULL AUTO_INCREMENT,
  other fields...
  PRIMARY KEY ( id ),
  KEY ( LastName, Name )
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

如果你想通过用户名进行搜索的附加键

CREATE TABLE IF NOT EXISTS `Likes` (
 id int(11) NOT NULL AUTO_INCREMENT,
 UserId integer NOT NULL,
 NewsID integer NOT NULL,
 `Type` enum('up','down') NOT NULL,
 `IsFavorite` enum('yes','no') NOT NULL,
 `DateCreate` datetime NOT NULL,
 PRIMARY KEY (`id`),
 KEY ( UserID ),
 KEY ( NewsID, IsFavorite )
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

此处用于连接和/或聚合的附加键。我还添加了一个标志列作为最爱。这可以避免需要收藏夹表，因为它们包含 LIKES 的相同基本内容。因此，有人可能只是喜欢/不喜欢给定的新闻项目，但也喜欢/不喜欢它作为最终用户希望能够快速引用的收藏。

那么，这些表结构是如何简化查询的呢？每个表都有自己的“id”列，但任何其他表都使用 tableNameID(UserID、NewsID、LikesID 或其他)，这就是连接。

select ...
   from 
      News N
         Join Users U
            on N.UserID = U.ID
         Join Likes L
            on N.ID = L.NewsID

在编写查询时，整数列更容易被其他人识别...这样更有意义吗？

关于mysql - 如何在某些表上使用左连接编写正确的 sql？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/21232890/