复杂的搜索SQL查询
问题描述:
我需要你的帮助与SQL查询。复杂的搜索SQL查询
我需要在该mathing元素具有smalest顺序表中选择值,目前我有以下查询:
SELECT p.confirmation,
p.lname,
p.fname,
p.profiletype,
a.address1,
a.city,
c.contactinfo
FROM profile p
LEFT JOIN contact c
ON p.confirmation = c.profileid
AND c.ord = 1
LEFT JOIN address a
ON p.confirmation = a.profileid
AND a.ord =1
这项工作做得很好,当smalest“a.ord”或c.ord “是1,但最小值不会永远是一个,也可能是其他任何数字,所以我试着不更迭如下:
SELECT p.confirmation,
p.lname,
p.fname,
p.profiletype,
a.address1,
a.city,
c.contactinfo
FROM profile p
LEFT JOIN contact c
ON p.confirmation = c.profileid
min(c.ord)
LEFT JOIN address a
ON p.confirmation = a.profileid
min(a.ord)
我已经取代” AND a.ord = 1 “by”min(a.ord)“没有成功。
我怎么能做到这一点,没有做出两个单独的查询。
有没有一种方法,我可以在排序的
SELECT c.ord FROM contact c WHERE p.confirmation = c.profileid ORDER BY c.ord LIMIT 1
当前SQL里面添加的东西?
谢谢!
答
AND a.ord =1 // if a.cord is always 1
AND a.ord <=1 // if a.cord is always <1 (eg. .9 or .75)
// or just leave it.. i dont know your script, but is it needed?
// same at c.cord!
// but rather use where! see below
而且作为一个例子:
SELECT
p.confirmation,
p.lname,
p.fname,
p.profiletype,
a.address1,
a.city,
c.contactinfo
FROM
profile AS p
LEFT JOIN
contact AS c
ON
p.confirmation=c.profileid
LEFT JOIN
address AS a
ON
p.confirmation=a.profileid
WHERE
c.ord=1 AND a.ord=1
ORDER BY
c.ord ASC, a.ord ASC
希望我得到了你的问题所在
答
您可以通过计算分ORD为每个表,然后使用,在加入做:
SELECT p.confirmation, p.lname, p.fname, p.profiletype,
a.address1, a.city, c.contactinfo
FROM profile p LEFT JOIN
(select c.profileid, MIN(c.ord) as minord
from contact c
group by c.profileid
) minco
on minco.profileid = c.profileid left join
contact c
ON p.confirmation = c.profileid and minco.minord = c.ord LEFT JOIN
(select a.profileid, min(a.ord) as minord
from address a
group by a.profileid
) minao
on minao.profileid = c.profileid left join
address a
ON p.confirmation = a.profileid and minao.minord = a.ord
答
只需在MySQL中使用LIMIT 1即可在订购后获得第一行:
SELECT p.confirmation,
p.lname,
p.fname,
p.profiletype,
a.address1,
a.city,
c.contactinfo
FROM profile p
LEFT JOIN contact c
ON p.confirmation = c.profileid
LEFT JOIN address a
ON p.confirmation = a.profileid
ORDER BY c.ord, a.ord
LIMIT 1
答
这样的事情可能就是你要找的东西。您可以根据所需的优先级将ORDER BY c.ord ASC, a.ord ASC
更改为ORDER BY a.ord ASC, c.ord ASC
。
SELECT p.confirmation,
p.lname,
p.fname,
p.profiletype,
a.address1,
a.city,
c.contactinfo
FROM profile p
LEFT JOIN contact c
ON p.confirmation = c.profileid
LEFT JOIN address a
ON p.confirmation = a.profileid
ORDER BY c.ord ASC, a.ord ASC
LIMIT 1
答
更新:
我finalyl解决我的问题与嵌套SELECT:
SELECT p.confirmation,
p.lname,
p.fname,
p.profiletype,
a.address1,
a.city,
c.contactinfo
FROM profile p
LEFT JOIN contact c
ON p.confirmation = c.profileid
AND c.ord=(SELECT ord FROM contact WHERE profileid= p.confirmation ORDER BY ord LIMIT 1)
LEFT JOIN address a
ON p.confirmation = a.profileid
AND a.ord=(SELECT ord FROM address WHERE profileid= p.confirmation ORDER BY ord LIMIT 1)
谢谢#Gordon Linoff您的洞察力上使用一个查询的多个选择!
close ...我有多个联系人为每个配置文件,并按“ord”排序。我需要每个配置文件的第一个联系人显示在我的搜索中。类似于:AND c.ord =(SELECT ord FROM contact where WHERE profileid = p.confirmation ORDER BY ord LIMIT 1)。但我现在知道,这是不正确的SQL! – 2013-03-26 19:25:47
@DanielBerthiaume。 。 。这就是这个查询正在做的事情。它只是将ord的最小值计算为单独的子查询。 – 2013-03-26 19:27:48
谢谢你,答案并没有解决我的问题,但它确实帮助我找到了我自己的答案,并且对嵌套select有所了解。 – 2013-03-26 19:47:34