Use of any product of the supplier in Mysql Query

The following articles mainly describe the solutions used by all the suppliers in Mysql query and any of their own products. This article mainly describes the specific use solutions of Group, according to a user's demand, the main cause is as follows:

There is a supplier Table a, product table B (hereinafter referred to as a, B, a and B) is a one-to-many relationship (one supplier corresponds to multiple items), and now I want to extract one-to-one, that is to say, all the suppliers are extracted, but each supplier can extract only one item record to match.

The test code I provided is as follows:

 
 

  
  
View plaincopy to clipboardprint?
  
  
Create table 't_ supplier '(
  
  
'Id' int (11) not null,
  
  
'Name' varchar (50) not null,
  
  
Primary key ('id ')
  
  
) ENGINE = InnoDB default charset = utf8;
  
  
Create table 't_ goods '(
  
  
'Id' int (11) not null AUTO_INCREMENT,
  
  
'Supplier _ id' int (11) not null,
  
  
'Name' varchar (50) default null,
  
  
Primary key ('id '),
  
  
KEY 'Supplier _ id' ('Supplier _ id '),
  
  
CONSTRAINT 't_ goods_ibfk_1 'foreign key ('Supplier _ id ')
REFERENCES't _ supplier '('id ')
  
  
) ENGINE = InnoDB AUTO_INCREMENT = 5 default charset = utf8;
  
  
Insert into t_Supplier values (1, 'tianjin supplier '), (2, 'Beijing supplier ');
  
  
Insert into t_Goods values (1, 1, 'tianjin product 1 '),
(2, 1, 'tianjin product 2'), (3, 2, 'Beijing product 1'), (4, 2, 'Beijing product 2 ');
 
 

Mysql query statement. Pay attention to the group usage.

 
 

  
  
select * from t_supplier s   
  
  
left join t_goods g on g.supplier_id=s.id   
  
  
group by s.id  
 
 

All the suppliers of Mysql query results and one of their products

 
 

  
  
Create table 't_ supplier '(
  
  
'Id' int (11) not null,
  
  
'Name' varchar (50) not null,
  
  
Primary key ('id ')
  
  
) ENGINE = InnoDB default charset = utf8;
  
  
Create table 't_ goods '(
  
  
'Id' int (11) not null AUTO_INCREMENT,
  
  
'Supplier _ id' int (11) not null,
  
  
'Name' varchar (50) default null,
  
  
Primary key ('id '),
  
  
KEY 'Supplier _ id' ('Supplier _ id '),
  
  
CONSTRAINT 't_ goods_ibfk_1 'foreign key ('Supplier _ id') REFERENCES 't_ Supplier' ('id ')
  
  
) ENGINE = InnoDB AUTO_INCREMENT = 5 default charset = utf8;
  
  
Insert into t_Supplier values (1, 'tianjin supplier '), (2, 'Beijing supplier ');
  
  
Insert into t_Goods values (, 'tianjin product 1'), (, 'tianjin product 2'), (, 'Beijing product 1, 'Beijing product 2 ');
 
 

Query statement. Pay attention to the group usage.

 
 

  
  
select * from t_supplier s  
  
  
left join t_goods g on g.supplier_id=s.id  
  
  
group by s.id 
 
 

Mysql query results, all vendors, and one of their products in Mysql, non-Group and aggregate fields can appear in select, and the system will automatically select a data. This is not allowed in other databases.