时间戳

首页 > 解决方案 > 循环时结果不显示

sql - 循环时结果不显示

问题描述

我正在做一个powershell，它通过一个循环来从具有不同参数的sql查询中获取结果。问题只是循环的第一次运行有结果。其余的都是空白结果，也应该显示一些结果。

$Days = @(90,60,30,15)
$Start = 0
$End = 0
$Affiliation = 1
for ($i = 0 ; $i -lt $Affiliation.Count ; $i++) { 
for ($i = 0; $i -lt $Days.Count ; $i++){
    if ($Days[$i] -eq 1) {
        $End = $Days[$i]
        $Start = 0
        write-host "Start/End r:" $Start $End
    } 
    elseIf ($index -eq $Days.Length) {
        $End = $Days[$i]
        $Start = 0
        write-host "Start/End m:" $Start $End 
    }
    else {
        $End = $Days[$i]
        $Start = $Days[$i+1] + 1
        write-host "Start/End s:" $Start $End
    }
          ########## Queries

        $Querydays = " SELECT 
        distinct tblcustomer.CustomerName AS Customer_Name
        , tblsma.sorno AS SMA_SOR_Number
        , FUNC_GET_JSON_VALUES('Name', tblsma.products) AS Products
        , DATE_FORMAT(tblsma.expiryDate, '%m/%d/%Y') AS Expiry_Date
        , tblsma.Remarks AS 'Remarks'
        , DATEDIFF( DATE(tblsma.expiryDate), CURDATE()) AS Days_Left_Before_Expiration 
         #,GROUP_CONCAT(tblsma.products) AS 'PRODUCT'
        FROM
        where 
        tblcustomer.affiliationID = '" + $Affiliation[$i] + "'
        and tblcustomer.affiliationID <> 95
        and tblaccountsgroup.GroupID <> 5
        and datediff(DATE(tblsma.expiryDate), CURDATE()) between " + $Start + " and " + $End + "
        order by Days_Left_Before_Expiration DESC"
        #####################
        $ResultDays=$null
        $ResultDays = MysqlConn -Query $Querydays
        Write-Host $ResultDays
        $EmailBody =  $EmailBody + "`r `n" + "<b>List of Valid License that will due in next " + $Days[$i] + " days:</b>"
        foreach($row in $ResultDays)
        {
            $EmailBody = "`n" + $EmailBody + "`r `n" + "Account Name : " + $row.Item(0) + "`r" +
            "SOR No.      : " + $row.Item(1) + "`r" +
            "Product Name : " + $row.Item(2) + "`r" +
            "Expiry Date  : " + $row.Item(3) + "`r" +
            "Remarks      : " + $row.Item(4) + "`r" +
            "Days Before Expiry : " + $row.Item(5) + "`r" +
            "`n"  
        } 
    $rep = $Days[$i+1]
}
}

Start/End s: 61 90
System.Data.DataRow System.Data.DataRow System.Data.DataRow
Start/End s: 31 60

Start/End s: 16 30

Start/End s: 1 15

标签： sqlpowershell

解决方案

因为您在两个for循环中使用相同的变量，$i所以这将一直被覆盖。更改其中之一的名称。

坏的：

for ($i = 1; $i -lt 11; $i ++)
{
    ('First for loop: ' + $i)

    for ($i = 1; $i -lt 11; $i ++)
    {
        ('Second for loop: ' + $i)
    }
}
First for loop: 1
Second for loop: 1
Second for loop: 2
Second for loop: 3
Second for loop: 4
Second for loop: 5
Second for loop: 6
Second for loop: 7
Second for loop: 8
Second for loop: 9
Second for loop: 10


好的：

for ($i = 1; $i -lt 11; $i ++)
{
    ('First for loop: ' + $i)

    for ($x = 1; $x -lt 11; $x ++)
    {
        ('Second for loop: ' + $x)
    }
}
First for loop: 1
Second for loop: 1
Second for loop: 2
Second for loop: 3
Second for loop: 4
Second for loop: 5
Second for loop: 6
Second for loop: 7
Second for loop: 8
Second for loop: 9
Second for loop: 10
First for loop: 2
Second for loop: 1
Second for loop: 2
...

推荐阅读