使用左外连接和平均值的MySQL查询

Question

我在mysql中有两个表。第一个表的名称包含以下日期：

    name           service         number
    carlos          telephone         6
    juan             watter          12
    maria             gas            23
    jhon             hostal          17
    marcos           sleeping        21
    carlos          othercarlos      12
    other             other          13

我还有其他的表别名。

    name             service         alias              price
   carlos          telephone        telephone-carlos     700
   carlos          sleeping         sleeping-carlos      300  
    juan             watter          watter-juan         900
    maria             gas            gas-maria           650
    jhon             hostal          hostal-jhon         700

我需要一个视图，包含名称、别名、号码和价格。 但是我需要在名称上进行左外连接以获取所有行。 问题在于，当查询为“othercarlos”时，我需要价格为卡洛斯服务的平均价格；当查询为“other”时，我需要出现所有服务的平均价格。但是它会显示null。 http://sqlfiddle.com/#!2/c1d4f/1 我创建了这些表和我的查询。

- camilo soto

---

做得很好，包括了 Fiddle！但我还是不太明白问题。你能把输出也包含进来吗？ - Ed Gibbs

你能发布所需的结果吗？ - Lamak

Answer 1

好的，我相信有更好的方法来做这件事情，但是我至少可以给你提供一种方式：

SELECT  t1.name, 
        t1.service, 
        t2.alias, 
        t1.number, 
        COALESCE(t2.price,t3.price,t4.price) AS price
FROM name t1
LEFT JOIN alias t2
    ON t1.name= t2.name 
    AND t1.service = t2.service
LEFT JOIN ( SELECT name, AVG(price) AS price
            FROM alias
            GROUP BY name) t3
    ON t1.name = t3.name
LEFT JOIN ( SELECT AVG(price) AS price
            FROM alias) t4
    ON t1.name = 'other'

这里有一个SQLFiddle演示链接。

结果如下：

╔════════╦═════════════╦══════════════════╦════════╦═══════╗
║  NAME  ║   SERVICE   ║      ALIAS       ║ NUMBER ║ PRICE ║
╠════════╬═════════════╬══════════════════╬════════╬═══════╣
║ carlos ║ telephone   ║ telephone-carlos ║      6 ║   700 ║
║ juan   ║ watter      ║ watter-juan      ║     12 ║   900 ║
║ maria  ║ gas         ║ gas-maria        ║     15 ║   250 ║
║ jhon   ║ hostal      ║ hostal-jhon      ║     21 ║   640 ║
║ carlos ║ sleeping    ║ sleeping-carlos  ║     24 ║   300 ║
║ carlos ║ othercarlos ║ (null)           ║     11 ║   500 ║
║ other  ║ (null)      ║ (null)           ║      2 ║   558 ║
╚════════╩═════════════╩══════════════════╩════════╩═══════╝

- Lamak