vc是这样写的
strSQL.Format("SELECT SUM(Expr1) AS AudienceTime FROM (SELECT CONVERT(real, CONVERT(datetime, end_time) - CONVERT(datetime, begin_time)) * 24 * 60 AS Expr1 FROM history_info_table WHERE (box_id = '%s') AND (user_sex = '%s') AND (chan_info = '%s') AND (user_age = '%s') UNION ALL SELECT CONVERT(real, CONVERT(datetime, end_time) - CONVERT(datetime, begin_time)) * 24 * 60 AS Expr1 FROM history_info_table AS history_info_table_1 WHERE (box_id = '%s') AND (user_sex = '%s') AND (chan_info = '%s') AND (user_age = '%s')) AS derivedtbl_1","888888000", "男","模拟频道47","0-12岁","888888000", "男","模拟频道35","0-12岁");
这个得到的结果就是0!用下面这个方法导出了sql语句得到的结果是27.将VC的SQL语句写文件,
然后到数据库中执行。
写文件的代码:
void WriteTempFile(const char * filename,const void *pData,size_t len)
{
::FILE* file = ::fopen(filename,
"wt");
::fwrite(pData, len,1,file);
::fclose(file);
}
调用时可以这样:
#ifdef _DEBUG
WriteTempFile("C:\\result.txt",strSQL,strSQL.GetLength());
#endif
得到的语句是:
SELECT SUM(Expr1) AS AudienceTime FROM (SELECT CONVERT(real, CONVERT(datetime, end_time) - CONVERT(datetime, begin_time)) * 24 * 60 AS Expr1 FROM history_info_table WHERE (box_id = '888888000') AND (user_sex = '男') AND (chan_info = '模拟频道47') AND (user_age = '0-12岁') UNION ALL SELECT CONVERT(real, CONVERT(datetime, end_time) - CONVERT(datetime, begin_time)) * 24 * 60 AS Expr1 FROM history_info_table AS history_info_table_1 WHERE (box_id = '888888000') AND (user_sex = '男') AND (chan_info = '模拟频道35') AND (user_age = '0-12岁')) AS derivedtbl_1
放在sql以及vc的查询器中都是27.太不明白了。帮忙看下
strSQL.Format("SELECT SUM(Expr1) AS AudienceTime FROM (SELECT CONVERT(real, CONVERT(datetime, end_time) - CONVERT(datetime, begin_time)) * 24 * 60 AS Expr1 FROM history_info_table WHERE (box_id = '%s') AND (user_sex = '%s') AND (chan_info = '%s') AND (user_age = '%s') UNION ALL SELECT CONVERT(real, CONVERT(datetime, end_time) - CONVERT(datetime, begin_time)) * 24 * 60 AS Expr1 FROM history_info_table AS history_info_table_1 WHERE (box_id = '%s') AND (user_sex = '%s') AND (chan_info = '%s') AND (user_age = '%s')) AS derivedtbl_1","888888000", "男","模拟频道47","0-12岁","888888000", "男","模拟频道35","0-12岁");
这个得到的结果就是0!用下面这个方法导出了sql语句得到的结果是27.将VC的SQL语句写文件,
然后到数据库中执行。
写文件的代码:
void WriteTempFile(const char * filename,const void *pData,size_t len)
{
::FILE* file = ::fopen(filename,
"wt");
::fwrite(pData, len,1,file);
::fclose(file);
}
调用时可以这样:
#ifdef _DEBUG
WriteTempFile("C:\\result.txt",strSQL,strSQL.GetLength());
#endif
得到的语句是:
SELECT SUM(Expr1) AS AudienceTime FROM (SELECT CONVERT(real, CONVERT(datetime, end_time) - CONVERT(datetime, begin_time)) * 24 * 60 AS Expr1 FROM history_info_table WHERE (box_id = '888888000') AND (user_sex = '男') AND (chan_info = '模拟频道47') AND (user_age = '0-12岁') UNION ALL SELECT CONVERT(real, CONVERT(datetime, end_time) - CONVERT(datetime, begin_time)) * 24 * 60 AS Expr1 FROM history_info_table AS history_info_table_1 WHERE (box_id = '888888000') AND (user_sex = '男') AND (chan_info = '模拟频道35') AND (user_age = '0-12岁')) AS derivedtbl_1
放在sql以及vc的查询器中都是27.太不明白了。帮忙看下
strSQL = "SELECT SUM(Expr1) AS AudienceTime FROM (SELECT CONVERT(real, CONVERT(datetime, end_time) - CONVERT(datetime, begin_time)) * 24 * 60 AS Expr1 FROM history_info_table WHERE (box_id = '888888000') AND (user_sex = '男') AND (chan_info = '模拟频道47') AND (user_age = '0-12岁') UNION ALL SELECT CONVERT(real, CONVERT(datetime, end_time) - CONVERT(datetime, begin_time)) * 24 * 60 AS Expr1 FROM history_info_table AS history_info_table_1 WHERE (box_id = '888888000') AND (user_sex = '男') AND (chan_info = '模拟频道35') AND (user_age = '0-12岁')) AS derivedtbl_1 "//这样的结果是什么?
这个是在vc中内嵌的,
下面那句是sql查询器中的查询语句。有上面那个得出的下面的语句,是等价的,但是现在vc中结果为0,本应该是27.
strSQL.Format("SELECT SUM(Expr1) AS AudienceTime FROM (SELECT CONVERT(real, CONVERT(datetime, end_time) - CONVERT(datetime, begin_time)) * 24 * 60 AS Expr1 FROM history_info_table WHERE (box_id = '888888000') AND (user_sex = '男') AND (chan_info = '模拟频道47') AND (user_age = '0-12岁') UNION ALL SELECT CONVERT(real, CONVERT(datetime, end_time) - CONVERT(datetime, begin_time)) * 24 * 60 AS Expr1 FROM history_info_table AS history_info_table_1 WHERE (box_id = '888888000') AND (user_sex = '男') AND (chan_info = '模拟频道35') AND (user_age = '0-12岁')) AS derivedtbl_1
");
把这个语句生成view,再用VC查询试试。
{
…
strText.Format(_T("%f"),"SELECT COUNT(*) AS AllPeopleCount FROM history_info_table\
WHERE (box_id = '888888000') AND (user_sex = '男') AND (chan_info = '模拟频道47') AND (user_age = '0-12岁')) AS derivedtbl_1");
pDC->TextOut(0,0,strText); …
}
是这样吗
pDC->TextOut(0,0,strText); 不对啊,得到很大的不规则的数,肯定不对
strSQL.Format("SELECT SUM(CONVERT(real, box_id)) AS AllPeopleCount\
FROM (SELECT box_id\
FROM current_info_table\
WHERE (box_id = '888888000')) AS derivedtbl_1");
}
break;
}
theApp.m_Database.Execute(strSQL.GetBuffer(strSQL.GetLength()));
CADOCommand myCADOCommand( &(theApp.m_Database), strSQL, adCmdText);
rs.Execute( &myCADOCommand );
rs.GetFieldValue(AllPeopleCount,CountValue);这样所得到的CountValue是0x60000000,换成十进制是1610612736;
CountValue的类型是unsigned long,
但是实际上,在sql查询分析器中CountValue是888888000这样看是不是明显一些?
SELECT SUM(Expr1) AS AudienceTime
FROM (SELECT CONVERT(real, CONVERT(datetime, end_time) - CONVERT(datetime,
begin_time)) * 24 * 60 AS Expr1
FROM history_info_table
WHERE (box_id = '888888000') AND (user_sex = '男') AND
((chan_info = '模拟频道47') OR
(chan_info = '模拟频道35')) AND (user_age = '0-12岁')