mysql - 如何组合查询？

Question

美好的一天。

Structure tables in SqlFidle.

我有一些疑问:

查询 1.

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 

查询 2.

Select count(*) as Uplikes FROM Likes WHERE Type = 'up' AND NewsIdn='730456' 
// value NewsIdn for example

查询 3.

Select count(*) as DownLikes FROM Likes WHERE Type = 'down' AND NewsIdn='730456' 
// value NewsIdn for example

我将从查询 1 中的每个行表 News 中获取 Uplikes 和 DownLikes 的查询合并(每个行表 News 具有唯一值 NewsIdn)。

请告诉我怎么做到的？

P.S.:结果我会得到下一个 - http://clip2net.com/clip/m264191/1389339754-clip-63kb.jpg

Answer 1

试试这个:

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, 
    SUM(l.Type = 'up') Uplikes, 
    SUM(l.Type = 'down') Downlikes
FROM News n 
INNER JOIN Users u ON n.UserIdn = u.UserIdn 
LEFT JOIN Likes l ON n.NewsIdn = l.NewsIdn
GROUP BY n.id

检查这个SQL FIDDLE DEMO

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