同一日期只检索一条记录多条记录... [英] Retrieving only one record for same date multiple records...

问题描述

我有填充记录如:
的数据库

I have database with filled records like :

Sr    ExpName       Date       Camt  Tamt
---   --------- -----------   ----- -----
1     Pen        10-10-2012      10    10
2     abc        10-10-2012      30    45
3     xyz        11-10-2012      15    55
4     ggg        11-10-2012      20    75
5     aaa        12-10-2012      10    85
6     nnn        13-10-2012      10    95

但是，与我只想在同一行中记录同一日期和相关的 Camt
将被添加到同一日期 Camt ，并且输出将显示为:

But along with i want to only same date records in one row and related Camt
will be added for same date Camt and output will be appears like :

DATE             AMOUNT
------------     ------
10-10-2012          40
11-10-2012          35
12-10-2012          10
13-10-2012          10

我已经使用cursor和SimpleCursorAdapter编写了关于需求的代码，但是仍然无法获得适当的结果.

此输出将通过简单的Sql查询获得，例如:
从tb_expense GROUP BY CAST(DATE AS DATE)中选择CAST(日期为DATE)，SUM(Camt)为AMOUNT；

I have coded for regarding requirement using cursor and SimpleCursorAdapter but still i can''t get appropriate result.

this output will be get in simple Sql query like :
SELECT CAST(Date AS DATE),SUM(Camt) AS AMOUNT from tb_expense GROUP BY CAST(Date AS DATE);

Cursor c = db.query("tb_expense", new String[] {"Date", "SUM(Camt) AS Amount" }, null, null, "Date", null, null, null);
adapter = new SimpleCursorAdapter(this, R.layout.myrow1, c, new String[]{"Date","Camt"},new int[]{R.id.textView3,R.id.textView5} );
startManagingCursor(c);
lstExpense.setAdapter(adapter);

推荐答案

此问题由我自己解决:

this problem solved by myself :

Cursor c = db.query("tb_expense", new String[] 
       {DbHelper.C_Expno,"Group_Concat(ExpName) AS 
       ExpName","Date","SUM(Camt) AS Camt"}, null, null, "Date", null, null);

adapter = new SimpleCursorAdapter(this, R.layout.myrow1, c, new String[]
       {DbHelper.C_Expno,"ExpName","Date","Camt"},new  
       int[]{R.id.lblexpno,R.id.lblexpnm,R.id.lbldt,R.id.lblcamt} );

startManagingCursor(c);
lstExpense.setAdapter(adapter);

select * from(
select row_number() over(partition by dateadd(d,0,datediff(d,0,Date)) order by dateadd(d,0,datediff(d,0,Date)))as newSrNo, ExpName, Camt,Tamt from tbltrading where tradeid =11 group by InvoiceNo,PDate
)as q where q.SrNo=1

请进行必要的更改
希望这项工作

please necessary changes
hope this work

这篇关于同一日期只检索一条记录多条记录...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！