提问人：Alfonso Hernandez Xochipa 提问时间：3/24/2021 最后编辑：Alfonso Hernandez Xochipa 更新时间：3/24/2021 访问量：63
使用 postgresql 数据库的某些 dbms 检索的结果集与 php 或 python 结果集不匹配
Result set retrieved with some dbms of postgresql database does not match with php or python result set
问：
我有一个奇怪的问题
我正在使用 postgresql 数据库，所以我创建了一个函数，该函数返回包含以下数据的查询。
当我想从编程语言中检索数据时，php或python，一些行和单元格被更改了。
下图是用 php 编写的 'var_dump' 语句，可以看到第一个数据与 dbms 的结果不相等
现在下图是用 python 编写的“print”语句，我使用相同的参数调用了相同的函数，并且发生相同的情况
例如，我有一行包含以下数据 这是 dbms DBeaver 7.3.3 和 AquaDataStudio 18.0.18 检索的数据示例。 检查用 * 括起来的值
Ac Diseño Y Maquinados S.A. De C.V. ADM120725CE8 **20105.00** **170684.94**
现在，当我从某种编程语言中获取值时，结果如下
Ac Diseño Y Maquinados S.A. De C.V. ADM120725CE8 **297488.33** **171968.47**
代码如下
返回结果集的函数的代码
返回 DBMS 的此结果集
create or replace function fn_reporte_cfdis(p_fecha_desde varchar,p_fecha_hasta varchar)
returns table (
razon_social varchar(254)
, rfc varchar(254)
, suma_emitidos NUMERIC
, suma_recibidos NUMERIC
, utilidad_fiscal NUMERIC
, isr NUMERIC
, ptu NUMERIC
, dividendos NUMERIC
) language plpgsql
as $$
begin
return query
SELECT
CI.SNOMBRE RAZON_SOCIAL,
CI.SRFC RFC,
coalesce(TO_NUMBER(TO_CHAR(EMITIDOS.TOTAL,'99G999G999D99'),'99G999G999D99'),0) SUMA_EMITIDOS,
coalesce(TO_NUMBER(TO_CHAR(RECIBIDOS.TOTAL,'99G999G999D99'),'99G999G999D99'),0)SUMA_RECIBIDOS,
coalesce(TO_NUMBER(TO_CHAR((EMITIDOS.TOTAL - RECIBIDOS.TOTAL),'99G999G999D99'),'99G999G999D99'),0) UTILIDAD_FISCAL,
coalesce(TO_NUMBER(TO_CHAR(((EMITIDOS.TOTAL - RECIBIDOS.TOTAL)*0.30),'99G999G999D99'),'99G999G999D99'),0) ISR,
coalesce(TO_NUMBER(TO_CHAR(((EMITIDOS.TOTAL - RECIBIDOS.TOTAL)*0.10),'99G999G999D99'),'99G999G999D99'),0) PTU,
coalesce(TO_NUMBER(TO_CHAR(((EMITIDOS.TOTAL - RECIBIDOS.TOTAL)*0.10),'99G999G999D99'),'99G999G999D99'),0) DIVIDENDOS
FROM
CLIENTES_IR CI
LEFT JOIN
(SELECT
CI.SRFC,
SUM(COALESCE(CS.SUBTOTAL,0)) AS TOTAL
FROM
CLIENTES_IR CI
LEFT JOIN
CFDIS_SAT CS
ON
CS.RFCEMISOR = CI.SRFC
WHERE
CS.TIPO = 'I' AND
CS.RFCEMISOR = CI.SRFC AND
CS.FHEMISION BETWEEN to_date(p_fecha_desde,'DD-MM-YYYY') AND to_date(p_fecha_hasta,'DD-MM-YYYY')
GROUP BY CI.SRFC ) EMITIDOS ON CI.SRFC = EMITIDOS.SRFC
LEFT JOIN
(SELECT
CI.SRFC,
SUM(COALESCE(CS.SUBTOTAL,0)) + COALESCE(EMITIDOS_NOMINA.SUBTOTAL,0) AS TOTAL
FROM
CFDIS_SAT CS
INNER JOIN
CLIENTES_IR CI ON CS.RFCRECEPTOR = CI.SRFC
LEFT JOIN
(SELECT
CI.SRFC,
SUM(COALESCE(CS.SUBTOTAL,0)) AS SUBTOTAL
FROM
CFDIS_SAT CS
INNER JOIN CLIENTES_IR CI ON CS.RFCEMISOR = CI.SRFC
WHERE
CS.TIPO = 'N' AND
CS.FHEMISION BETWEEN to_date(p_fecha_desde,'DD-MM-YYYY') AND to_date(p_fecha_hasta,'DD-MM-YYYY')
GROUP BY CI.SRFC ) EMITIDOS_NOMINA ON CI.SRFC = EMITIDOS_NOMINA.SRFC
WHERE
CS.TIPO ='I' AND
(CS.USOCFDI = 'G01' OR CS.USOCFDI = 'G03') AND
CS.FHEMISION BETWEEN to_date(p_fecha_desde,'DD-MM-YYYY') AND to_date(p_fecha_hasta,'DD-MM-YYYY')
GROUP BY CI.SRFC , EMITIDOS_NOMINA.SUBTOTAL) RECIBIDOS ON RECIBIDOS.SRFC = CI.SRFC;
end;$$
php 中连接到数据库并调用函数的模型类的代码
class ReporteCfdisModel
{
private $connection;
private $queryData;
public function __construct()
{
$this->connection = Connection::get()->connection(); //Get the connection of a class, is a tipical connection with pg_connect()
$this->queryData = IniUtils::getUtils("application", "QUERY"); // get data of .ini file (Not relevant for this case)
}
public function getAll()
{
try {
$stmt = pg_prepare($this->connection, "get_all", "select * from fn_reporte_cfdis($1,$2)"); // Prepare the query
$stmt = pg_execute($this->connection, "get_all", array("01-02-2021", "28-02-2021")); // execute the query and pass parameters
var_dump(pg_fetch_all($stmt)); // The line that Shows in the second image.
// return pg_fetch_all($stmt);
} catch (\Exception $ex) {
echo json_encode(new Response(500, $ex->getMessage(), ""), JSON_PRETTY_PRINT);
} finally {
$stmt = null;
$this->connection = null;
}
}
}
这是数据库连接的代码
class Connection
{
private $configData;
private $host;
private $dbName;
private $user;
private $password;
private $port;
private static $instance = NULL;
public $connectionData;
public function __construct()
{
$this->configData = IniUtils::getUtils("application", "DB_PGSQL");
if ($this->configData !== null || count($this->configData) > 0) {
$this->host = $this->configData["db_host"];
$this->dbName = $this->configData["db_name"];
$this->user = $this->configData["db_user"];
$this->password = $this->configData["db_password"];
$this->port = $this->configData["db_port"];
}
}
public static function get()
{
if (self::$instance === NULL) {
self::$instance = new Connection();
}
return self::$instance;
}
public function connection()
{
try {
// $this->connectionData = new PDO('pgsql:host=' . $this->host . ';port=' . $this->port . ';dbname=' . $this->dbName, $this->user, $this->password);
$strHost = 'host=' . $this->host . ' port=' . $this->port . ' dbname=' . $this->dbName . " user=" . $this->user . " password=" . $this->password;
$this->connectionData = pg_connect($strHost);
} catch (\Exception $ex) {
echo $ex->getMessage();
}
return $this->connectionData;
}
}
这些都是重要的代码来构建逻辑。
发生了什么？我不知道当我使用 dbms 调用查询或函数时会发生什么，以及为什么此数据与某些编程语言中的结果集调用不匹配。
请帮帮我！
答： 暂无答案
上一个：在火柴臂中使用闭合 [复制]
下一个：在 PHP 中匹配数组内的相似性
评论