SQL-我应该使用联接吗? [英] SQL - Should I use a join?

问题描述

我有以下示例查询(MySQL):

I have the following sample query (MySQL):

SELECT * FROM `action` 
WHERE `customer_id` IN 
    (SELECT `id` FROM `customer` WHERE `status`=1)
ORDER BY
    action.date ASC
LIMIT 0, 10

我需要能够按customer.status字段进行订购.我可以通过加入来完成此操作吗?

I need to be able to ORDER BY the customer.status field. Do I accomplish this with a join?

status是customer表上的字段.

编辑查询:

SELECT * FROM `action` 
ORDER BY
    action.date ASC
LIMIT 0, 10

---

重要！

---

IMPORTANT!

我正在通过PHP解析返回数据.运行修改后的查询后:

I am parsing the return data via PHP. After running the revised query:

SELECT * FROM `action` a INNER JOIN `customer` c ON a.customer_id = c.id ORDER BY a.form_id ASC LIMIT 0, 10

我的PHP代码坏了...

My PHP code breaks...

这篇文章帮助了我.

我修改后的查询如下:

SELECT 
    *, a.id AS lead_id, c.id AS customer_id 
FROM 
    `action` a 
INNER JOIN 
    `customer` c ON a.customer_id = c.id 
ORDER BY c.status DESC

谢谢大家！

更新

因为我有一些客户记录而没有操作记录，所以INNER JOIN并未返回所有相关记录.我现在使用JOIN，所有结果都会按预期返回.

Because I have some customer records without an action record, an INNER JOIN was not returning all relevant records. I use a JOIN now, and all results come back as expected.

推荐答案

SELECT * 
FROM `action` a
INNER JOIN `customer` c on a.`customer_id` = c.`id`
WHERE c.`status` in (1, 4, 7, 8)
ORDER BY a.date, c.status
LIMIT 0, 10 

这篇关于SQL-我应该使用联接吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！