select model_name, vendor_name from model a where exists (select 1 from model where model_name=a.model_name and vendor_name!=a.vendor_name)
或者 select distinct a.* from model a,model b where a.model_name=b.model_name and a.vendor_name!=b.vendor_name另外还可以用 where model_name in (select model_name from model group by model_name having count(*)>1) 等等。
如果model_name和vendor_name在不同的table, 要用 FROM model LEFT JOIN asset_details ON model.model_id = asset_details.model_id LEFT JOIN vendor ON asset_details.vendor_id = vendor.vendor_id 才能连到model_name和vendor_name,这样应该怎么写呢
我刚刚用 SELECT model_name, vendor_name FROM model LEFT JOIN asset_details ON model.model_id = asset_details.model_id LEFT JOIN vendor ON asset_details.vendor_id = vendor.vendor_id where model_name in (select model_name from model LEFT JOIN asset_details ON model.model_id = asset_details.model_id LEFT JOIN vendor ON asset_details.vendor_id = vendor.vendor_id group by model_name having count(DISTINCT vendor_name)>1 )MySQL直接run到死机 >_<!
SELECT model_name, vendor_name FROM model LEFT JOIN asset_details ON model.model_id = asset_details.model_id LEFT JOIN vendor ON asset_details.vendor_id = vendor.vendor_id inner join (aelect model_name from model LEFT JOIN asset_details ON model.model_id = asset_details.model_id LEFT JOIN vendor ON asset_details.vendor_id = vendor.vendor_id group by model_name having count(DISTINCT vendor_name)>1) zz on model.model_name=zz.model_name
SELECT DISTINCT vendor_name, model.model_name, model.model_id FROM model LEFT JOIN asset_details ON model.model_id = asset_details.model_id LEFT JOIN vendor ON asset_details.vendor_id = vendor.vendor_idINNER JOIN ( SELECT model_name FROM model LEFT JOIN asset_details ON model.model_id = asset_details.model_id LEFT JOIN vendor ON asset_details.vendor_id = vendor.vendor_id GROUP BY model_name HAVING count( DISTINCT vendor_name ) >1 )zz ON model.model_name = zz.model_nameProblem solved! Thanks everyone. Close the thread and c u~
from model a
where exists (select 1 from model where model_name=a.model_name and vendor_name!=a.vendor_name)
select distinct a.*
from model a,model b
where a.model_name=b.model_name and a.vendor_name!=b.vendor_name另外还可以用 where model_name in (select model_name from model group by model_name having count(*)>1) 等等。
FROM model
LEFT JOIN asset_details ON model.model_id = asset_details.model_id
LEFT JOIN vendor ON asset_details.vendor_id = vendor.vendor_id
才能连到model_name和vendor_name,这样应该怎么写呢
SELECT model_name, vendor_name
FROM model
LEFT JOIN asset_details ON model.model_id = asset_details.model_id
LEFT JOIN vendor ON asset_details.vendor_id = vendor.vendor_id
where model_name in
(select model_name
from model
LEFT JOIN asset_details ON model.model_id = asset_details.model_id
LEFT JOIN vendor ON asset_details.vendor_id = vendor.vendor_id
group by model_name
having count(DISTINCT vendor_name)>1
)MySQL直接run到死机 >_<!
FROM model
LEFT JOIN asset_details ON model.model_id = asset_details.model_id
LEFT JOIN vendor ON asset_details.vendor_id = vendor.vendor_id
inner join
(aelect model_name
from model
LEFT JOIN asset_details ON model.model_id = asset_details.model_id
LEFT JOIN vendor ON asset_details.vendor_id = vendor.vendor_id
group by model_name
having count(DISTINCT vendor_name)>1) zz
on model.model_name=zz.model_name
FROM model
LEFT JOIN asset_details ON model.model_id = asset_details.model_id
LEFT JOIN vendor ON asset_details.vendor_id = vendor.vendor_idINNER JOIN (
SELECT model_name
FROM model
LEFT JOIN asset_details ON model.model_id = asset_details.model_id
LEFT JOIN vendor ON asset_details.vendor_id = vendor.vendor_id
GROUP BY model_name
HAVING count( DISTINCT vendor_name ) >1
)zz
ON model.model_name = zz.model_nameProblem solved! Thanks everyone. Close the thread and c u~