我有查询:
SELECT TOP 20 f.id_service AS f_id_service,
f.id_city AS f_id_city,
f.name AS f_name,
f.address AS f_address,
f.business AS f_business,
f.web AS f_web,
f.phone AS f_phone,
f.id_firm AS f_id_firm
FROM Firm f
LEFT JOIN Price p
ON p.id_service = f.id_service
AND p.id_city = f.id_city
AND p.id_firm = f.id_firm
WHERE f.name NOT IN (SELECT DISTINCT TOP 20 f.name
FROM Firm f
WHERE f.blocked = '0'
AND ( f.name LIKE 'АВТО%'
OR f.phone LIKE 'АВТО%' )
AND ( f.phone != ''
OR f.address != '' )
AND f.id_city = '73041'
ORDER BY f.name ASC)
AND f.dogovor = '1'
AND f.blocked = '0'
AND ( f.name LIKE 'АВТО%'
OR f.phone LIKE 'АВТО%' )
AND ( f.phone != ''
OR f.address != '' )
AND f.id_city = '73041
请告诉我如何使这个查询只选择唯一的f.name
将SELECT改为this
SELECT DISTINCT TOP 20
f.name as f_name
FROM ...
你不能有所有列的值,但只区分其中一个:这是没有意义的。
试试这个
SELECT DISTINCT
TOP 20
f.id_service as f_id_service,
f.id_city as f_id_city,
f.name as f_name,
f.address as f_address,
f.business as f_business,
f.web as f_web,
使用DISTINCT
关键词:
SELECT DISTINCT
TOP 20
f.id_service as f_id_service,
f.id_city as f_id_city,
f.name as f_name,
f.address as f_address,
f.business as f_business,
f.web as f_web,
f.phone as f_phone,
f.id_firm as f_id_firm FROM Firm f
left join Price p on p.id_service=f.id_service
AND p.id_city=f.id_city AND p.id_firm=f.id_firm
WHERE
f.name NOT IN (
SELECT DISTINCT TOP 20 f.name FROM Firm f
WHERE f.blocked='0'
AND (f.name LIKE 'АВТО%' OR f.phone LIKE 'АВТО%')
AND (f.phone !='' OR f.address != '') AND f.id_city='73041'
ORDER BY f.name ASC ) AND f.dogovor='1'
AND f.blocked='0' AND (f.name LIKE 'АВТО%' OR f.phone LIKE 'АВТО%')
AND (f.phone !='' OR f.address != '')
AND f.id_city='73041'
因为所有的列都来自同一个表,除非name
在firm
表中是唯一的,否则你不能像这样使用DISTINCT。您需要决定在重复的情况下显示哪个公司,然后编写一个复杂的查询来实现这一点。