你用Group by就需要select sum(...) 等聚合函数里。

解决方案 »

  1.   

    Left(PictureID,6) 必须在select 的显示列中体现
      

  2.   

    但我的PictureID是VARCHAR型,如果不用Group By 那如何写呢,请指教!!
      

  3.   

    to maotin,chenxyon:谢谢你们,说到点子了.可怎办呢?
      

  4.   

    Group by Left(PictureID,6) 你这个条件的目的是什么?
    ××××××××××××××××××××××××××××
    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
      

  5.   

    Group by Left(PictureID,6) 你这个条件的目的是什么?表示在每个分类中只取最新一条.  Left(PictureID,6)表示每一个Picture的分类号
      

  6.   

    要语句通过
    PictureID,Caption
    这两个字段也要进行聚合处理
    放到Gourp By中,或Sum等聚合函数处理然后Top value
    是获取整个查询结果的最高value条记录
    好像没有办法获得每个分组的第一条
      

  7.   

    PictureID,Caption分别表示图片ID号和图片标题,它们都是文本型,如何放到聚合函数中去呢?
      

  8.   

    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 PictrueID,Caption order by AuditDate Desc
      

  9.   

    还是不对:System.Data.SqlClient.SqlException: Column 'Pictures.PictureID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
      

  10.   

    你是怎么加到group by 中的
    贴出来看
      

  11.   

    就是不对,所以才来求助的.我最初的语句就是最上面写的
    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不知有没有替代方案
      

  12.   

    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 PictureID,Caption,AuditDate
    order by AuditDate Desc
      

  13.   

    语句执行是没问题,但肯定不是我想要的,首先,它没按分类号来分组,其次,它跳过了and left(PictureID,2)='00' ,反而这样的记录没取到,而取到了以'01'打头的记录了,呵呵!!
      

  14.   

    语句就是楼上那样写应该可以运行
    不过加了top 1后
    得到的数据只可能是0或1条这可能不是你要的结果
    我实在想不到,如何获取分组纪录的第一条
    (或者你可以到Sql板块去问)
    给你一个用程序提示的思路
    把查询中的Top 1去掉
    然后用DataReader读取资料
    每读一行,都判断这一行和上一行的Left(PictureID,6)
    是否相同,如果,相同,则不保存值
    这样的话~~~~相信你应该搞得定,就不多说了~~
      

  15.   

    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
    这样子写肯定是会有问题的!
      

  16.   

    不明白你为什么要以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"
      

  17.   

    不知你的这个sql 语句是要放在哪里用的,你可以将它们放在存储过程中,会帮你检查语法错误,不太知道这么写对不对,你可以试试:declare proPictureID char(6)
    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
    最好到数据库版问问
      

  18.   

    sorry, 写错了,proPictureID应该是6位,declare proPictureID char(6)
    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
      

  19.   

    declare proPictureID char(6)
    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应该也是不行的啦~~~~
      

  20.   

    谢谢大家的参与:
    我这个语句当然可以放到存储过程中去,只要能够解决为了方便你们的回答,我弄一些数据在上面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关联,其它字段已不重要
      

  21.   

    下面提供一些测试数据主表
    图片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
      

  22.   

    主表的PictureID是关键字段,唯一的,还有最后我要的是12位的PictureID和该图片的Caption,其它字段不需要。各位帮帮忙了。
      

  23.   

    To jonsonzxw(.net从入门到精通):
      用程序控制读取,不可以吗?
    一定要用SQL语句?
      

  24.   

    我一定要返回一个数据集,因为还有别的程序要用到这个数据集if(ViewState["Type"].ToString() == "0")
    ds = pictures.GetPicturesByCategory(ViewState["categoryId"].ToString());
    if(ViewState["Type"].ToString() == "1")
    ds = pictures.GetPicturesBySearch(ViewState["searchCondition"].ToString());
      

  25.   

    如果只是要返回一个数据集
    没有关系阿用程序可以实现啊
    你自定义一个DataTable表结构
    然后用程序控制,要读取的值
    并添加到DataTable中
    然后把这个table加入你的数据集
    不就ok
      

  26.   

    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 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里面运行试试看!看和你要的要求是不是一样的
      

  27.   

    写成程序就是这样子:
    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"
      

  28.   

    To dragontt:我考虑一下你的方法试试吧
      

  29.   

    早就该这样!用程序去处理比较好一点!
    用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
      

  30.   

    找到一个新的方法!
    快点来观注啊~~~~~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)把以上代码放到“查询分析器”上去运行一下!应该可以达到你的要求了!
      

  31.   

    最新解决方案!
    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)
      

  32.   

    To SophiaWang:还是不行呀,结果是有,但所有的都出来的,并不是我想要的呀!!!!
      

  33.   

    这是我的运行结果!
    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)
      

  34.   

    但在我这儿为何全部记录都出来了呢?你将最后的SQL语句再写出来看看,让我再试试好吗,
      

  35.   

    select * from @Pictures a 
    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改为你的表名
      

  36.   

    还是将所有的都取出来了,包括PictureID前6位重复的