sql草稿

参考：MySQL 内连接、外连接、左连接、右连接、全连接

SELECT count(*) FROM `t_product_base`

select m_name from t_medicinal_info where id in (select t_medicinal_id from t_medicinal_productbase where t_product_base_id = '2af213b02cef481b8432fd5b0d753896')


select * from t_product_base t_medicinal_productbase t_medicinal_info where t_medicinal_productbase='2af213b02cef481b8432fd5b0d753896' and t_medicinal_info.id=t_medicinal_productbase.t_medicinal_id

--查询有标明药材的公司
Set @i:=0;
select (@i:=@i+1) i,b.id,GROUP_CONCAT(a.m_name SEPARATOR' ') as medicinalNames,b.b_name,b.b_Position,b.b_area,b.b_water_environment,b.b_soil_environment,b.b_air_environment,b.b_totality_appraise,b.b_remarks from t_medicinal_info a, t_product_base b, t_medicinal_productbase c where (a.id=c.t_medicinal_id and b.id=c.t_product_base_id) group by b.b_create_time    


select count(*) from t_product_base a,t_medicinal_productbase b where a.id=b.t_product_base_id

--查询无表明药材的公司，使用带exists的嵌套查询
Set @i:=0;
select (@i:=@i+1) j,a.* from t_product_base a where not exists(select * from t_medicinal_productbase b where a.id=b.t_product_base_id) 

Set @j:=0;
select (@j:=@j+1) j from t_medicinal_info a, t_product_base b, t_medicinal_productbase c where (a.id=c.t_medicinal_id and b.id=c.t_product_base_id) 

select count(*) from t_medicinal_info a, t_product_base b, t_medicinal_productbase c where (a.id=c.t_medicinal_id and b.id=c.t_product_base_id) 

---

<!--左外连接-->
Set @j:=0;
select (@j:=@j+1) j,pb.id, pb.b_name, pb.b_area, pb.b_company_id, pb.b_position, pb.b_latitude, pb.b_longitude, pb.b_remarks,pb.b_water_environment,pb.b_soil_environment,pb.b_air_environment, b_environment_dataimg_path,pb.b_totality_appraise, pb.b_create_time,gc.c_name as bCompanyName, GROUP_CONCAT(mi.m_name SEPARATOR '  ') AS medicinalNames, gc.c_position AS bCompanyPosition, gc.c_person AS bCompanyPerson, gc.c_contact AS bCompanyContact, gc.c_medicinal AS bCompanyMedicinal, gc.c_remarks AS bCompanyRemarks
from t_product_base pb 
LEFT JOIN t_grow_company gc ON gc.id=pb.b_company_id
left join t_medicinal_productbase mp on pb.id = mp.t_product_base_id
left join t_medicinal_info mi on mp.t_medicinal_id=mi.id group by pb.b_name