[Application Instance] displays data based on the display rate-[Leaf]

-- Data
/*
Occurrence Rate of serial number name category
-----------------------------------------------------------------
1 aa 1 0.5
2 BB 1 0.5
3 cc 2 0.3
4 DD 2 0.6
5 ff 2 0.1
*/
-- Requirements:
-- For example, the data in the table above is classified into two groups: 1 and 2.
-- Obtain a data entry for each category, so that the probability of Data occurrence matches the occurrence rate in the field.

Declare @ t table (serial number int, name varchar (2), classification int, occurrence rate numeric (0.5) insert into @ tselect 1, 'A', 1, Union allselect 2, 'bb ', 1, 0.5 Union allselect 3, 'cc', 2, 0.3 Union allselect 4, 'dd', 2, 0.6 Union allselect 5, 'ff', 2, 0.1; with MACO as (select. * From @ t aright join Master .. spt_values B on 1 = 1 where type = 'p' and. occurrence Rate * 10> B. number) Select category, (select top 1 name from MACO where category = T. CATEGORY order by newid () as name from maco t group by category/* category name --------- ---- 1 BB2 dd */

Example Source:

Http://social.microsoft.com/Forums/zh-CN/sqlserverzhchs/thread/33f20f9a-9750-409f-a1cc-99111dcbb5ff