MySQL查询:使用UNION并将行号作为SELECT的一部分 [英] MySQL query: Using UNION and getting row number as part of SELECT
本文介绍了MySQL查询:使用UNION并将行号作为SELECT的一部分的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个联合查询,如下所示:
I have a union query as follows:
(SELECT t.id, t.name, c.company AS owner, t.creation_date AS date, t.notes
FROM tool t, client c
WHERE t.id_customer = '15' AND t.trash_flag = '1')
UNION
(SELECT f.id, f.name, CONCAT(m.first_name, ' ', m.last_name) AS owner, f.date, f.notes
FROM file f, meta m
WHERE ((f.acl = 0) OR (f.acl = 1 AND '1' = TRUE) OR (f.acl = 2 AND f.id = '7')) AND f.id = '15' AND f.trash_flag = '1' AND m.user_id = f.id_user)
ORDER BY 'name' 'ASC' LIMIT 0,20
Everything works fine but I have two questions:
- How do I add a column to the entire result set that gives the row number
- Could I do this without using UNION e.g. an advanced join?
- How do I add a column to the entire result set that gives the row number
- Could I do this without using UNION e.g. an advanced join?
Thanks for your time MySQL gurus!
Thanks for your time MySQL gurus!
推荐答案
I can't test it righ now but from what I found, following might work:
I can't test it righ now but from what I found, following might work:
Reference: Row Number Variable
SQL Statement
SELECT @rownum := @rownum + 1 rownum
, t.*
FROM (
(SELECT t.id
, t.name
, c.company AS owner
, t.creation_date AS date
, t.notes
FROM tool t
, client c
WHERE t.id_customer = '15'
AND t.trash_flag = '1'
) UNION (
SELECT f.id
, f.name
, CONCAT(m.first_name, ' ', m.last_name) AS owner
, f.date
, f.notes
FROM file f
, meta m
WHERE ((f.acl = 0) OR (f.acl = 1 AND '1' = TRUE) OR (f.acl = 2 AND f.id = '7')) AND f.id = '15' AND f.trash_flag = '1' AND m.user_id = f.id_user)
)
) t
, (SELECT @rownum := 0) r
ORDER BY
'name' ASC
LIMIT 0, 20
这篇关于MySQL查询:使用UNION并将行号作为SELECT的一部分的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文