使用SQLite和Perl从功能周期中选择一个随机行 [英] Select one random row from cycle of function with SQLite and Perl

问题描述

我尝试从中选择一个随机数:

Hi I tried to select one random number from this:

我的来源:

use DBI;
use CGI; 

my $file = '.\input.txt';       # Name the file
open(FILE, $file) or die("Unable to open file");
my @data = <FILE>;
foreach my $line (@data)
{
  chomp $line
  my $sth = $dbh->prepare("SELECT columnA FROM table WHERE columnA LIKE '%$line%'");
  $sth->execute;
  my $result = $sth->fetchall_arrayref;


  foreach my $row ( @$result ) {
  print "- ";
  print "@$row\n";
  print "<BR />";
  }

 }

如何只打印一个随机行??? 我尝试过这样的事情:

How can I print only ONE RANDOM row??? I tried something like that:

my $sth = $dbh->prepare("SELECT nazov_receptu FROM recepty WHERE nazov_receptu LIKE '%$line%' AND kategoria == 'p' AND (rowid = (abs(random()) % (select max(rowid)+1 from recepty)) or rowid = (select max(rowid) from recepty)) order by rowid limit 1;");

但不清楚...我不知道为什么...

but its not clear... i dont know why...

我正在使用SQLite并将其打印到Web界面.

I am using SQLite and printing it to web interface.

有空就可以尝试 input.txt:

You can try it when you have input.txt:

A
C

数据库:

id name
1 A
2 B
3 C
4 D
5 E

输出:

A OR C (random)

推荐答案

为什么不立即将文件参数加入查询而不是循环遍历它们?然后，在perl中提取随机索引很简单:

Why not join the file arguments into the query right away instead of looping over them? Then it is a simple matter to extract a random index in perl:

use strict;
use warnings;                            # Always use these two pragmas

my $file   = '.\input.txt';
open my $fh, "<", $file or die "Unable to open file: $!";
chomp(my @data = <$fh>);                # chomp all lines at once
my $query  = "SELECT columnA FROM table WHERE ";
$query    .= join " OR ", ( "columnA LIKE ?" ) x @data;
                                         # add placeholder for each line
@data = map "%$_%", @data;               # add wildcards
my $sth    = $dbh->prepare($query);
$sth->execute(@data);                    # execute query with lines as argument
my $result = $sth->fetchall_arrayref;
my $randid = rand @$result;              # find random index
my $row    = $result->[ $randid ];
print "- @$row\n";
print "<BR />";

如您所见，我使用了占位符，这是正确的方法在查询中使用变量.它也是碰巧处理任意数量参数的一种简单方法.因为我们将所有行都包含在查询中，所以我们不需要for循环.

As you see, I've used placeholders, which is the proper way to use variables with queries. It also happens to be a simple way to handle an arbitrary amount of arguments. Because we include all lines in the query, we do not need a for loop.

如您所见，我还更改了一些其他小细节，例如使用带有词法文件句柄的三个参数open，包括die语句中的错误变量$!，并使用适当的缩进，使用strict和warnings(没有它们，您永远不要编码)

As you see, I've also changed a few other small details, such as using three argument open with a lexical file handle, including the error variable $! in the die statement, using proper indentation, using strict and warnings (you should never code without them)

我已经在perl中处理了随机化，因为这对我来说是最简单的.在SQL查询中处理它可能既简单又有效.您可以将ORDER BY random() LIMIT 1附加到其末尾，这可能也很好.

I've handled the randomization in perl because it is simplest for me. It may be as simple and more effective to handle in the SQL query. You may just tack on the ORDER BY random() LIMIT 1 to the end of it, and that might work just fine too.

这篇关于使用SQLite和Perl从功能周期中选择一个随机行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！