DWQA QuestionsCategory: DatabaseHow to implement such a query (two fields determine a unique entry)
Small damage SC asked 3 weeks ago

The query criteria are two groups of one-to-one data. The first group is productid, and the second group is type
such as
Productid is type
Pants
Pants
Clothing
Decoration
They have a one-on-one relationship
Then we have to sort their prices
Product ID has repetition, but if product ID and type judge at the same time, there will be no repetition
For example, type = 1, productid = 1 can determine a unique entry
If you query a single data, select * fromorderWhere productid = 1 and type =’pants class’; that’s all
In the case that productid can determine one entry, if you want to query multiple entries, use select * fromorderWhere productid in (1,3,5) is OK
However, only product ID and type can determine an entry. How can we find it at one time
It seems a little complicated. I can’t figure out how to realize it
It’s like this!图片描述
It is equivalent to merging these queries and sorting them by price in the whole result set
Select * from ‘order’ where productid in (1,3) and type =’pants class’
Select * from ‘order’ where productid = 1 and type =’pants class’
Select * from ‘order’ where productid = 3 and type =’pants class’
Select * from ‘order’ where productid = 2 and type =’clothing ‘
Select * from ‘order’ where productid = 2 and type =’decoration class’
图片描述++++++++Plus
图片描述++++++++Plus
图片描述
The table is as follows
CREATE TABLE `order` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`productid` int(10) DEFAULT NULL,
`type` varchar(255) DEFAULT NULL,
`price` int(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;
Insert into ‘order’ values (1,1, ‘clothing’, 515);
Insert into ‘order’ values (2,2, ‘clothing’, 5154);
Insert into ‘order’ values (3,3, ‘clothing’, 4455);
Insert into ‘order’ values (4,4, ‘clothing’, 55023);
Insert into ‘order’ values (5,5, ‘clothing’, 456);
Insert into ‘order’ values (6,1, ‘trousers’, 1651);
Insert into ‘order’ values (7,2, ‘trousers’, 164);
Insert into ‘order’ values (8,3, ‘pants’, 111);
Insert into ‘order’ values (9,4, ‘trousers’, 1411);
Insert into ‘order’ values (10,5, ‘trousers’, 1511);
Insert into ‘order’ values (11,1, ‘decoration’, 1054);
Insert into ‘order’ values (12,2, ‘decoration’, 156);
Insert into ‘order’ values (13,3, ‘decoration’, 167324);
Insert into ‘order’ values (14,4, ‘decoration’, 20);
Insert into ‘order’ values (15,5, ‘decoration’, 165);

clcx_1315 replied 3 weeks ago

I don’t know what you’re going to do

clcx_1315 replied 3 weeks ago

Find out how to do all (PID is 1 pants class, PID is 3 pants class, PID is 2 clothes class, PID is 2 decoration class) and create a view of how to do it

2 Answers
clcx_1315 answered 3 weeks ago

Unclear needs, confused statements
I don’t know if you want to sort all the prices together or sort them internally. Let’s see for ourselves

Select * from 'order' where (productid, type) in ((1, 'trousers'), (3,' trousers'), (2, 'clothes'), (2,' decoration ')) order by type, price;
Small damage SC replied 3 weeks ago

In ((1,’pants’), (3,’pants’), (2,’clothing ‘), (2,’decoration’));Ah, this is what I wantThe original conditions can be like this. That’s it. That’s what I think. Thank you

konohanaruto answered 3 weeks ago

You mean, I think it means grouping by productid and type at the same time?

SELECT productid, type FROM order WHERE group by productid, type;
--Non SQL_ In strict mode, you can use it directly, because there is only one data in each group, so such data is correct and reliable
SELECT * FROM order WHERE group by productid, type;

I don’t know if it’s what you mean. If you understand it wrong, please point out..

Small damage SC replied 3 weeks ago

You didn’t get to the point of the problem, but thank you