英文:
Running aggregate join queries on mysql from golang returns no values
问题
我有以下查询，在直接从MySQL运行时返回结果。
当从Golang程序运行时，相同的查询返回0个值。
package main
import (
"github.com/rs/zerolog/log"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
)
var DB *sqlx.DB
func main() {
DB, err := sqlx.Connect("mysql", "root:password@(localhost:3306)/jsl2")
if err != nil {
log.Error().Err(err)
}
sqlstring := `SELECT
salesdetails.taxper, sum(salesdetails.gvalue),
sum(salesdetails.taxamt)
FROM salesdetails
Inner Join sales ON sales.saleskey = salesdetails.saleskey
where
sales.bdate >= '2021-12-01'
and sales.bdate <= '2021-12-31'
and sales.achead IN (401975)
group by salesdetails.taxper
order by salesdetails.taxper`
rows, err := DB.Query(sqlstring)
for rows.Next() {
var taxper int
var taxableValue float64
var taxAmount float64
err = rows.Scan(&taxper, &taxableValue, &taxAmount)
log.Print(taxper, taxableValue, taxAmount)
}
err = rows.Err()
if err != nil {
log.Error().Err(err)
}
}
在控制台上运行该程序返回以下值。
在SQL浏览器中，返回了4行，这是正确的。
相同查询在SQL浏览器中的结果为：
0 1278.00 0.00
5 89875.65 4493.78
12 3680.00 441.60
18 94868.73 17076.37
但是在程序中也返回了4行，但值为0。
{"level":"debug","time":"2022-01-13T17:07:39+05:30","message":"0 0 0"}
{"level":"debug","time":"2022-01-13T17:07:39+05:30","message":"0 0 0"}
{"level":"debug","time":"2022-01-13T17:07:39+05:30","message":"0 0 0"}
{"level":"debug","time":"2022-01-13T17:07:39+05:30","message":"0 0 0"}
如何为聚合函数设置数据类型？
英文:
I have the following query which returns results when run directly from mysql.
The same query returns 0 values, when run from golang program.
package main
import (
"github.com/rs/zerolog/log"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
)
var DB *sqlx.DB
func main() {
DB, err := sqlx.Connect("mysql", "root:password@(localhost:3306)/jsl2")
if err != nil {
log.Error().Err(err)
}
sqlstring := `SELECT
salesdetails.taxper, sum(salesdetails.gvalue),
sum(salesdetails.taxamt)
FROM salesdetails
Inner Join sales ON sales.saleskey = salesdetails.saleskey
where
sales.bdate >= '2021-12-01'
and sales.bdate <= '2021-12-31'
and sales.achead IN (401975)
group by salesdetails.taxper
order by salesdetails.taxper`
rows, err := DB.Query(sqlstring)
for rows.Next() {
var taxper int
var taxableValue float64
var taxAmount float64
err = rows.Scan(&taxper, &taxableValue, &taxAmount)
log.Print(taxper, taxableValue, taxAmount)
}
err = rows.Err()
if err != nil {
log.Error().Err(err)
}
}
On the console, running the program returns the following values.
In SQL browser, it returns 4 rows which is correct.
The result from the sql browser for the same query is
0 1278.00 0.00
5 89875.65 4493.78
12 3680.00 441.60
18 94868.73 17076.37
But in the program also return 4 rows with 0 value.
{"level":"debug","time":"2022-01-13T17:07:39+05:30","message":"0 0 0"}
{"level":"debug","time":"2022-01-13T17:07:39+05:30","message":"0 0 0"}
{"level":"debug","time":"2022-01-13T17:07:39+05:30","message":"0 0 0"}
{"level":"debug","time":"2022-01-13T17:07:39+05:30","message":"0 0 0"}
How to set the datatype for the aggregate functions.
答案1
得分: 0
我将taxper的数据类型更改为float，并且它起作用了。我在检查rows.Scan的错误后发现了这个问题，正如@mkopriva建议的那样。
英文:
I changed the datatype of taxper to float and it worked.
I found this after checking the err from rows.Scan( as suggested by @mkopriva
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论