SQL-我应该使用联接吗?
问题描述：
我有以下示例查询(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