I have a customer table with 2 columns, customerID and customerType, in which there are totally 1000 records. there are five values for customerType: 'A', 'B', 'C', 'D', 'E'. Each record has a customerType among these.
My question is how to randomly select 100 records, in which each customerType is evenly distributed,i.e. 20 rows for 'A', 20 rows for 'B'...
My current method is
select customerID from customer where customerType = 'A' order by random() limit 20 union
select customerID from customer where customerType = 'B' order by random() limit 20 union
select customerID from customer where customerType = 'C' order by random() limit 20 union
select customerID from customer where customerType = 'D' order by random() limit 20 union
select customerID from customer where customerType = 'E' order by random() limit 20 union
union
works,but is very verbose.Could you please show me if there is a more simple solution?
Besides, I also tried nested sql,
select customerID from customer order by random() limit 20 where customerType in
(select customerType from customer group by customerType)
but an syntax error occurs as where
should not be after order by
I really need help from stackoverflow community. Could anyone do me a favor?
Aucun commentaire:
Enregistrer un commentaire