具有左外连接和平均值的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

我需要一个包含名称、别名、号码和王子的 View 。但是我需要名称中的所有行，我打算使用左外连接。但问题是，当我查询 othercarlos 时，我需要价格是 carlos 服务的平均值，而当名称是 other 时，我需要出现所有服务的平均值。但是显示为null

http://sqlfiddle.com/#!2/c1d4f/1

我创建了这个表和我的查询

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'

Here is a fiddle有了这个。

结果:

╔════════╦═════════════╦══════════════════╦════════╦═══════╗
║  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 ║
╚════════╩═════════════╩══════════════════╩════════╩═══════╝