Pertanyaan bersarang ke medan rujukan dalam pertanyaan induk

Question

Contoh ringkas:

select
    ticket_id,
    `number` as 'ticket number',
    (SELECT count(*) from ost_thread_entry join ost_thread on ost_thread_entry.thread_id = ost_thread.id where ost_thread.object_id = 1234) as 'number of posts in ticket'
from
    ost_ticket

Saya perlu memetik nilai dalam ticket_id bukan 1234

Answer 1

Anda boleh menggunakan jadual alias di sini:

SELECT
    ticket_id,
    number AS `ticket number`,
    (SELECT COUNT(*)
     FROM ost_thread_entry ote
     INNER JOIN ost_thread ot ON ote.thread_id = ot.id
     WHERE ot.object_id = t.ticket_id) AS `number of posts in ticket`
FROM ost_ticket t;

Ambil perhatian bahawa anda juga boleh menulis pertanyaan tanpa menggunakan subkueri yang berkaitan dan sebaliknya menggunakan gabungan:

SELECT
    t.ticket_id,
    t.number AS `ticket number`,
    COUNT(ote.thread_id) AS `number of posts in ticket`
FROM ost_ticket t
LEFT JOIN ost_thread ot ON ot.object_id = t.ticket_id
LEFT JOIN ost_thread_entry ote ON ote.thread_id = ot.id
GROUP BY
    t.ticket_id,
    t.number;