在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中.

[lightenvoy]

有两个表A，B
a:JID,JName
↑
b:JID,UID,IfDel

b 表和A表的关系是多对1，现在要显示的字段是count(B.UID),A,JID,Jname,IfDel；
count(B.UID)为b表中每一个JID的UID的和，并且要显示count(B.UID)>0的，我写的SQL语句是：

Select Count(B.UID) C,A.JName,A.JID,B.IfDel From A inner join B on B.JID=J.JID group by A.JID Having Count(B.JID)>0 Order By A.JID Desc

出现的如下错误：
服务器: 消息 8120，级别 16，状态 1，行 1
列 'A.JName' 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。
服务器: 消息 8120，级别 16，状态 1，行 1
列 'B.Ifdel' 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。

请大家帮我一下，后者QQ（43725776）讨论，先谢过~

[phantomMan]

select a.*,t.[count] from a,(
select a.JID,count(*) as [count] from a left join b on a.JID=b.JID
group by b.JID
)t
where a.JID=t.JID

[vivianfdlpw]

指定 GROUP BY 时，选择列表中任一非聚合表达式内的所有列都应包含在 GROUP BY 列表中，或者 GROUP BY 表达式必须与选择列表表达式完全匹配。


select A.JID,A.JName,T.count,T.IFDel
from A join (select [count]=sum(UID),JID,[IFDel]=max(IFDel) from B group by JID)T
on A.JID=T.JID

[phantomMan]

select a.*,t.[count],b.IfDel from a,(
select a.JID,count(*) as [count] from a left join b on a.JID=b.JID
group by b.JID
)t,b
where a.JID=t.JID and a.JID=b.JID

[Frewin]

Select a.A.JName,A.JID,b.Count,c.IfDel From a inner join (Select JID,Count(UID) As Count From b Group BY Jid Having Count(UID)>0) b On a.Jid = b.Jid Inner Join (Select Distinct Jid,IfDel) C on c.jid = a.Jid

[lightenvoy]

phantomMan(去年Delphi,年底.net,今年5月SQL,现在XML,何时学JAVA?) 还是不行
列 'A.JID' 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。

[phantomMan]

列名选择错误了:

select a.*,t.[count],b.IfDel from a,(
select b.JID as JID,count(*) as [count] from a left join b on a.JID=b.JID
group by b.JID
)t,b
where a.JID=t.JID and a.JID=b.JID

[phantomMan]

create table a(
JID int,
JName varchar(10)
)
create table b(
UID int identity,
JID int,
IfDel varchar(10)
)

insert into a values(1,'aaa')
insert into a values(2,'bbb')
insert into a values(3,'ccc')

insert into b values(1,'fafa')
insert into b values(2,'afa')
insert into b values(3,'fsadf')
insert into b values(1,'fas')
insert into b values(2,'fas')
insert into b values(3,'fas')
insert into b values(1,'sadf')
insert into b values(2,'sadf')
insert into b values(3,'asfd')
insert into b values(3,'asfd')

select a.*,t.[count],b.IfDel from a,(
select b.JID as JID,count(*) as [count] from a left join b on a.JID=b.JID
group by b.JID
)t,b
where a.JID=t.JID and a.JID=b.JID

--结果:

--不知道下面是不是你要的结果:
JID JName count IfDel
----------- ---------- ----------- ----------
1 aaa 3 fafa
2 bbb 3 afa
3 ccc 4 fsadf
1 aaa 3 fas
2 bbb 3 fas
3 ccc 4 fas
1 aaa 3 sadf
2 bbb 3 sadf
3 ccc 4 asfd
3 ccc 4 asfd

（所影响的行数为 10 行）

[phantomMan]

假如 A 表再插入一条记录,
insert into a values(3,'ccc')
由于上面做的是inner join,所以已经排除了count(B.UID)><=0 情况

[lightenvoy]

phantomMan,vivianfdlpw的现在没出错，我测试一下，如果没有问题，明天这个时候揭贴，如果还有更好的，同样给分！

[phantomMan]

你的这个问题有很多种答案,不过都可以分为两类:
连接查询,子查询

[lightenvoy]

vivianfdlpw你的这个可以给我解释一下么？

[powerly]

to：phantomMan,vivianfdlpw 应该是9条数据才对吧！
select a.JID,a.JName ,b_temp.UID,b_temp.IfDel
from a
inner join
(select b.JD ,b.IfDel, bb_temp.cnt_UId as UID
from b
inner join
(select count(UID) as cnt_UId , jD from b group by jD ) bb_temp
on b.jD = bb_temp.jD
) b_temp
on a.JID = b_temp.JD

[phantomMan]

powerly(豆豆):

--
我上面的b表是10条记录,有一个重复的

[powerly]

贴错了：
应该不重复：
select a.JID,a.JName ,b_temp.UID,b_temp.IfDel
from a
inner join
(select distinct b.JD ,b.IfDel, bb_temp.cnt_UId as UID
from b
inner join
(select count(UID) as cnt_UId , jD from b group by jD ) bb_temp
on b.jD = bb_temp.jD
) b_temp
on a.JID = b_temp.JD

[Frewin]

--借phantomMan(去年Delphi,年底.net,今年5月SQL,现在XML,何时学JAVA?) 数据一用,
--难道我的不行
create table a(
JID int,
JName varchar(10)
)
create table b(
UID int identity,
JID int,
IfDel varchar(10)
)
go

insert into a values(1,'aaa')
insert into a values(2,'bbb')
insert into a values(3,'ccc')

insert into b values(1,'fafa')
insert into b values(2,'afa')
insert into b values(3,'fsadf')
insert into b values(1,'fas')
insert into b values(2,'fas')
insert into b values(3,'fas')
insert into b values(1,'sadf')
insert into b values(2,'sadf')
insert into b values(3,'asfd')
insert into b values(3,'asfd')

Select a.JName,A.JID,b.Count,c.IfDel From a inner join (Select JID,Count(UID) As Count From b Group BY Jid Having Count(UID)>0) b On a.Jid = b.Jid Inner Join (Select Distinct Jid,IfDel From b) C on c.jid = a.Jid


drop table a
drop table b

[Frewin]

而且,我排除了重复记录.而且采用了Inner Join ,效率更高

[phantomMan]

不要重复,改一下:

select a.JID,a.JName,t.[count],b.IfDel from a,(
select b.JID as JID,count(*) as [count] from a left join b on a.JID=b.JID
group by b.JID
)t,(Select Distinct Jid,IfDel From b)b
where a.JID=t.JID and a.JID=b.JID

[lightenvoy]

结帐～谢谢大家

[lsghy0414]

碰到同样问题
学习学习……