mysql - 在最小值相同的情况下随机设置最小值或列

Question

我对最小值有疑问。在下表中，d1、d2、d3 值是最小值。当使用 SQL min() 或 least() 函数时，它总是会返回 left side first(也许我错了)意味着 d1 column here.

My question is : Is there any possibility to set least value or least column randomly among d1, d2 or d3. I want to say here, for every execution time, least_column will be changed where values are same.

表格正式

id  date                    name    d1  d2  d3  d4  least_value     least_column
1   2017-02-10T09:00:00Z    bimal   1   1   1   4   1               d1

select f.*,
       (case ff.least_column
             when 'd1' then d1
             when 'd2' then d2
             when 'd3' then d3
             when 'd4' then d4
        end) as least_value,
        ff.least_column
from formal f cross join
     (select least(d1, d2, d3, d4) as least_value,
             (case least(d1, d2, d3, d4)
                   when d1 then 'd1'
                   when d2 then 'd2'
                   when d3 then 'd3'
                   when d4 then 'd4'
              end) as least_column
      from formal
     ) ff;

SQL Fidle here

Answer 1

您依赖于限制随机化能力的小行结构。我建议您“反转”数据以生成更多行，然后您可以获得更随机的结果。注意 order by rand() 适用于小型结果集/表，但不适用于大型结果集。

您需要访问此 SQL Fiddle然后单击运行以查看任何“随机性”

CREATE TABLE formal
    (`id` int, `date` datetime, `name` varchar(5), `d1` int, `d2` int, `d3` int, `d4` int)
;

INSERT INTO formal
    (`id`, `date`, `name`, `d1`, `d2`, `d3`, `d4`)
VALUES
    (1, '2017-02-10 09:00:00', 'bimal', 1, 1, 1, 1);

查询 1:

select
*
from (
      select id, date, name, d1 colvalue, 'd1' colsource from formal union all
      select id, date, name, d2         , 'd2'           from formal union all
      select id, date, name, d3         , 'd3'           from formal union all
      select id, date, name, d4         , 'd4'           from formal
    ) p
inner join (
        select id, least(d1, d2, d3, d4) as least_value from formal
      ) lv on p.id = lv.id
where colvalue = least_value # correction here
order by rand()
limit 1

Results :

| id |                 date |  name | colvalue | colsource | id | least_value |
|----|----------------------|-------|----------|-----------|----|-------------|
|  1 | 2017-02-10T09:00:00Z | bimal |        1 |        d2 |  1 |           1 |

对于多行结果，每个 id 一个:

select
    *
from (
      select
            p.*
          , lv.least_value
          , @row_num :=IF( @prev = p.id, @row_num + 1, 1)AS RowNumber
          , @prev := p.id
      from (
            select id, date, name, d1 colvalue, 'd1' colsource from formal union all
            select id, date, name, d2         , 'd2'           from formal union all
            select id, date, name, d3         , 'd3'           from formal union all
            select id, date, name, d4         , 'd4'           from formal
          ) p
      inner join (
              select id, least(d1, d2, d3, d4) as least_value from formal
            ) lv on p.id = lv.id
      cross join (
          select @row_num := 0, @prev := null
          ) vars
      where colvalue = colvalue
      order by p.id, rand()
    ) d
where rowNumber = 1
;

结果

| id |                 date |  name | colvalue | colsource | least_value | RowNumber | @prev := p.id |
|----|----------------------|-------|----------|-----------|-------------|-----------|---------------|
|  3 | 2017-02-10T09:00:00Z | suman |        1 |        d3 |           1 |         1 |             3 |
|  1 | 2017-02-10T09:00:00Z | bimal |        1 |        d1 |           1 |         1 |             1 |
|  2 | 2017-02-10T09:00:00Z |  amal |        1 |        d2 |           1 |         1 |             2 |

参见 this revised sqlfiddle