将子查询
问题描述:
我有以下查询:将子查询
SELECT l.id,
(SELECT amount FROM lead_status WHERE lead_id = l.id AND buyer = 'BUYER_PROXY') AS our_bid,
(SELECT amount FROM lead_status WHERE lead_id = l.id AND buyer != 'BUYER_PROXY' AND discriminator='AUTO_PING' ORDER BY amount DESC LIMIT 1) AS best_bid,
(SELECT amount FROM lead_status WHERE lead_id = l.id AND discriminator = 'AUTO_POST' AND winner = 1 ORDER BY amount DESC LIMIT 1) AS final_sold_amount,
(SELECT buyer FROM lead_status WHERE lead_id = l.id AND discriminator = 'AUTO_POST' AND winner = 1 ORDER BY amount DESC LIMIT 1) AS buyer,
(SELECT COUNT(*) FROM vehicles WHERE lead_id = l.id) AS number_of_vehicles FROM leads AS l,
co.first_name, co.last_name, co.email, co.state, co.credit_type, ca.make, ca.model, co.owns_home,
co.months_in_residence, a.injury, a.at_fault, d.gender_type, d.license_valid, d.license_obtained_age,
d.education_level, d.license_revoked_in_last_five_years, TIME(l.create_date), rc.coverage_type
FROM leads AS l
LEFT JOIN contacts AS co ON l.id = co.lead_id
LEFT JOIN vehicles AS v ON v.lead_id = l.id
LEFT JOIN cars AS ca ON ca.id = v.car_id
LEFT JOIN drivers AS d ON d.lead_id = l.id
LEFT JOIN accidents AS a ON d.id = a.driver_id
LEFT JOIN requested_coverage AS rc ON rc.lead_id = l.id
WHERE l.state = 'ACCEPTED'
AND (SELECT buyer FROM lead_status WHERE lead_id = l.id AND discriminator = 'AUTO_POST' AND winner = 1 ORDER BY amount DESC LIMIT 1) != 'TEST'
AND DATE(l.create_date) > '2011-12-01'
我试图添加其他子查询,但每次我把它添加到子查询,我得到一个错误的结束,当我运行命令。
这是我想要添加的。
不断收到错误消息当我运行:
SELECT l.id,
(SELECT amount FROM lead_status WHERE lead_id = l.id AND buyer = 'BUYER_PROXY') AS our_bid,
(SELECT amount FROM lead_status WHERE lead_id = l.id AND buyer != 'BUYER_PROXY' AND discriminator='AUTO_PING' ORDER BY amount DESC LIMIT 1) AS best_bid,
(SELECT amount FROM lead_status WHERE lead_id = l.id AND discriminator = 'AUTO_POST' AND winner = 1 ORDER BY amount DESC LIMIT 1) AS final_sold_amount,
(SELECT buyer FROM lead_status WHERE lead_id = l.id AND discriminator = 'AUTO_POST' AND winner = 1 ORDER BY amount DESC LIMIT 1) AS buyer,
(SELECT COUNT(*) FROM vehicles WHERE lead_id = l.id) AS number_of_vehicles FROM leads AS l,
co.first_name, co.last_name, co.email, co.state, co.credit_type, ca.make, ca.model, co.owns_home,
co.months_in_residence, a.injury, a.at_fault, d.gender_type, d.license_valid, d.license_obtained_age,
d.education_level, d.license_revoked_in_last_five_years, TIME(l.create_date), rc.coverage_type
FROM leads AS l
LEFT JOIN contacts AS co ON l.id = co.lead_id
LEFT JOIN vehicles AS v ON v.lead_id = l.id
LEFT JOIN cars AS ca ON ca.id = v.car_id
LEFT JOIN drivers AS d ON d.lead_id = l.id
LEFT JOIN accidents AS a ON d.id = a.driver_id
LEFT JOIN requested_coverage AS rc ON rc.lead_id = l.id
WHERE l.state = 'ACCEPTED'
AND (SELECT buyer FROM lead_status WHERE lead_id = l.id AND discriminator = 'AUTO_POST' AND winner = 1 ORDER BY amount DESC LIMIT 1) != 'TEST'
AND DATE(l.create_date) > '2011-12-01'
答
你有两个FROM leads AS l
(SELECT COUNT(*) FROM vehicles WHERE lead_id = l.id) AS number_of_vehicles FROM leads AS l,
/* ------------------------------------------------------------------------------^^^^^^^^*/
co.first_name, co.last_name, co.email, co.state, co.credit_type, ca.make, ca.model, co.owns_home,
co.months_in_residence, a.injury, a.at_fault, d.gender_type, d.license_valid, d.license_obtained_age,
d.education_level, d.license_revoked_in_last_five_years, TIME(l.create_date), rc.coverage_type
FROM leads AS l
/*---^^^^^^^^^^^^*/
第一个不应该存在。
...
(SELECT COUNT(*) FROM vehicles WHERE lead_id = l.id) AS number_of_vehicles,
co.first_name, co.last_name, co.email, co.state, co.credit_type, ca.make, ca.model, co.owns_home,
co.months_in_residence, a.injury, a.at_fault, d.gender_type, d.license_valid, d.license_obtained_age,
d.education_level, d.license_revoked_in_last_five_years, TIME(l.create_date), rc.coverage_type
FROM leads AS l
...
什么是错误信息? – 2011-12-15 18:01:50