你用Group by就需要select sum(...) 等聚合函数里。
解决方案 »
- nfc 读取卡片,android中有个MifareClassic(Tag tag)的类有个a.getSak()方法可以拿到个东西,是0x01这样的
- C#线程创建时参数怎么传入,线程创建完成可以返回参数吗
- 请教大家一个问题, 关于用C#获取PDA手机信息的问题
- 20分求一个简单的计数问题。
- 实现这样的遍历(文件操作)
- Winform的EXE程序中如何調用另外一個ExE程序,并传递参数?
- 请教各位问题!!(有关C#里的mobile技术,哪位能说明一下?)
- 有关添加新数据的问题。
- 怎样在调用自定义的方法和属性中产生注释说明
- 新手的难题?
- 在线等待:为什么我使用Volitant.Dialogs出现以下错误?
- C#的初学者(应用DataGrid)-->用Microsft的配置数据适配器Wizard创建和配置数据集的问题,它会自动为你加上一些你不要的列
××××××××××××××××××××××××××××
Select Top 1 PictureID,Caption, Left(PictureID,6) from Pictures where PictureID In(Select PictureID from PictureDetails) and AuditMark='1' and left(PictureID,2)='00' and Caption like '%" + Request.Params["cont"] + "%' or Contents like '%" + Request.Params["cont"] + "%' Group by Left(PictureID,6) order by AuditDate Desc
PictureID,Caption
这两个字段也要进行聚合处理
放到Gourp By中,或Sum等聚合函数处理然后Top value
是获取整个查询结果的最高value条记录
好像没有办法获得每个分组的第一条
贴出来看
Select Top 1 PictureID,Caption from Pictures where PictureID In(Select PictureID from PictureDetails) and AuditMark='1' and left(PictureID,2)='00' and Caption like '%" + Request.Params["cont"] + "%' or Contents like '%" + Request.Params["cont"] + "%' Group by Left(PictureID,6) order by AuditDate Desc不知有没有替代方案
from Pictures
where PictureID In(Select PictureID from PictureDetails)
and AuditMark='1'
and left(PictureID,2)='00'
and Caption like '%" + Request.Params["cont"] + "%'
or Contents like '%" + Request.Params["cont"] + "%'
Group by PictureID,Caption,AuditDate
order by AuditDate Desc
不过加了top 1后
得到的数据只可能是0或1条这可能不是你要的结果
我实在想不到,如何获取分组纪录的第一条
(或者你可以到Sql板块去问)
给你一个用程序提示的思路
把查询中的Top 1去掉
然后用DataReader读取资料
每读一行,都判断这一行和上一行的Left(PictureID,6)
是否相同,如果,相同,则不保存值
这样的话~~~~相信你应该搞得定,就不多说了~~
这样子写肯定是会有问题的!
string strSql = "Select Top 1 PictureID,Caption,AuditDate from Pictures where PictureID In(Select PictureID from PictureDetails) and AuditMark='1' and left(PictureID,2)='00' and Caption like '%" + Request.Params["cont"] + "%' or Contents like '%" + Request.Params["cont"] + "%' Group by PictureID,Caption,AuditDate order by AuditDate Desc"
Select proPictureID = left(PictureID,2) from Pictures
Select Top 1 PictureID,Caption,Left(PictureID,6)
from Pictures
where PictureID In(Select PictureID from PictureDetails)
and AuditMark='1'
and left(PictureID,2)='00'
and Caption like '%" + Request.Params["cont"] + "%'
or Contents like '%" + Request.Params["cont"] + "%'
Group by proPictureID
order by AuditDate Desc
最好到数据库版问问
Select proPictureID = left(PictureID,6) from Pictures
Select Top 1 PictureID,Caption,Left(PictureID,6)
from Pictures
where PictureID In(Select PictureID from PictureDetails)
and AuditMark='1'
and left(PictureID,2)='00'
and Caption like '%" + Request.Params["cont"] + "%'
or Contents like '%" + Request.Params["cont"] + "%'
Group by proPictureID
order by AuditDate Desc
Select proPictureID = left(PictureID,6) from Pictures
Select Top 1 PictureID,Caption,Left(PictureID,6)
from Pictures
where PictureID In(Select PictureID from PictureDetails)
and AuditMark='1'
and left(PictureID,2)='00'
and Caption like '%" + Request.Params["cont"] + "%'
or Contents like '%" + Request.Params["cont"] + "%'
Group by proPictureID
order by AuditDate Desc应该也是不行的啦~~~~
我这个语句当然可以放到存储过程中去,只要能够解决为了方便你们的回答,我弄一些数据在上面Pictures----图片主表
字段名 类型 大小 备注
PictureID nvarchar 12 图片ID号
Caption nvarchar 80 图片标题
Contents nvarchar 400 文字说明
AuditMark nvarchar 1 审核标志
AuditDate datetime 8 审核日期
...PictureDetails ---- 图片子表
字段名 类型 大小 备注
DetailID int 4 图片子表ID号
PictureID nvarchar 12 图片ID
...
注、 两个表通过PictureID关联,其它字段已不重要
图片ID 标题 内容 审核 审核日期
000000000000 aaaaaaaa aaaaaaaa 1 2003-1-1
000000000001 bbbbbbbb bbbbbbbb 1 2003-1-2
000001000000 cccccccc ccccccc 1 2003-2-3
000101000000 dddddddd ddddddd 1 2003-1-15
000001000001 eeeeee eeeeee 1 2003-3-2
000001000002 ffffff fffff 0 2003-3-5...就拿上面的数据来说吧
PictureID 为12位,前6位表示该图片的分类,前6位相同的话表示分类相同,故第1、2条中是相同分类的,还有3、5、6也是相同分类下的图片,我需要取出来的结果是在每个分类中只需要取得最新的一条,但同时满足AuditMark = 1故上面的数据结果就应是:
000000000001 bbbbbbbb bbbbbbbb 1 2003-1-2
000001000001 eeeeee eeeeee 1 2003-3-2
用程序控制读取,不可以吗?
一定要用SQL语句?
ds = pictures.GetPicturesByCategory(ViewState["categoryId"].ToString());
if(ViewState["Type"].ToString() == "1")
ds = pictures.GetPicturesBySearch(ViewState["searchCondition"].ToString());
没有关系阿用程序可以实现啊
你自定义一个DataTable表结构
然后用程序控制,要读取的值
并添加到DataTable中
然后把这个table加入你的数据集
不就ok
declare @PictureDetails table(DetailID int,PictureID nvarchar(12))
insert @Pictures select '000000000000','aaaaaaaa','aaaaaaaa','1','2003-1-1'
insert @Pictures select '000000000001','bbbbbbbb','bbbbbbbb','1','2003-1-2'
insert @Pictures select '000001000000','cccccccc','cccccccc','1','2003-2-3'
insert @Pictures select '000101000000','dddddddd','dddddddd','1','2003-1-15'
insert @Pictures select '000001000001','eeeeee','eeeeee','1','2003-3-2'
insert @Pictures select '000001000002','ffffff','ffffff','0','2003-3-5'insert @PictureDetails select '000','000000000000'
insert @PictureDetails select '001','000000000001'
select * from @PicturesSelect Top 1 PictureID,Caption,AuditDate
from @Pictures
where PictureID In(
Select PictureID
from @PictureDetails
)
and AuditMark='1'
and left(PictureID,2)='00'
and Caption like '%1%'
or Contents like '%a%'
Group by PictureID,Caption,AuditDate
order by AuditDate Desc自己在SQL里面运行试试看!看和你要的要求是不是一样的
string strSql = "Select Top 1 PictureID,Caption,AuditDate from Pictures where PictureID In(Select PictureID from PictureDetails) and AuditMark='1' and left(PictureID,2)='00' and Caption like '%" + Request.Params["cont"] + "%' or Contents like '%" + Request.Params["cont"] + "%' Group by PictureID,Caption,AuditDate order by AuditDate Desc"
用SQL语句做,似乎不大可能办到!Select Left(PictureID,6) as Class,AuditDate
from @Pictures
where AuditMark='1'
Group by Left(PictureID,6),AuditDate
order by Left(PictureID,6),AuditDate Desc
快点来观注啊~~~~~declare @Pictures table(PictureID nvarchar(12), Caption nvarchar(80),Contents nvarchar(400),AuditMark nvarchar(1),AuditDate datetime)
declare @PictureDetails table(DetailID int,PictureID nvarchar(12))
insert @Pictures select '000000000000','aaaaaaaa','aaaaaaaa','1','2003-1-1'
insert @Pictures select '000000000001','bbbbbbbb','bbbbbbbb','1','2003-1-2'
insert @Pictures select '000001000000','cccccccc','cccccccc','1','2003-2-3'
insert @Pictures select '000101000000','dddddddd','dddddddd','1','2003-1-15'
insert @Pictures select '000001000001','eeeeee','eeeeee','1','2003-3-2'
insert @Pictures select '000001000002','ffffff','ffffff','0','2003-3-5'insert @PictureDetails select '000','000000000000'
insert @PictureDetails select '001','000000000001'
select * from @PicturesSelect Left(PictureID,6) as Class,max(AuditDate) AuditDate
from @Pictures
where AuditMark='1'
Group by Left(PictureID,6)把以上代码放到“查询分析器”上去运行一下!应该可以达到你的要求了!
declare @Pictures table(PictureID nvarchar(12), Caption nvarchar(80),Contents nvarchar(400),AuditMark nvarchar(1),AuditDate datetime)
declare @PictureDetails table(DetailID int,PictureID nvarchar(12))
insert @Pictures select '000000000000','aaaaaaaa','aaaaaaaa','1','2003-1-1'
insert @Pictures select '000000000001','bbbbbbbb','bbbbbbbb','1','2003-1-2'
insert @Pictures select '000001000000','cccccccc','cccccccc','1','2003-2-3'
insert @Pictures select '000101000000','dddddddd','dddddddd','1','2003-1-15'
insert @Pictures select '000001000001','eeeeee','eeeeee','1','2003-3-2'
insert @Pictures select '000001000002','ffffff','ffffff','0','2003-3-5'insert @PictureDetails select '000','000000000000'
insert @PictureDetails select '001','000000000001'
select * from @Picturesselect * from @Pictures a
where exists (select 1 from (select Left(PictureID,6) PictureID,max(AuditDate) AuditDate
from @Pictures
where AuditMark = '1'
group by Left(PictureID,6)) b
where Left(a.PictureID,6)=Left(b.PictureID,6) and a.AuditDate=b.AuditDate)
PictureID Caption Contents AuditMark AuditDate
------------ -------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------- ------------------------------------------------------
000000000000 aaaaaaaa aaaaaaaa 1 2003-01-01 00:00:00.000
000000000001 bbbbbbbb bbbbbbbb 1 2003-01-02 00:00:00.000
000001000000 cccccccc cccccccc 1 2003-02-03 00:00:00.000
000101000000 dddddddd dddddddd 1 2003-01-15 00:00:00.000
000001000001 eeeeee eeeeee 1 2003-03-02 00:00:00.000
000001000002 ffffff ffffff 0 2003-03-05 00:00:00.000(6 row(s) affected)PictureID Caption Contents AuditMark AuditDate
------------ -------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------- ------------------------------------------------------
000000000001 bbbbbbbb bbbbbbbb 1 2003-01-02 00:00:00.000
000101000000 dddddddd dddddddd 1 2003-01-15 00:00:00.000
000001000001 eeeeee eeeeee 1 2003-03-02 00:00:00.000(3 row(s) affected)
where exists (
select * from (
select Left(PictureID,6) PictureID,max(AuditDate) AuditDate
from @Pictures
where AuditMark = '1'
group by Left(PictureID,6)
) b
where Left(a.PictureID,6)=Left(b.PictureID,6) and a.AuditDate=b.AuditDate)将@Pictures改为你的表名