你用Group by就需要select sum(...) 等聚合函数里。
解决方案 »
- 已经安装了.Net 4.0后但程序仍不能运行
- 求助!如何从dataset中按条件查询数据?
- 一个项目用到oracle数据库并且要部署到n台机器如何能把oracle客户端也打包呢
- 【兄弟们】问一下。为什么WPF/Silverlight非要用到DependencyProperty?和普通Property有什么区别?能不能形象易懂的说明一下啊?我太笨了,看书也看不明白。
- 请问如何在string中加分号
- 在winform中用Panel来显示窗体的问题?
- 求教页面刷新问题
- 一点关于MCAD的疑惑?谢谢
- system.winforms与system.windows.forms的疑问继续发问
- SQL中distinct的使用问题
- 在线等待:为什么我使用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改为你的表名