gpt4 book ai didi

bash - 错误 : more than one row returned by a subquery used as an expression

转载 作者:行者123 更新时间:2023-11-29 14:24:06 27 4
gpt4 key购买 nike

我搜索了又搜索.. 我有一个 bash 脚本,用于运行 psql 查询并每天通过电子邮件发送结果。数据库直到午夜才更新,我的 bash 脚本将一个变量传递给前一天的查询。只有当我使用传递的变量时我才会收到此错误,否则如果我手动将日期放入查询中它运行正常。不太确定,因为我仍在学习 psql 和 bash。

这是 bash 脚本:

#!/bin/bash
NOWDATE=`date +%Y-%m-%d -d "yesterday"`
SUBDATE=`date '+%B %e, %G'`
DIR=/file/report/
FILE=file-$NOWDATE.csv
RECIPIENT=email@mail.com

PGPASSWORD=passwrod psql -w -h host -p 5432 -d database -U user -o $DIR/$FILE <<EOF
select distinct als."Table_AccountID",
(select "Table_val_AccountStatusID" from "Table_log_AccountStatus"
where "Table_AccountID" = als."Table_AccountID" order by "Date" desc limit 1)
as "Table_val_AccountStatusID",

CASE
when (select count(*) from "Table_UsageHistory" cfuh
where cfuh."Disk">'123456' and date_trunc('day',cfuh."Created") = date_trunc('day','$NOWDATE'::timestamp)
-- -'1day':: interval
and extrTable('day' from "Created"::timestamp) = ac."DesiredBillingDate"
and date_trunc('day', "Created"::timestamp) = date_trunc('day', '$NOWDATE'::timestamp)
and cfuh."Table_AccountID" in (
select distinct "Table_AccountID" from "Table_Usage"
where date_trunc('day', "Timestamp"::timestamp) = date_trunc('day','$NOWDATE'::timestamp)
and "Table_AccountID" = cfuh."Table_AccountID")
and cfuh."Table_AccountID" = als."Table_AccountID") >0
then 'Y'
else 'N'
end as "RollUp",

(select distinct bc."ID" from "BIL_BillableCharge" bc, "Table_UsageHistory" cfh
where date_trunc('day',bc."Date"::timestamp) = date_trunc('day',cfh."Created"::timestamp)
and bc."Table_AccountID" = cfh."Table_AccountID" and bc."BIL_val_InvoiceItemTypeID" = '23'
and extrTable('month' from "Created"::timestamp) = extrTable('month' from '$NOWDATE'::timestamp)
and extrTable('year' from "Created"::timestamp) = extrTable('year' from '$NOWDATE'::timestamp)
and cfh."Table_AccountID" = als."Table_AccountID") as "BillableChargeID"

from "Table_log_AccountStatus" als, "Table_Account" ac
group by als."Table_AccountID", ac."ID", ac."DesiredBillingDate"
having (select distinct "Disk" from "Table_UsageHistory" cfu
where date_trunc('day', cfu."Created") = date_trunc('day','$NOWDATE'::timestamp)
and ac."ID" = cfu."Table_AccountID")>'123456'
and extrTable('day' from '$NOWDATE'::timestamp) = ac."DesiredBillingDate"
and ac."ID" = als."Table_AccountID"
ORDER BY "RollUp" ASC
EOF

sed -i '2d' $DIR/$FILE |
mailx -a $DIR/$FILE -s " Report for $SUBDATE" -r email@anotheremail.com $RECIPIENT

这里是 SQL,为了便于阅读而重新格式化。

select distinct 
als."Table_AccountID",

(select "Table_val_AccountStatusID"
from "Table_log_AccountStatus"
where "Table_AccountID" = als."Table_AccountID"
order by "Date" desc limit 1) as "Table_val_AccountStatusID",

CASE when
(select count(*)
from "Table_UsageHistory" cfuh
where cfuh."Disk">'123456'
and date_trunc('day',cfuh."Created") = date_trunc('day','$NOWDATE'::timestamp) -- -'1day':: interval
and extrTable('day' from "Created"::timestamp) = ac."DesiredBillingDate"
and date_trunc('day', "Created"::timestamp) = date_trunc('day', '$NOWDATE'::timestamp)
and cfuh."Table_AccountID" in
(select distinct "Table_AccountID"
from "Table_Usage"
where date_trunc('day', "Timestamp"::timestamp) = date_trunc('day','$NOWDATE'::timestamp)
and "Table_AccountID" = cfuh."Table_AccountID")
and cfuh."Table_AccountID" = als."Table_AccountID") > 0
then 'Y'
else 'N'
end as "RollUp",

(select distinct bc."ID"
from "BIL_BillableCharge" bc, "Table_UsageHistory" cfh
where date_trunc('day',bc."Date"::timestamp) = date_trunc('day',cfh."Created"::timestamp)
and bc."Table_AccountID" = cfh."Table_AccountID" and bc."BIL_val_InvoiceItemTypeID" = '23'
and extrTable('month' from "Created"::timestamp) = extrTable('month' from '$NOWDATE'::timestamp)
and extrTable('year' from "Created"::timestamp) = extrTable('year' from '$NOWDATE'::timestamp)
and cfh."Table_AccountID" = als."Table_AccountID") as "BillableChargeID"

from "Table_log_AccountStatus" als, "Table_Account" ac
group by als."Table_AccountID", ac."ID", ac."DesiredBillingDate"
having (select distinct "Disk"
from "Table_UsageHistory" cfu
where date_trunc('day', cfu."Created") = date_trunc('day','$NOWDATE'::timestamp)
and ac."ID" = cfu."Table_AccountID")>'123456'
and extrTable('day' from '$NOWDATE'::timestamp) = ac."DesiredBillingDate"
and ac."ID" = als."Table_AccountID"
ORDER BY "RollUp" ASC

当从服务器上的命令行像这样运行时,它会吐出错误:错误:用作表达式的子查询返回多于一行

非常感谢您的帮助,这个社区是最好的。抱歉格式问题,它来自复制粘贴。

最佳答案

当使用查询的 SELECT 部分中的子查询时,例如 (SELECT a, b, (SELECT c from d ...)) 则它必须返回 one 值。返回一个错误,因为一个子查询返回多于一行。检查所有子查询以确保它们返回的行不超过一行。如果可以接受存在多个值但只采用一个值,则添加 LIMIT 1 子句。

关于bash - 错误 : more than one row returned by a subquery used as an expression,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/8900774/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com